SQL Expression

[sambrierley]

Hi, firstly thanks for any help.

I have the below expression but ive done it backwards.

SELECT [Week Ending].[Week Ending:], Archive.[Week Ending:]
FROM [Week Ending] RIGHT JOIN Archive ON [Week Ending].[Week Ending:] = Archive.[Week Ending:]
WHERE (((Archive.[Week Ending:]) Is Null));

a quick description of its prupose will make understanding my requirement a little easier.
basically Archive is a report repository, every JDE (person) needs to submit theyre report once a week(week ending).
so i want to know if someone hasnt submitted theyre report. the above highlights if a week ending appears in the week ending table but not in the Archive, obviously this is insufficient but tbh that exhausted my SQL abilities.

thanks

 

[plog]

I believe your should use a LEFT JOIN instead of a RIGHT JOIN. So the only change you need to make is changing RIGHT to LEFT in your SQL.

From a learning perspective--forget about RIGHT JOINs - they are worthless and only serve to confuse. They essentially are the same as a LEFT JOIN (including data from one side of the join), but from the other side. So if you ever want to include all from one table and only those matching from another, use the LEFT JOIN and put the tables on the appropriate side:

FROM [UseAllFromThisTable] LEFT JOIN [OnlyMatchesFromThisTable] ON ...

Just remember that and you never have to deal with RIGHT JOINS.

 

[sambrierley]

Hi, many thanks for the reply however my explanation was insufficent it would appear.
my approach has to change slightly as the above simply wouldnt work.
my new approach has to include a thrid table named staff. this is a list of all JDe numbers for staff (JDE is our staff ID)
so my plan is to select all staff and check that each have submitted a report(in Archive) for each week in the Week ending table. if they havent they are the people i need to see. hope that makes sense

i am very grateful if you could assist the SQL for this. thanks

 

[plog]

Can you provide sample data to illustrate what you have and what you want? I need 2 sets--A: starting sample data from your tables and B: results that should be returned based on A. Be sure to include table and field names. Use a screenshot or this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name...
David, 1/3/2004, 55
Larry, 2/2/2010, 91
Sam, 3/8/2011, 11

 

[sambrierley]

Ive uploaded a sample.
you will see that there are 2 members of staff with there unique JDE. in the Archive there is has been submissions from one person on the 04/07. however if you look the Week ending table 2 are required from each person, so i would like to see the result below
JDE, Week Ending
654321, 11/07/2014
123456, 04/07/2014
123456, 11/07/2014

thanks for your help

 

[plog]

If 2 are required for each person how come 654321, 04/07/2014 isn't an expected result?

Nevermind--I see, give me a second.

 

[sambrierley]

FYI its only 2 required at that point in time, obviously the next week 3 will be required and the hope is that this system will allow the admin to know whos missing so they can chase them up.

thanks

 

[plog]

You can't report on data that doesn't exist. Since all the permutations of JDE and WeekEndings don't exist in a data source, you need to create a datasource that has them. This will do that:

SELECT Staff.[JDE:], [Week Ending].[Week Ending:]
FROM [Week Ending], Staff;

Save that query with the name 'sub_MissingData'. Run it and it will give you all your permutations. With it, you can build another query to find out what data is missing. This is that code:

SELECT sub_MissingData.[JDE:], sub_MissingData.[Week Ending:], Archive.ID
FROM sub_MissingData LEFT JOIN Archive ON (sub_MissingData.[JDE:] = Archive.[JDE:]) AND (sub_MissingData.[Week Ending:] = Archive.[Week Ending:])
WHERE (((Archive.ID) Is Null));

Paste the above in and run it and it will provide you with what you want.