How to differentiate 'New' items on a temporal basis...

[cheuschober]

Hiya folks.

So here's today's ponderance:

I have several queries chained together to produce a working dynaset of data with two fields--one is a non-unique item number, and the other is the trimester (a numeric field set up like 20041, 20042, etc) the item had action within. Because an item can have action within multiple semesters, each semester recorded for the item is its own record.

What I need to do for the next step in my preparation, is somehow weed out only those items which are completely new to the database for the 2004 trimesters.

I know I can't simply use "Where [Trimester] > 20033 as it would catch all items that had action within the given period of time as opposed to those that are new.

Maybe its just been a long day, but I've been staring at the screen for 40 minutes quite dumbfounded by it so any help would be greatly appreciated.

~Chad

 

[FoFa]

If I understand correctly, would
[Trimester] LIKE "2004*" work?

or Like 2004* if numeric (or does like only work on strings)?

or str([Trimester]) LIKE "2004*"

 

[cheuschober]

Unfortunately this would not work as it only catches actions within 2004. The items in field 1 are non-unique as I had said before.

An example dynaset:

Field 1----|----Trimester
124 _________ 2003-3
125 _________ 2004-1
125 _________ 2003-3
126 _________ 2004-2
126 _________ 2002-4
127 _________ 2004-1
127 _________ 2004-2
128 _________ 2004-2

Out of the above dynaset, only items 127 and 128 are new to 2004. That's the field/data that I want to be able to extract. Any help?

Thnx,
~Chad

 

[john471]

One Possibility

Build a query to retrieve the "Field 1" values of the items you want to exclude, then use that to create an um-matched values query on the original (the wizard for Find Un-Matched should help if you need it); and hey presto; you have the desired result.

 

[cheuschober]

Hmm.. I think I had figured out that much already. I guess what I need help with is what exactly to tell the query to look for. I know it needs to compare the trimester values of each [field 1] against the other values for the same [field 1] but the logic inside my head isn't making sense for how to go about doing that.

Thanks again for all the assistance,
~Chad

 

[john471]

I have several queries chained together to produce a working dynaset of data with two fields--one is a non-unique item number, and the other is the trimester (a numeric field set up like 20041, 20042, etc)

I have assumed you have a query named qryItemTrimesterAction that includes fields ItemNumber, and TrimesterAction.
I have further assumed that the trimesterAction field is a numeric field with values as described above e.g. 20041 for 1st trimester of 2004.

Build an "exclude" query to supply a list of the Item Numbers you don't want (any item number less than the trimester you are interested). Given that you said you have a numeric field, something along the following lines should work:

SQL for query named qryItemExclude :-
SELECT DISTINCT qryItemTrimesterAction.ItemNumber
FROM qryItemTrimesterAction
WHERE (((qryItemTrimesterAction.Trimester)<20040));

Then build the converse (include) query:

If you only want the item numbers :-
SQL for query named qryItemShow :-
SELECT DISTINCT qryItemTrimesterAction.ItemNumber
FROM qryItemExclude RIGHT JOIN qryItemTrimesterAction ON qryItemExclude.ItemNumber = qryItemTrimesterAction.ItemNumber
WHERE (((qryItemExclude.ItemNumber) Is Null));


Or if you want item numbers and trimesters - (but this may result in multiple occurrences of item number if it both "new" and has had multiple trimester/actions eg. one item actioned in 2004-1, 2004-2 & 2004-3 would show up three times:-

Alternate SQL for query named qryItemShow :-
SELECT qryItemTrimesterAction.ItemNumber, qryItemTrimesterAction.Trimester
FROM qryItemExclude RIGHT JOIN qryItemTrimesterAction ON qryItemExclude.ItemNumber = qryItemTrimesterAction.ItemNumber
WHERE (((qryItemExclude.ItemNumber) Is Null));

HTH

Regards

John.

 

[cheuschober]

John - Thank you so much, I appreciate your patience.

I thought I was so close when I was sniffing up the

[MinOfTrimester] GROUP BY [Field 1] trail, but this seems considerably less expensive.

Thank you again.

Regards,
~Chad