By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 803 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

Having problem creating an SQL stat. for a report

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

Aug 10 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Aug 10 '06 #2

P: n/a
"erick-flores" <ch**********@hotmail.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.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

Aug 10 '06 #3

P: n/a
I got it...thank you guys

Aug 10 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.