Query Subtotal

[bgwool]

I'm developing a payroll database and having a lot of trouble creating a query to determine the total bill for each project (obviously an important part). Each of the [Bill#] entries in the query is associated with a specific survey code [Line#Code]. I need a query to sum all of the [Bill#] entries that have the same survey code. I'm having difficulty figuring out how to relate all of the separate [Line#Code] field values to one survey code. I've looked at UNION queries but haven't had any success so far.

This is the SQL of the query that has all the information the new query should need. I need the report display something like this:



Survey Code: Total Bill:
0500 $10,253.50
0550 $15,234.33

etc.

SELECT AllElapsedTimes.Line1Code, AllElapsedTimes.Line2Code, AllElapsedTimes.Line3Code, AllElapsedTimes.Line4Code, AllElapsedTimes.Line5Code, AllElapsedTimes.Line6Code, AllElapsedTimes.Line7Code, AllElapsedTimes.Line8Code, AllElapsedTimes.Bill1, AllElapsedTimes.Bill2, AllElapsedTimes.Bill3, AllElapsedTimes.Bill4, AllElapsedTimes.Bill5, AllElapsedTimes.Bill6, AllElapsedTimes.Bill7, AllElapsedTimes.Bill8
FROM AllElapsedTimes, [Survey Codes]
GROUP BY AllElapsedTimes.Line1Code, AllElapsedTimes.Line2Code, AllElapsedTimes.Line3Code, AllElapsedTimes.Line4Code, AllElapsedTimes.Line5Code, AllElapsedTimes.Line6Code, AllElapsedTimes.Line7Code, AllElapsedTimes.Line8Code, AllElapsedTimes.Bill1, AllElapsedTimes.Bill2, AllElapsedTimes.Bill3, AllElapsedTimes.Bill4, AllElapsedTimes.Bill5, AllElapsedTimes.Bill6, AllElapsedTimes.Bill7, AllElapsedTimes.Bill8;

[June7]

Appears your data is not normalized. Use UNION query to rearrange data into normalized structure.

Use that query in subsequent queries.

Post example of data from the two tables.

Advise no spaces in naming convention.

[bgwool]

Thank you. I have thankfully figured out the issue. It was mostly a matter adjusting the relationship details. I appreciate the help!