joining queries

[kevcar40]

Hi
i have a table which records the number of cards issued to a team
fields are

Team text
Numof yellow integer
numor Red integer
date yellow short date
date red short date

query1
assigns years months and weeknumber to both date yellow and date red

query 2
returns team numof yellow total of cards based on a year ie 2005

query 3
returns team numof red total of cards based on a year ie 2005

what i want to do now is put join query2 and query3 to get a combine
result

ie Team numof yellow numof red Total
team 1 4 2 6
team 2 2 3 5
team 3 6 4 10

what i am getting is duplicate record
ie

Team numof yellow numof red Total
team 1 2 2
team 1 4 4
team 2 3 3
team 2 2 2
team 3 6 6
team 3 4 4

Can anyone please tell me how to resolve this

thanks

kevin

[Douglas J. Steele]

Assuming that your table is fully populated, you should be able to do it in
a single query:

SELECT Team, Sum(NumOfYellow ) AS TotalYellow,
Sum(NumOfRed) AS TotalRed,
Sum(NumOfYellow ) + Sum(NumOfRed) AS Total
FROM MyTable
WHERE Year([DateYellow]) = 2005
OR Year([DateRed]) = 2005
GROUP BY Team

(Warning: the above is untested air code. You may have to futz around with
it a little bit...)

The easiest way is to type the SQL in directly, rather than trying to do
this through the graphical query designer. When the query's in Design mode,
choose SQL view on the View menu.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"kevcar40" <ke******@btint ernet.com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

Hi
i have a table which records the number of cards issued to a team
fields are

Team text
Numof yellow integer
numor Red integer
date yellow short date
date red short date

query1
assigns years months and weeknumber to both date yellow and date red

query 2
returns team numof yellow total of cards based on a year ie 2005

query 3
returns team numof red total of cards based on a year ie 2005

what i want to do now is put join query2 and query3 to get a combine
result

ie Team numof yellow numof red Total
team 1 4 2 6
team 2 2 3 5
team 3 6 4 10

what i am getting is duplicate record
ie

Team numof yellow numof red Total
team 1 2 2
team 1 4 4
team 2 3 3
team 2 2 2
team 3 6 6
team 3 4 4

Can anyone please tell me how to resolve this

thanks

kevin

[kevcar40]

THanks

[kevcar40]

hi again
if i include red_month and yellow_month can i still use the same
process
ie year 2005 and month june
i have tried altering the query but it is returning everything

thanks again

kevin

[kevcar40]

hi again
if i include red_month and yellow_month can i still use the same
process
ie year 2005 and month june
i have tried altering the query but it is returning everything

thanks again

kevin