Sum new name

[Andrzejek]

Are you "creating a table" or referencing the same table twice in your SELECT statement? Be specific.

Show the SELECT statement - the one that gives you the circular reference error.

Have fun.

---- Andy

 

[MICKI0220]

I am creating a table from another table....for example. I design a Make table query. I choose table PASUM1 to create PASUM.

 

[MICKI0220]

Here is the code:


SELECT PASUM1.[Job Number], PASUM1.[Employee ID], PASUM1.Phase, PASUM1.[Employee Name], PASUM1.[Social Security Number], PASUM1.[Type Code], Sum(([Hours])) AS Hours, Sum(([Amount of Wages])) AS [Amount of Wages] INTO PASUM
FROM PASUM1
GROUP BY PASUM1.[Job Number], PASUM1.[Employee ID], PASUM1.Phase, PASUM1.[Employee Name], PASUM1.[Social Security Number], PASUM1.[Type Code];

 

[SkipVought]

Hi,

What is your purpose for this new table?

Good db practice does not store aggregations generally, but rather employs a query, instead, to calculate such values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MICKI0220]

Several reports use this table....What has happened is that we have a huge Access program that utilizes a export from an old Dos based Accounting program....We are upgrading that program to a new one. The information that the software developer gave me is not summarized so I have to do it on the fly.....I could recreate every query, report etc to do this right, but we needed a quick fix and so far this was my thought. If I try to change it in all the locations it is needed I could be here for months and still not catch everything by the time we go live.

 

[dhookom]

You may need to use two queries. One generates SumOfHours and the final uses SumOfHours as Hours to get it back where you want it to be.

Duane
Hook'D on Access
MS Access MVP