Running a query on a few subtables at once and adding them together (HOW?)

[Compustudent]

I gave a database that consists of one main table with a few sub
tables linked in. and I am having a problem running a query on it.

The main table is for example "jobs", and the sub tables are who
participated in them, there is one table for driving, another for
being at headquarters, and one for assisting out.

I want to make a query that will count up the amount of jobs each
individual participated in, over a particular date range.
The date information is stored in the main table.
I don't know how to run a query that will add up how many times
someone did work in "driving" and how many times did work in
"assisting out", and add it together, so that in the end I will just
see that "john doe" worked on 15 jobs.
Thank You

 

[John W. Vinson]

I gave a database that consists of one main table with a few sub
tables linked in. and I am having a problem running a query on it.

The main table is for example "jobs", and the sub tables are who
participated in them, there is one table for driving, another for
being at headquarters, and one for assisting out.

Well... that's "storing data in tablenames", pretty much always a bad
idea. Could you consider instead having ONE related table, with an
additional field for the activity (driving, headquarters,
assisted...)? If these tables all have the same or very similar field
definitions, this would be a much simpler architecture.

I want to make a query that will count up the amount of jobs each
individual participated in, over a particular date range.
The date information is stored in the main table.
I don't know how to run a query that will add up how many times
someone did work in "driving" and how many times did work in
"assisting out", and add it together, so that in the end I will just
see that "john doe" worked on 15 jobs.
Thank You

With your current design it'll be difficult but not impossible. You'll
perhaps need a UNION query to string together all these subtables.
Care to post some relevant fieldnames and datatypes? We can probably
help set this up.

John W. Vinson [MVP]

 

[Compustudent]

Well... that's "storing data in tablenames", pretty much always a bad
idea. Could you consider instead having ONE related table, with an
additional field for the activity (driving, headquarters,
assisted...)? If these tables all have the same or very similar field
definitions, this would be a much simpler architecture.


With your current design it'll be difficult but not impossible. You'll
perhaps need a UNION query to string together all these subtables.
Care to post some relevant fieldnames and datatypes? We can probably
help set this up.

John W. Vinson [MVP]

Thank You

The main table is called Run Info
One Sub table is called Crew-Rig, another is Crew-HQ,and the other is
Crew-Scene.

But I just started using the database. What is a better way for me to
have it set up, I can redo it. The reason why I went this way, is that
not always will you have the same amount of people on each section,
and sometimes there wont be anyone in a particular section.

Thankx

 

[John W. Vinson]

Thank You

The main table is called Run Info
One Sub table is called Crew-Rig, another is Crew-HQ,and the other is
Crew-Scene.

But I just started using the database. What is a better way for me to
have it set up, I can redo it. The reason why I went this way, is that
not always will you have the same amount of people on each section,
and sometimes there wont be anyone in a particular section.

Thankx

All the more reason to have just ONE Crew table, with an additional
field - Location maybe, with values Rig, HQ, and Scene (and possibly
additional values in the future, which would be easy with the new
structure but require a total database restructuring in yours).

You can add as many people into this new table as are needed, with any
needed number of records in each location.

John W. Vinson [MVP]