Issue with "Group by" in a query

[Chatholo]

Hi,



I am trying to compare the data of two tables through a query, I thought that was easy but it looks like I am running into at least a couplea of issues.

The first one is I want to compare based on the LHSID item but as you can see in my query datasheedt it doesn't fully group it - I have 3 lines. I am guessing it is due to the 1 to many relashionship but not to sure how to fix it.

The second problem is that my last field is a calculated field to check the variance of the two other columns, i have tried to change the "Total" criteria but then it says it doesn't recognize it.....

Any help is appreciated! The aim is really onlyu to compare these two tables based on the LHSID.

[stmoong]

Try the following.

1) Remove the grouping.
2) Remove the tblProgramBudget.LHSID field.
3) Double click on the join (the line between the table).
4) The "Join Properties" window will be displayed. Choose option 2, which is to include ALL records from 'tblProgramList' and only those records from 'tblProgramBudget' where the joined fields are equal.
5) Click OK.

The query should work now.

[Chatholo]

Thanks Stan. I just tried it but it doesn't give me the expected result. My join is already on #2, hence the reason why (i think) i end up with a long list of item not grouped by LHSID. Also I need to keep the tblProgramBudget.LHSID field otherwise I won't know which items are in this table and not in the Program list one ...

Basically I want to end up to something like that, grouped by LHSID:
LHSID (Tblprogramlist) - LHSID (Tblbudget) - Heads (Tblprogramlist) - Heads (Tblbudget) - Check (calculated field)

C.

[stmoong]

By right, shouldn't all LHSID be in tblProgramList since that is the master table?

If the are LHSID in tblProgramBudget but not found in tblProgramList, then you have missing master data.

From the screenshot, it doesn't appear to be Left Join. The screenshot shows one where you will only get results for matching records.

The grouping is normally not necessary, because tblProgramList will have unique LHSID. Unless you have many records in tblProgramBudget that have the same Salary Heads or have no values, then probably grouping would be useful.

[Chatholo]

My tblProgramList is my source data, the other one is manipulated data, linked to a form and split by region, business etc... I am trying to create a query that will check that the new allocations still match the original numbers. That's why I have two tables with the same ID.

[stmoong]

Your tblProgramList and tblProgramBudget is a 1-to-many relationship. But your query join property does not appear to be set to Left Join.

There is however a problem in the tblProgramBudget in that no primary key is specified to uniquely identify each record. You can use autonumber as the primary key. Using LHSID to update record in tblProgramBudget is dangerous, because you will update all the records with the same LHSID in tblProgramBudget.

[Chatholo]

Here is a screenshot of my join properties - I am confused, is this what you call "left join". I set it to what you mentionned in the first reply #2.

In regards to the Key I guess i can add it to autonumber no issue with that.
For the last one, even though I am using LHSID to update records I am not update in the same field, so i don't believe (but I am new in access) that it will be an issue. the initial one is called [average heads] and my new input [Heads]

[stmoong]

Yes, the latest screenshot shows the left join for tblProgramList.

With the autonumber ID field, you basically tell Access to update the record with the ID, which is unique. If you use the LHSID to update the tblProgramBudget, then you can end up with more than 1 records being updated.

For example, you have 10 records in tblProgramBudget with the LHSID="0010". If you tell Access to update the salary for records with LHSID="0010", then, all 10 records will be updated.

[Chatholo]

I appreciate your help, but I don't understand how that helps me with my initial question on how to perform a "variance" check between these two tables.

[stmoong]

If the relationship of the data is correct, then the query is relatively easy.

I created these sample tables and query to illustrate the concept.

tblProgramList:

tblProgramBudget:

ProgramQuery:

Result:

[Chatholo]

Thanks Stan. This is what I am after but on the result table how can I have only 1 line by LHSID type?

[stmoong]

If your requirement is to get the average variance, then you need to modify the query to something like this.