 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008What's Wrong With This Query?
Hi: I just created a Travel Expense database so there are very few entries in the child table called tblExpenses. I want to create a subreport so I created a query that groups on TravelID [each trip has a unique TravelID], the TypeExpense [Air, Hotel, etc.] and then I want to sum up the total expense in each category. Here's what the table data looks like, I hope the columns line up: TravelID DateExpenseTypeExpense AmountExpense ---------------------------------------------------------------------- 001 5/12/2008 Hotel $201.64 001 5/12/2008 Taxi $10.00 001 5/12/2008 Airfare $399.00 001 5/13/2008 Hotel $201.64 001 5/14/2008 Hotel $201.64 001 5/12/2008 Per Diem $40.50 001 5/13/2008 Per Diem $54.00 001 5/15/2008 Hotel $201.64 001 5/14/2008 Per Diem $54.00 001 5/15/2008 Per Diem $54.00 001 5/16/2008 Taxi $30.00 001 5/16/2008 Per Diem $40.50 001 5/16/2008 Airfare $399.00 001 5/12/2008 Mileage $21.72 001 5/16/2008 Mileage $21.72 001 5/16/2008 Tip $10.00 002 6/9/2008 Airfare $350.50 002 6/9/2008 Car Rental $40.72 002 6/9/2008 Hotel $131.10 003 6/16/2008 Airfare $150.50 003 6/20/2008 Airfare $369.00 I created what I thought would be a simple totals query and the numbers are way off. The totals are about ten times higher than they should be so I know I'm doing something STUPID! Here's the query: SELECT tblExpenses.TravelID, tblExpenses.TypeExpense, Sum(tblExpenses.AmountExpense) AS SumOfAmountExpense FROM tblExpenses, LtblRegionalOffices GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; You're very, very close to what you need. You've created a cartesian query, where for every row in one table, it displays each row in the second table, even if there's no logical correlation. If your totals are 10 times what you expect them to be, you have 10 rows in the second table. Remove the second table, LtblRegionalOffices, from your syntax, and you'll find the totals much more to your liking. SELECT tblExpenses.TravelID, tblExpenses.TypeExpense, Sum(tblExpenses.AmountExpense) AS SumOfAmountExpense FROM tblExpenses GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; Thank you for showing sample data, but for future reference, if you want to give sample data to test with, please put it in an easy-to-import format. Commas between each item in each row would have taken seconds to copy/paste into a text file or spreadsheet, then import into a table in Access, but copy/pasting your data into a text file or spreadsheet to import into Access took a lot of manipulation to get the 27 columns to line up into 4. And yes, I know I'm looking a gift horse in the mouth, but the faster people can import your data to test with, the faster you'll get a response that will help you. Chris Microsoft MVP >Hi: > [quoted text clipped - 39 lines] >FROM tblExpenses, LtblRegionalOffices >GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; Chris: Thanks so much. Of course you were right, however, I placed LtblRegionalOffices in the query because it has the name of each office. Without that info, when I design a report based on this query, it's grouping expenses by TraveLID as opposed to Office Name. How do I solve that problem? Sorry about the data issue. Thanks > You're very, very close to what you need. You've created a cartesian query, > where for every row in one table, it displays each row in the second table, [quoted text clipped - 66 lines] > >FROM tblExpenses, LtblRegionalOffices > >GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; Well, you may know how the data in expenses tables relates to the data in lTblRegionalOffices, but we don't. And possibly your data structure doesn't either. Is there a field in expenses that will tell you which record in tblRegionalOffices the expense is for? John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Chris: > [quoted text clipped - 77 lines] >> >FROM tblExpenses, LtblRegionalOffices >>> GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; John: You guessed right. LtblRegionalOffices is nothing more than a lookup table for a field in the parent table called RegionalOffice. There is no relationship so it cannot tell Access which record. Robert > Well, you may know how the data in expenses tables relates to the data in > lTblRegionalOffices, but we don't. And possibly your data structure doesn't [quoted text clipped - 89 lines] > >> >FROM tblExpenses, LtblRegionalOffices > >>> GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; John: I think I inadvertently posted a blank response, sorry about that. John, your guess was correct. LtblRegionalOffices is nothing more than a lookup table for a field in the parent table called RegionalOffice. There is no relationship so Access cannot tell which record it's for. Robert > Well, you may know how the data in expenses tables relates to the data in > lTblRegionalOffices, but we don't. And possibly your data structure doesn't [quoted text clipped - 89 lines] > >> >FROM tblExpenses, LtblRegionalOffices > >>> GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; You can solve it by joining two or more tables in your query. Logically, the LtblRegionalOffices has the regional office info, but not the TravelID that's included in the tblExpenses table to somehow associate a regional office with a particular expense. So that means a third table is involved, which includes TravelIDs and regional office names (or regional office IDs if you're using surrogate keys). So to build a query that associates an expense with a TravelID that associates with a regional office, we'll need to know the structure of those two other tables. Namely, the names of the tables, their primary keys, and whatever other field names need to be shown in the query. And it's possible that I'm guessing wrong on your db design, and you have it designed differently. Explain what you have, and we'll try to help you. Chris Microsoft MVP >Chris: > >Thanks so much. Of course you were right, however, I placed >LtblRegionalOffices in the query because it has the name of each office. >Without that info, when I design a report based on this query, it's grouping >expenses by TraveLID as opposed to Office Name. How do I solve that problem? Hi Chris: I really appreciate you taking the time to help. Unforunately I've been so busy today that I haven't had time to respond promptley. The Parent Table is called tblTravel. It has a PK called TravelID that is a TEXT field. It tracks the Start/EndDate of travel, the name of the office where I'm traveling [LtblRegionalOffices], whether I'm flying, taking a train, hotel info, etc. The child table is called TblExpenses. It has an Auto Number field but it's linked to the Parent table via the TravelID text field. The other fields are: DateExpense TypeExpense [Text - Hotel, Airfare, Taxi, Train, etc.] AmountExpense [Currency] Note [Text] Parent Table -------------- There is a text field called RegionalOffice and there is a combo box that looks up the RegionalOffices from the table called LtblRegionalOffices. This is a one field table, the only field is called RegionalOffice. Obviously it lists all of the offices where I must travel. Hopefully this helped. Robert > You can solve it by joining two or more tables in your query. Logically, the > LtblRegionalOffices has the regional office info, but not the TravelID that's [quoted text clipped - 20 lines] > >Without that info, when I design a report based on this query, it's grouping > >expenses by TraveLID as opposed to Office Name. How do I solve that problem? Try this query: SELECT LtblRegionalOffices, TypeExpense, CCUR(Sum(AmountExpense)) AS SumOfAmtExpense FROM tblExpenses INNER JOIN tblTravel ON tblExpenses.TravelID = tblTravel. TravelID GROUP BY LtblRegionalOffices, TypeExpense; Chris Microsoft MVP >Hi Chris: > [quoted text clipped - 22 lines] >is a one field table, the only field is called RegionalOffice. Obviously it >lists all of the offices where I must travel. Where does "LtblRegionalOffices" come from in your query? Try it without. > Hi: > [quoted text clipped - 39 lines] > FROM tblExpenses, LtblRegionalOffices > GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense; |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.