Is this easy or ???

[AC5FF]

Or am I barking up the wrong tree?!?!

I have two tables I am compairing and would like to identify what is missing from one table when compaired to the 2nd.

For simplicity, the first table contains 10 dates; we'll just call them 1 thru 10.
The second table contains two columns, a list of initials along with dates that corrispond to the first tables numbers.

So; in essence here is the data from table two:

ABC 1
ABC 2
ABC 4
ABC 5
ABC 6
ABC 8
ABC 9
ABC 10
XYZ 1
XYZ 2
XYZ 3
XYZ 4
XYZ 5
XYZ 7
XYX 8
XYZ 9
XYZ 10

What I want my query to output is:
ABC 3
ABC 7
XYZ 6

Yet I can't seem to get this to work.
Any Ideas?

 

[John Big Booty]

Check out the Find Unmatched Query Wizard

 

[Brianwarnock]

That won't work as all of the dates in table1 are in table2, unless you know a way to do it by group.
I think that you will need to use code to loop through each group looking for the missing dates.

Brian

 

[John Big Booty]

Yes you can, but you will need to first create a could of queries to strip of the ABC and XYZ prefixes and then compare the results of those two query in your Unmatched query.

You will of course need two unmatched queries one compare table 1 with table 2 and the other to do the reverse, you can then reapply the prefix in a further query. Convoluted yes, but doable none the less

 

[AC5FF]

John; Thanks for the "Unmatched Query" Link.. Never caught that.
It didn't work though.. LOL Go Figure.. I had high hopes last night after reading your post.

Here is the SQL from that unmatched query wizard:

SELECT [TimeSheetHrs Weekly Hrs Check].[EMPLOYEE ID], [TimeSheetHrs Weekly Hrs Check].[YR-QTR-WK]
FROM [TimeSheetHrs Weekly Hrs Check] LEFT JOIN [YR-QTR-WK] ON [TimeSheetHrs Weekly Hrs Check].[YR-QTR-WK] = [YR-QTR-WK].[yr-qtr-wk]
WHERE ((([YR-QTR-WK].[yr-qtr-wk]) Is Null));

This actually resulted in no records when it was run. Although the table format/data is a lot more extensive than my example above; in essence it is the same.

Brian; to expand on what you mentioned; yes, the dates are in table 1 and in table 2 - formatted/etc to be the same. Table 2 auto fills in the YR-QTR-WK field via a link with the table.

John; I can't strip out the ABC XYZ information ... that is actually employee initials that I need to know who has not had time entries done...

 

[AC5FF]

Just an FYI ...

I didn't get the data I wanted; but I got it visually.
I ran a crosstab query. That gave me a listing of all the YR/QTR/WK's down the side with a column for each employee. The total hrs populate the table. So, anyone with no entry now pops right out.

Here's the query:

TRANSFORM Count([TimeSheetHrs Weekly Hrs Check].SumOfHRS) AS CountOfSumOfHRS
SELECT [TimeSheetHrs Weekly Hrs Check].[YR-QTR-WK], [TimeSheetHrs Weekly Hrs Check].[Week Of], [TimeSheetHrs Weekly Hrs Check].[Week Ending]
FROM [TimeSheetHrs Weekly Hrs Check]
GROUP BY [TimeSheetHrs Weekly Hrs Check].[YR-QTR-WK], [TimeSheetHrs Weekly Hrs Check].[Week Of], [TimeSheetHrs Weekly Hrs Check].[Week Ending]
PIVOT [TimeSheetHrs Weekly Hrs Check].[EMPLOYEE ID];

Not exactly what I was looking for, but it'll get me through the job I need to do.

 

[John Big Booty]

Are you able to post a small sample of the actual data you are working with, in your two tables?

 

[AC5FF]

Okay.. Here's the data.
I've put in my crosstab query as well. Not that it matters a whole lot, but in this test db the data in TimeSheetHrs Weekly Hrs Check table is actually a query in my main db. But the data is the same, field names are the same, etc etc...

Please feel free to look and comment, but don't spend a lot of time .. The crosstab query is going to work for now. My thoughts are that with the data I would like to pull out (just missing employee, dates) I can then take that information to another query that will give me their time card hours for those specific weeks.

Grand ideas with little time to implement..