Trouble setting up ~Gross - Deductions = Net~

[lilsewon]

~*~*EASY ONE*~*~ -!-!- Trouble with a setup -!-!-

Hi, I'm fairly new to access, so I'm sure the answer to this question isn't incredibly difficult, but I sure can't seem to figure it out, any help is greatly appreciated.

Say I have 2 tables: (1) Gross Sales, (2) Deductions

And I want to make a report: NET SALES by substracting Deductions from Gross Sales.

GROSS SALES contains the price of each individual sale with the date and a few other details.

DEDUCTIONS contains expenses, which include the price, the category, and the date of each deduction

I am having trouble finding a relationship between the two because they're not really related to each other, I just need to subtract one from the other to get a net total. But the report won't let me include 2 different tables unless they have a relationship.

If anyone can suggest a setup and/or relationships needed for this please reply, thank you very much.

 

[raskew]

Please describe the deductions in more detail.

 

[shay]

If you just want to subtract the total expenses from the sales total, this should do it...

NetTotal = DSum("[Sales]", "Gross Sales") - DSum("[Expenses]", "Deductions")

shay

 

[The_Doc_Man]

Actually, you can't do this. But you CAN change the problem very slightly to make it work. Before you attack this problem, you need to read thoroughly the HELP files on UNION queries. And in order to make this work, you need to define something that ties things together.

By that last sentence, I mean that you need to decide up front whether your expenses are tied back to individual sales or back to a general fund or back to a salesperson or a department code or whatever. The sales and expenses need to have certain fields in common besides just the dollar value of the total sale in order for this to work correctly and for your resultant reports to make sense. For example, you surely need dates on every transaction in or out.

Now write a UNION query. For sales (representing income), include the dollar value. For expenses (representing outgo), include the negative of the dollar value. A short and purely hypothetical example is...

SELECT [SaleDate] as XDate, [SaleAmount] as XAmount, [Department] as XDept, [SalesPerson] as XPerson, [MerchandiseCategory] as XCat FROM [SalesTable]
UNION
SELECT [ExpenseDate] as XDate, [B]-[/B] [ExpenseAmount] as XAmount, [Department] as XDept, [ResponsibleParty] as XPerson, [ExpenseCategory] as XCat FROM [ExpenseTable];

Now you have a query that includes fields XDate, XAmount, XDept, XPerson, and XCat, where your sales and expenses have been merged. To compute the net as (Sales - Expenses), you can do a Sum on XAmount.

You can sort the above by date, department, person, or category as you choose. If you force reports to break on date, you have a daily net profit report. If you break on department, you have a departmental profit report, and so on. Now, apply this principle to your situation.

Remember this about UNION queries: You MUST include the same number of fields in all components of a SELECT and you MUST apply the same names to those fields and they must appear in the same order in each segement of the UNION.

If there is a "disconnect" because you cannot include the same data in both (perhaps because one field doesn't exist in one of the tables, you are allowed to include a "fake entry" for that field in the individual SELECT clause. Like,

SELECT ... , "00" as XDept, ....

in the case where one of the two data sources is not associated with a real department. Also, if you have THREE tables to merge this way, a UNION query still works and the rules I mentioned still apply. I have personally done a six-way UNION query that worked just fine.