What's Wrong With This Query?

[Robert T]

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;

 

[scubadiver]

Where does "LtblRegionalOffices" come from in your query? Try it without.

 

[Robert T]

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,
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:

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;

 

[John Spencer]

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:

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,
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:

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;

 

[Robert T]

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
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:

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,
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


Robert T wrote:
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;

 

[Robert T]

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
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:

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,
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


Robert T wrote:
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;

 

[Robert T]

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