Linking queries

[Access_Novice]

I am creating a query (query3) based on queries 1 and 2 as a data source. I have queries 1 and 2 linked between 4 fields. The query3 results are based on a LEFT OUTER join. There is one particular record in query1 that is not properly pairing with its counterpart in query2.

In query1 I have selected 4 fields to display (there are other fields that I did not add to the design grid). I also applied GROUP BY.

11/02/2014 Smith, Steve Project XYZ 40.0 hours

In query2 I have selected 4 fields to display (there are other fields that I did not add to the design grid). I also applied GROUP BY so that this line would combine into one line with 40.0 hours to match the line above in Query1.

11/02/2014 Smith, Steve Project XYZ 20.0 hours
11/02/2014 Smith, Steve Project XYZ 20.0 hours




When I view my results with the LEFT OUTER join I see the record from Query1, but I don't see a record from Query2 with 40.0 hours (the two 20 hour lines added together).

If I query query2 alone as a data source and do GROUP BY, I am able to get it into one line with 40 hours, so I don't know why this suddenly does not work when I have both queries (1 and 2) as a data source.

This contains company information so I am unable to upload this.

[June7]

Must be some minor difference between the data. What are the sources for each of the GROUP BY queries?

Post full SQL statements of all queries.

[Access_Novice]

Must be some minor difference between the data. What are the sources for each of the GROUP BY queries?

Post full SQL statements of all queries.

Good idea about posting the SQL code, since I'm dealing with company data. Here is the SQL code for the query that is causing me trouble.

SELECT qryClickTimeSummary2.WeekEnding, qryClickTimeSummary2.Fullname, qryClickTimeSummary2.ProjectName, qryClickTimeSummary2.SumOfHours, qryBillingSummary2.WeekEnding, qryBillingSummary2.SumOfExostarHours
FROM qryClickTimeSummary2 LEFT JOIN qryBillingSummary2 ON (qryClickTimeSummary2.SumOfHours = qryBillingSummary2.SumOfExostarHours) AND (qryClickTimeSummary2.ProjectName = qryBillingSummary2.ProjectName) AND (qryClickTimeSummary2.Fullname = qryBillingSummary2.FullName) AND (qryClickTimeSummary2.WeekEnding = qryBillingSummary2.WeekEnding)
WHERE (((qryBillingSummary2.WeekEnding) Is Null) AND ((qryBillingSummary2.SumOfExostarHours) Is Null));

The query above is based on these two queries:

SELECT Billing.WeekEnding, Billing.EmploymentType, Billing.FullName, Billing.ProjectName, Billing.VIPnoSCAT, Billing.CLINECA, Billing.ExostarLineNo, Billing.LMApprover, Billing.HoursType, Sum(Billing.ExostarHours) AS SumOfExostarHours
FROM Billing
GROUP BY Billing.WeekEnding, Billing.EmploymentType, Billing.FullName, Billing.ProjectName, Billing.VIPnoSCAT, Billing.CLINECA, Billing.ExostarLineNo, Billing.LMApprover, Billing.HoursType
HAVING (((Billing.ProjectName)="XYZ"));

SELECT [TimesheetDate]+7-Weekday([TimesheetDate],2) AS WeekEnding, [LastName] & ", " & [FirstName] AS Fullname, ClickTime.ProjectName, [VIPno] & [SCATCode] AS VIP, ClickTime.ExostarLineNo, Sum(ClickTime.Hours) AS SumOfHours
FROM ClickTime
GROUP BY [TimesheetDate]+7-Weekday([TimesheetDate],2), [LastName] & ", " & [FirstName], ClickTime.ProjectName, [VIPno] & [SCATCode], ClickTime.ExostarLineNo
HAVING (((ClickTime.ProjectName)="XYZ"));

I agree that there must be some minor difference somewhere. I know I have done this in the past, but I deleted the previous queries that did work.

Notice how I am using calculated fields to come up with values for the WeekEnding and FullName values. I am trying to link these calculated fields to their counterparts in the other query. Those counterparts however, are not calculated fields. I wonder if this could somehow be the problem. As far as I can remember this is the only thing I changed. When this did work, I didn't have calculated fields for these values because I used formulas in the Excel spreadsheet to get these values, then I uploaded that into Access. I would like to avoid having to do that in my Excel sheet, that is why I decided to derive these values when I run the query with a calculated field.

I really appreciate your help.

[June7]

I understand JOIN linking on WeekEnding, FullName, ProjectName - but why hours?

Must be something about the calculated fields. Without analyzing data, don't see how can debug that for you.

[Access_Novice]

I understand JOIN linking on WeekEnding, FullName, ProjectName - but why hours?

Must be something about the calculated fields. Without analyzing data, don't see how can debug that for you.

The reason why I linked hours was because I am trying to do an exact pairing down to the number of hours. So anything that is off by .25 or something would not show as a match.

However, you gave me a good idea. Maybe I should not link the hours field. I'll give that a try and see the results I get. I may be able to work with that.

[Access_Novice]

Without analyzing data, don't see how can debug that for you.

I have a database attached. I only took a few lines, the ones that were giving me trouble. I am working on the query called "qryBillingRecWeekEndingDate." This query will use the other two queries as a source. Those other two queries are linked with a LEFT OUTER join.

Each ChargeNo is associated with a Task Name and Task Code.

Here is my problem:

1. Jim Fitzgerald has 40 timesheet hours. I want to pair that with his billed hours which is also 40 (20 hours on one ChargeNo and 20 on another ChargeNo). If you run qryBillingRecWeekEndingDate, I get all 40 timesheet hours, but the corresponding 20 and 20 hours do not sum up even though I used GROUP BY on qryBillingSummary. I suspect that this has something to do with the fact that 20 is on one ChargeNo and the other 20 is on a different ChargeNo. But I don't think that should matter because my query links are connecting WeekEnding,Fullname, ProjectName and SumofHours. So the ChargeNo isn't even included in the linking so I would think that that should not factor into the query results but I could be wrong. I know I have done a query link this before.

2. I have the same problem with Larry Roberts. He has 10 hours and 10 hours on two different ChargeNo's in his timesheet, and also in his BilledHours file and query. So I don't understand why they are not totaling up. Again I am not including ChargeNo in the linking of my source queries.

In short, I all I'm trying to do in qryBillingRecWeekEndingDate is pull the WeekEnding, FullName, Project and SummedHours from the Timesheet data and the Billing data and pair them together.

I must be overlooking something, because I know for sure that I have done a query like this before where I have hours on different lines due to having a different ChargeNo/Task Name/Task Code.

[June7]

They won't link because qryBillingSummary has two records for Fitzgerald and each is for 20 hours. Since you include the hours in the link, there is no match.

Also, Roberts shows 4 records in WeekEndingDate because the query joins 2 records from each query to each other (2 x 2 = 4).

The grouping in the two queries is too refined - too many fields - or else need to include ChargeNoHTCode in the linking, in which case one of the Fitzgerald 20-hr records will not be retrieved.

[Access_Novice]

They won't link because qryBillingSummary has two records for Fitzgerald and each is for 20 hours. Since you include the hours in the link, there is no match.

Also, Roberts shows 4 records in WeekEndingDate because the query joins 2 records from each query to each other (2 x 2 = 4).

The grouping in the two queries is too refined - too many fields - or else need to include ChargeNoHTCode in the linking, in which case one of the Fitzgerald 20-hr records will not be retrieved.

I'm sure that I did this type of query before. Here is the reason why I included all of these fields.

First, the purpose of the Timesheet Summary query is to calculate a weekending date from the timesheet dates and also to sum hours for any duplicate records (because each record would consist of WeekEnding, FullName, Project and Hours; additionally remember that my original dataset consisted of hundreds of rows). The purpose of the Billing Summary query was also to sum hours for duplicate records.

Based on having created these summary queries, the reason why I added so many fields is because of this:

1. The first query, would show a high level pairing of timesheet hours and billed hours by WeekEnding date per person.
2. The second query that I created in the past, would be a bit more specific. It would pair up timesheet hours and billed hours by WeekEnding date + Person + ChargeNo.
3. The third query that I created in the past, would be the most refined. It would pair up timesheet hours and billed hours by WeekEnding date + Person + ChargeNo. + Line Item No.

I added many fields to those summary queries so that I can use both of those summary queries as sources for all 3 queries above. That was the idea. I did this in the last month or so, but unfortunately I have since deleted those queries. I just can't remember how I was able to extract and pair these up at a high level and also at a very refined level. When I did the high level (by Week Ending Date) query, I simply took what I needed (I just omitted the ChargeNo and the Line item). Then I included ChargeNo for the 2nd query and then Line Item for the 3rd query.

I really appreciate your feedback. You have been extremely helpful.

[June7]

I don't see how that can work.

For 1. I think will have to use the 2 queries in another 2 queries that further summarize the data by WeekEnding, ProjectName, FullName and link those 2 queries on those 3 fields or go back to the tables as source.

Then the same for 2 and 3 - although adding the additional fields for grouping and linking.

Personally, I would just use the tables. Here is example of all-in-one nested query statement.
SELECT qryBillingSum.*, qryTimesheetSum.SumOfHours
FROM
(SELECT [TimesheetDate]+7-Weekday([TimesheetDate],2) AS WeekEnding, [LastName] & ", " & [FirstName] AS Fullname, Timesheet.ProjectName, Sum(Timesheet.Hours) AS SumOfHours
FROM Timesheet
GROUP BY [TimesheetDate]+7-Weekday([TimesheetDate],2), [LastName] & ", " & [FirstName], Timesheet.ProjectName) AS qryTimesheetSum
LEFT JOIN
(SELECT Billing.WeekEnding, Billing.FullName, Billing.ProjectName, Sum(Billing.PortalHours) AS SumOfPortalHours
FROM Billing
GROUP BY Billing.WeekEnding, Billing.FullName, Billing.ProjectName) AS qryBillingSum
ON (qryTimesheetSum.WeekEnding = qryBillingSum.WeekEnding) AND (qryTimesheetSum.Fullname = qryBillingSum.FullName) AND (qryTimesheetSum.ProjectName = qryBillingSum.ProjectName);

Use WHERE and/or HAVING clause in each subquery if needed.

Now build 2 more nested queries for 2. and 3. with grouping and linking on additional fields.

[Access_Novice]

I don't see how that can work.

For 1. I think will have to use the 2 queries in another 2 queries that further summarize the data by WeekEnding, ProjectName, FullName and link those 2 queries on those 3 fields or go back to the tables as source.

Then the same for 2 and 3 - although adding the additional fields for grouping and linking.

Personally, I would just use the tables. Here is example of all-in-one nested query statement.
SELECT qryBillingSum.*, qryTimesheetSum.SumOfHours
FROM
(SELECT [TimesheetDate]+7-Weekday([TimesheetDate],2) AS WeekEnding, [LastName] & ", " & [FirstName] AS Fullname, Timesheet.ProjectName, Sum(Timesheet.Hours) AS SumOfHours
FROM Timesheet
GROUP BY [TimesheetDate]+7-Weekday([TimesheetDate],2), [LastName] & ", " & [FirstName], Timesheet.ProjectName) AS qryTimesheetSum
LEFT JOIN
(SELECT Billing.WeekEnding, Billing.FullName, Billing.ProjectName, Sum(Billing.PortalHours) AS SumOfPortalHours
FROM Billing
GROUP BY Billing.WeekEnding, Billing.FullName, Billing.ProjectName) AS qryBillingSum
ON (qryTimesheetSum.WeekEnding = qryBillingSum.WeekEnding) AND (qryTimesheetSum.Fullname = qryBillingSum.FullName) AND (qryTimesheetSum.ProjectName = qryBillingSum.ProjectName);

Use WHERE and/or HAVING clause in each subquery if needed.

Now build 2 more nested queries for 2. and 3. with grouping and linking on additional fields.

Wow that's a lot. I'll have to read over this a lot and give this some thought. Thank you again. I clicked "Add to your reputation."