In my database I collect information from manual teams and mechanical teams seperately. Each report the amount of material produced and location as well as various other information unique to each team. I am trying to make a single query that will give me the total material produced from both teams in each location. The problem I a running into is, when I group by location and add columns summing for material produced manually and mechanicly, It only returns results in areas where both groups were working. Is there a way I can get it to dislplay all locations worked and assign a 0 value to the columns when that type of team did not work there? Thank you.
Multiple table queries
- Thread starter Funkbuqet
- Start date
When I do that, either way, and then try to run the query it give me an error that says: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement."
Which, I have no idea what that is asking me to do.
Which, I have no idea what that is asking me to do.
What if the tables are not directly related? Basicly what I am trying to do is this: I have 4 tables, one that identifies counties by 4 letter code (CountyID) and associates it with the actual county name. The second containcs a list of all the segments in all the couties. For Gulf county the CountyID is FLGU, and a segment in gulf county would be FLGU1-003 (<-- this is the SegmentID). there is a column in the Segments table for the CountyID that relates back to the Counties table. The next to tables containing all the information from the daily reports frilled out by our crews. One for manual operations and one for mechanical. Each line item in these tables is a report from one crew in one segment for a particular day. Each report contains the SegmentID (which is related tot the segmentID in the Segments table) in which they worked and the number of people in that crew, etc. I am trying to produce a query that will tell me per county the total number of workers etc. (both manual and mechanical). But when I have both the manual and mechanical reports in the query, even if the only feild i have entered below is the county names, and I run the query it only returns counties in which both crews worked. If i remove either the mechanical or the manual table from the query the other 3 table work perfectly together. I have been able to successfully make 2 seperate querries that return the results I need, but I am unable to find a way to combine the numbers into a singel total in a querry or report. Sorry if this is really confusing, but I can't figure out what is causing this. Thanks for the help.
To put it simply with SQL you have to Joins that are linear:
Table -> Table - Table or
Table - Table -> Table Not
Table <- Table -> Table
So to get around your problem you could consider combining the mechanical and manual tables into one, flagging each record with Mechanical or Manual
OR
By creating separate queries one for mechanical and one for manual and each having the SegmentID FLGU1-003 and add the values you want in an expression. Can you send up an example and I will have a look.
Simon
Table -> Table - Table or
Table - Table -> Table Not
Table <- Table -> Table
So to get around your problem you could consider combining the mechanical and manual tables into one, flagging each record with Mechanical or Manual
OR
By creating separate queries one for mechanical and one for manual and each having the SegmentID FLGU1-003 and add the values you want in an expression. Can you send up an example and I will have a look.
Simon
Ok I created 2 seperate queries, on to total up menual and one for mechanical, and tey work well. I created the below query for summing up totals per county. I only used info from the manual query to start with and it worked great - see below.
Then when I simply add the mechanical query to the upper section, everything goes to hell and I have no idea why. I haven't changed anything in the lower section, but it produces values when I run the query that seem to have no basis in reality. I can't for the life of me figure out how it is producing these numbers. - see below.
Is there anyway to add the values from one table to another, and put that value in a report? This seems so simple, but i can't seem to figure out why it cannot be done. Any assistance would be appreciated. Thank you.
Then when I simply add the mechanical query to the upper section, everything goes to hell and I have no idea why. I haven't changed anything in the lower section, but it produces values when I run the query that seem to have no basis in reality. I can't for the life of me figure out how it is producing these numbers. - see below.
Is there anyway to add the values from one table to another, and put that value in a report? This seems so simple, but i can't seem to figure out why it cannot be done. Any assistance would be appreciated. Thank you.
