New to Access. Help with Queries with two unrelated tables.

[Csalge]

I'm working on a database that collects production and time excluded data per employee. This information is gathered so that employees productivity is based on their available hours at work. If an employee's schedule is 7.5 regular hours and they submitted 2 hours of excluded time for a given that the productivity of that given day is based on their production input (points) and on 5.5 hours. I have two different tables for time excluded requests and production that are not related. They're both related to employee table (employeeID). I need to do a query that based on a date range it should show records from both tables for the same day. If employee did not input time excluded in a day to assume they're available hours are based upon their regular schedule. I've tried running a query combining both tables but I get duplicate values. Any guidance on how I should tackle this will be greatly appreciated.

[June7]

I suspect need to do aggregate queries then join.

Could you provide sample data for analysis? Post table in thread or attach file. Follow instructions at bottom of my post.

[Csalge]

Here you go. I'm working on the supervisor interface that links to the data in this database. The supervisor will enter a date range and should see the breakdown daily of each day with its respective production and time exclusion.

[Csalge]

I forgot to include that in the breakdown of each day within the date range if a given day there was no production entered to show a value of 0 production but to still show the total for time excluded requests. The same thing with time exclusion if no time exclusion entered for a given day to show a production value and 0 time excluded. If there was none inputs for a day or days in the date range (holidays, weekends) to show 0 prod and 0 time excluded. There can be multiple entry per day for both (production and time excluded). Please let me know what would be the best way to accomplish this. thank you again

[June7]

Records in both tblProduction and tblTimeExcluded Request tables without fk_EmployeeID.

Since each table can have multiple records for each employee for a date, I think aggregate queries are needed. Try:

qryExcludedSum
SELECT [tblTime Excluded Request].DateRequested, [tblTime Excluded Request].fk_EmployeeID, Sum(DateDiff("n",[FromTime],[ToTime])/60) AS ElapsedHours
FROM [tblTime Excluded Request]
GROUP BY [tblTime Excluded Request].DateRequested, [tblTime Excluded Request].fk_EmployeeID;

qryProductionSum
SELECT tblProduction.DateofProductivity, tblProduction.fk_EmployeeID, Sum(tblProduction.points) AS SumOfpoints
FROM tblProduction
GROUP BY tblProduction.DateofProductivity, tblProduction.fk_EmployeeID;

Problem with joining these queries is that there is NOT a record in each table for each employee/date combination. Need to join these tables to a dataset that has every possible combination of employee/date values from both tables.

qryEmpDateAll
SELECT fk_EmployeeID, DateofProductivity AS DateWorked FROM tblProduction
UNION SELECT fk_EmployeeID, [DateRequested] FROM [tblTime Excluded Request];

Now join all three queries:
SELECT EmpDateAll.fk_EmployeeID, EmpDateAll.DateWorked, qryExcludedSum.ElapsedHours, qryProductionSum.SumOfpoints
FROM qryProductionSum RIGHT JOIN (qryExcludedSum RIGHT JOIN EmpDateAll ON (qryExcludedSum.fk_EmployeeID = EmpDateAll.fk_EmployeeID) AND (qryExcludedSum.DateRequested = EmpDateAll.DateWorked)) ON (qryProductionSum.fk_EmployeeID = EmpDateAll.fk_EmployeeID) AND (qryProductionSum.DateofProductivity = EmpDateAll.DateWorked);

None of this will show a record for a date where there is no data in either table. That would require a dataset of all dates - a table with a record for date of every day of every year.


Two suggestions:
Advise no spaces or special characters/punctuation (underscore is exception) in names
Advise not to set lookups in table http://access.mvps.org/access/lookupfields.htm

[Csalge]

Thank you so much!! I tried and everything worked except when I tried the last query to join all 3, the ElapsedHours column shows up empty :/

[June7]

The query works for me. With the data you provided, the query results in 14 records, 4 of which have ElapsedHours and 5 have SumOfPoints.

Null is not valid for joining records so those without EmployeeID and/or Date are not carried over into the final query. Why would records not have EmployeeID, CaseNumber, Date? Need to fix the data.

[Csalge]

i did it again and it worked! thank you so much!!!