Having problem creating an SQL stat. for a report

[erick-flores]

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].ExpenseDetailI D, [Expense Details].ExpenseItemAmo unt, [Expense
Details].ExpenseItemDes cription, [Expense Details].ExpenseDate,
[Expense Categories].ExpenseCategor y, Employees.Emplo yeeNumber,
Employees.First Name, Employees.LastN ame, [Expense Details
Co].[EnpenseDetailID Co], [Expense Details Co].[ExpenseItemAmou nt Co],
[Expense Details Co].[ExpenseItemDesc ription Co], [Expense Details
Co].[ExpenseDate Co]
FROM (Employees INNER JOIN ([Expense Reports] INNER JOIN [Expense
Details Co] ON [Expense Reports].ExpenseReportI D = [Expense Details
Co].[ExpenseReportID Co]) ON Employees.Emplo yeeID = [Expense
Reports].EmployeeID) INNER JOIN ([Expense Categories] RIGHT JOIN
[Expense Details] ON [Expense Categories].ExpenseCategor yID = [Expense
Details].ExpenseCategor yID) ON [Expense Reports].ExpenseReportI D =
[Expense Details].ExpenseReportI D
WHERE ((([Expense Reports].ExpenseReportI D)=[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

[pietlinden]

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.

[Bob Quintal]

"erick-flores" <ch**********@h otmail.comwrote in
news:11******** **************@ m79g2000cwm.goo glegroups.com:

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.

Thank you

--
Bob Quintal

PA is y I've altered my email address.

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

[erick-flores]

I got it...thank you guys