If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Running a query on a few subtables at once and adding them together (HOW?)
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 |
#2
|
|||
|
|||
Running a query on a few subtables at once and adding them together (HOW?)
|
#3
|
|||
|
|||
Running a query on a few subtables at once and adding them together (HOW?)
On Feb 11, 7:00 pm, John W. Vinson
wrote: On 11 Feb 2007 15:08:08 -0800, wrote: 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] 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 |
#4
|
|||
|
|||
Running a query on a few subtables at once and adding them together (HOW?)
|
Thread Tools | |
Display Modes | |
|
|