Problem with join queries

[lansel]

I have been reading all the threads on joining queries, right joins, left joins, etc., but I am having problems understanding.

I have two queries.

Query #1

Week #
Dept
Empl ID
Name
Std Hrs
Earned Hrs
Total Units

Query #2

Week #
Empl ID
Total Errors

The joins are Week # and Empl ID. The tables I used for the queries have daily entries, which I grouped for a weekly total. I want to combine the information into one report. There is only enter entry per employee per day for Std Hrs, Earned Hrs, and Total Units, but there may be lots of entries per day per employee for errors.

I think I may need three queries, doing a left outer join and a right outer join, then what? Only thing have been creating is lots of dups.

Please help!

 

[namliam]

select Sum([Total Errors])
from
where ...
group by ' list all fields from the first table here


Note: you should not use spaces in field names!

Regards

 

[lansel]

Thanks for your reply. Actually the date fields are DateEntry and DateAudit.

I do have the sum of the errors. The problem I have is joining the two queries. They both have EmpID and they both have date fields. BUT, they may not have the same dates(or week #s) in both queries and if there is not any production [Std Hrs],[Earned Hrs], and [Total Errors] for that date, there still may be [Total Errors], and there may be production without any errors.

I welcome any further suggestions.

Thanks,

 

[namliam]

It would seem for me that to make errors someone will have to have worked. Therefor the Query 1 will always have to have a record ... To me anyway.

You will need a commen point of reference. If you are sure you can have data in 1 and not in 2 and vice versa, then i would create a week table/query as a 3rd query/table.

Then you will allways have data in that 3rd table (only beeing the weeknr), you can then outer join to the other 2 doing all sorts of group by's etc.

If you feel this is not the way to go, try explaining your situation in more detail or post a sample db.

Regards

 

[lansel]

Thanks for your reply. I did try the date table last night and it worked great. I was able to create the report I needed.

In answer to your question concerning having an error for a matching production record, in some cases the cartons aren't checked for errors until the next day and that particular employee may not be working that day, so there would only be a entry for the error and none for production.

I appreciate all the help I receive from this forum.

Thanks!

 

[lansel]

I was too happy too soon! When reviewing my report and doublechecking my numbers, I realized that again if the production entry didn't have a matching error entry, the record didn't show up and the same for the error side.

When I created the queries joining with the date table, all entries were listed with just the date listed from the date table and entry for that day. If there wasn't an entry for that date the production or error side was blank. I created two queries, one with the production and date table and one with the errors and date table. Then I tried to join the two created queries. It only had records if they had a match. I tried every way my little brain could think of to join, but obviously I don't know what I am doing!!

The reports looks great, but it is not right. Please help!

Thanks,

 

[RV]

Use LEFT JOINS or RIGHT JOINS in your query, depending on the relations between your tables.
Check up upon JOIN types in Access Help.

RV