Sum columns in query used in sum column for another query

[Coleman984]

I have 3 queries that have a column titled TotalPoints which uses the below formula to get the information:

Then I want to have a 4th query that uses the column created by the below formula to summarize the 3 queries that contain the below formula.

The problem I have is getting the information to show correctly, what would be the proper way to create relationships between the tables? The common field between all tables / queries is EmployeeID. The tables used to create the queries are:

Weekof17July2011
Weekof24July2011
Weekof31July2011
tblEmployeeNames

The queries are named as follows:
Weekof17July2011_Query
Weekof24July2011_Query
Weekof31July2011_Query
TotalPoints (this is the query not working)

TotalPoints: IIf([Sunday]="NCNS" Or [Sunday]="ABS",1,IIf([Sunday]="LE" Or [Sunday]="late",0.5,0))+IIf([Monday]="NCNS" Or [Monday]="ABS",1,IIf([Monday]="LE" Or [Monday]="late",0.5,0))+IIf([Tuesday]="NCNS" Or [Tuesday]="ABS",1,IIf([Tuesday]="LE" Or [Tuesday]="late",0.5,0))+IIf([Wednesday]="NCNS" Or [Wednesday]="ABS",1,IIf([Wednesday]="LE" Or [Wednesday]="late",0.5,0))+IIf([Thursday]="NCNS" Or [Thursday]="ABS",1,IIf([Thursday]="LE" Or [Thursday]="late",0.5,0))+IIf([Friday]="NCNS" Or [Friday]="ABS",1,IIf([Friday]="LE" Or [Friday]="late",0.5,0))+IIf([Saturday]="NCNS" Or [Saturday]="ABS",1,IIf([Saturday]="LE" Or [Saturday]="late",0.5,0))

 

[spikepl]

Your table structure is not good.. Same type of data just needs tagging by date and be stored in one table

 

[plog]

Agree with spike. You properly normalize your data and this issue becomes trivial.

You are essentially using the table name to store part of the data that should be stored in a field in your table. What you need to do is create one table with all the fields you currently have, plus one new date field called something like 'DataWeek' in which you store the date for which the data is for.

For example, all the data currently in 'Weekof17July2011' would have 7/17/2011 in that new field, 'all the data in 'Weekof24July2011' would have 7/24/2011 in that new field, etc. Once you have all your data in this new table you can run your Sum query just like you do for an individual week.