Software
I maintain records with a sub table of dated entries.
Want to select records which have no entries for a given time period. Example - all records with no activity in December. I need all records without dates >11/30/2004 and <1/1/2005 - but cannot recall formula (if I ever knew).
by dklippert
I think you're looking for NULL
Meaning no entry.
by qaqcdave
Records are updated each month - the purpose is to locate those records which require updating, not where a null exists (existing record with no date). Until I make a December entry, there is no entry to be null, as previous updates are identified per date.
The search is to locate records where no entries yet exist in the sub table for December (or any other month or range of dates)
by rewrite
I think you want a "not in" query. I don't know what your table structures are and what they are keyed on but it would look something like this:
select from table1 where not in (select from table2 where >11/30/2004 and <1/1/2005)
If this doesn't make sense post back with your table structures and what result set you're trying to get.
Cheers.
RW
by qaqcdave
I think this is the right track, but failed to achieve goal (maybe my writing skills)...running Access 2003;
I have 20 files in table 1, w misc data;
I have 20 files in table 2, w misc data, including 5 Nov dates, 5 Dec dates, 5 Jan dates, 5 blank (is test database).
Tables are linked by unique identifier #.
I am trying to find a way to tell a query to select only files WITHOUT Dec dates.
by rewrite
OK. Your query would look something like this:
select * from table1 where table1.id not in (select id from table2 where >11/30/2004 and <1/1/2005)
This will return all of the fields in table1 where the id field is not present in table2 for the specified dates.
I'm assuming from your response that you meant you have 20 records in table1 and 20 records in table2. If you actually meant that you have separate tables for each of the months then you will have to "or" the detail tables togather to get the results.
Cheers.
RW
by qaqcdave
Not yet -
For purpose of discussion; structure as follows -
Table 1 is "PO Status", has 2 columns - 1) ID 2) PO Number;
Table 2 is "Rpt Status", has 3 columns - 1) ID 2) PO Number 3) Date;
Table 2 has significantly larger number of records then table 1, derived from number of dated record entries; but all records are traceable to a single PO in Table 1 (aka PO Status).
Table 1 may have more PO Numbers then Table 2 due to introduction of new POs - so must orientate off Table 1 PO list; but dates are in Table 2. Objective is to create list of PO Numbers that have no dates for specific date range (December in this case), from there can easily populate list with requried details.
Can you provide example of how you would write this?
Have tried many many combinations, getting nowhere - suspect I am making some simple but critical mistake.
by rewrite
I believe you're using the wrong id field to link the tables. Do not literally using the "ID" field. Use the "PO Number" field as common field between the tables. The query would look something like this:
select * from "PO Status" where "PO Number" not in (select "PO Number",date from "Rpt Status" where date >11/30/2004 and <1/1/2005)
I don't see the significance of the "ID" field and would remove it from both tables.
Cheers.
RW
by qaqcdave
Never could get statement to provide the result I was looking for.
Alternative implemented was -
1) using Make Table to create set "PO# w/ dates in desired range";
2) running Umatched query to find where "Active PO#" did not have match in set "PO# number w/ date in desired range" (which was the objective - a list of POs without activity in December);
3) using Make Table w/ Unique values to yes to remove duplicate PO numbers;
4) linked list of "PO#s" to PO details from PO database to attach detail data;
5) wired the series of coands into a macro, added a button, and shazam, report in 5 seconds.
Lack of elegance compensated for by functioning!
Interesting attempt though.
by qaqcdave
This discussion is closed to new replies.
Sort by Trending