Join query Problem

[willy292]

I have two query called query1 and query3 having the following structure

Query1
Project Name, Project desc, start Date,end date,sponsor, TeamA, TeamB, Team C

The value of TeamA,TeamB,TeamC is -1 or 0 which is orignally the Yes/No boxes from table , A project can be catered by more than 1 team together which means TeamA and TeamB or even TeamC can be -1 on the same row.
This query show the outstanding project.

Query3
Project Name, Working hr,Date,TeamA,TeamB,TeamC

This query show the working hr within weeks in each team, only One team
can be allocated for each record, TeamA,TeamB,TeamC is Yes/No box.
Not all the outstanding projects appear in query 3 because some project may be pending.

Aim
Return for each project, how many working hours have been spent for each team

 

[Mihail]

You have a (very) poor design that elude the main rule for databases: normalization.
So, first, understand this concept (normalization), then, redesign your database, then ask again.
My opinion, of course.
Good luck !

 

[JHB]

Query3
Project Name, Working hr,Date,TeamA,TeamB,TeamC

This query show the working hr within weeks in each team, only One team
can be allocated for each record, TeamA,TeamB,TeamC is Yes/No box.
Not all the outstanding projects appear in query 3 because some project may be pending.

Aim
Return for each project, how many working hours have been spent for each team

I agree with Mihail, but it doesn't help you much now, when you have the data in.
I think you can get what you wish by running a union query on query3, look in the Help file for Union query. I you wish further help, post some sample data from query3 in an Excel-file or database file, (zip it).

 

[willy292]

Thanks JHB, I finally completed by implementing a left join, the database is not first design by me and I know it is poor but as JHB said, some data have be in the table and I have no choice but try to get over it. Thanks you guys.