Connecting Tech Pros Worldwide Help | Site Map

Having problem creating an SQL stat. for a report

  #1  
Old August 10th, 2006, 06:15 PM
erick-flores
Guest
 
Posts: n/a
Hello all

I am creating an Expense Report. There are to types of expenses:
Company expenses and Reimb Expeneses. The company expenses is under
"Expense Details Co" table and Reimb Expenses is under "Expense
Details" table. So I am creating this final report to see both type of
expenses separately but I CANT.
I got this report design from a website, but it only came with one
"Expense Details" table, so I add the Expense Details Co. The report
worked fine with only Expense Details but now that I am trying to add
the other table it wont work quite well for my final report.
Here is the SQL stat. that I am using to display the report, but again,
is not working the way I want.
I want to see in one group the Expense Details and on other group the
Expense Details Co.

SELECT DISTINCTROW [Expense Reports].*, [Expense
Details].ExpenseDetailID, [Expense Details].ExpenseItemAmount, [Expense
Details].ExpenseItemDescription, [Expense Details].ExpenseDate,
[Expense Categories].ExpenseCategory, Employees.EmployeeNumber,
Employees.FirstName, Employees.LastName, [Expense Details
Co].[EnpenseDetailID Co], [Expense Details Co].[ExpenseItemAmount Co],
[Expense Details Co].[ExpenseItemDescription Co], [Expense Details
Co].[ExpenseDate Co]
FROM (Employees INNER JOIN ([Expense Reports] INNER JOIN [Expense
Details Co] ON [Expense Reports].ExpenseReportID = [Expense Details
Co].[ExpenseReportID Co]) ON Employees.EmployeeID = [Expense
Reports].EmployeeID) INNER JOIN ([Expense Categories] RIGHT JOIN
[Expense Details] ON [Expense Categories].ExpenseCategoryID = [Expense
Details].ExpenseCategoryID) ON [Expense Reports].ExpenseReportID =
[Expense Details].ExpenseReportID
WHERE ((([Expense Reports].ExpenseReportID)=[forms]![Expense
Reports]![ExpenseReportID]));

I am almost sure that I have something wrong in the SQL but I dont know
where exactly..please help me

Thank you

  #2  
Old August 10th, 2006, 09:35 PM
pietlinden@hotmail.com
Guest
 
Posts: n/a

re: Having problem creating an SQL stat. for a report


Anything keeping you from creating two separate reports and then
dropping them into another report? then they'll have separate
rowsources and that's perfectly fine.

otherwise, you need to union the two tables together. Makes me wonder
why the data isn't all in one table to begin with. Just add a field to
the table so that you can distinguish what kind of expense it is.

  #3  
Old August 10th, 2006, 11:15 PM
Bob Quintal
Guest
 
Posts: n/a

re: Having problem creating an SQL stat. for a report


"erick-flores" <chunkyflores@hotmail.comwrote in
news:1155230597.116641.255890@m79g2000cwm.googlegr oups.com:
Quote:
Hello all
>
I am creating an Expense Report. There are to types of
expenses: Company expenses and Reimb Expeneses. The company
expenses is under "Expense Details Co" table and Reimb
Expenses is under "Expense Details" table. So I am creating
this final report to see both type of expenses separately but
I CANT. I got this report design from a website, but it only
came with one "Expense Details" table, so I add the Expense
Details Co. The report worked fine with only Expense Details
but now that I am trying to add the other table it wont work
quite well for my final report.
That's because you should not have added the second table. What you
should have done was put a new column called expense_type, (I'd use
text, 1 character, validation to allow only 'C' or 'R' and no nulls
allowed.
Quote:
Thank you
>


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

  #4  
Old August 11th, 2006, 12:05 AM
erick-flores
Guest
 
Posts: n/a

re: Having problem creating an SQL stat. for a report


I got it...thank you guys

Closed Thread