Calcuation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have two tables, i.e TableSales and TableCost, which include five products
(say A, B, C, D & E). I would like to calcuate the profit/loss from
individual product,i.e. Product A sales less Product A Cost. Please advise
what I should do. Thanks a lot.
Roy
 
aggregate said:
Hi,
I have two tables, i.e TableSales and TableCost, which include five
products (say A, B, C, D & E). I would like to calcuate the
profit/loss from individual product,i.e. Product A sales less Product
A Cost. Please advise what I should do. Thanks a lot.
Roy
Click to expand...

Relate the two tables on trhe product ID and use a calculated field.
 
Hi,
I have two tables, i.e TableSales and TableCost, which include five products
(say A, B, C, D & E). I would like to calcuate the profit/loss from
individual product,i.e. Product A sales less Product A Cost. Please advise
what I should do. Thanks a lot.
Roy
Click to expand...

Create a Query joining the two tables by the unique ProductID field.
You may need a Totals query if there are multiple sales per product
(you don't say). In a blank Field cell type

Profit: [Sales] - [Cost]

I *HOPE* you aren't using five fields for the five products. You
should have one *record* per product. If this isn't making sense,
please post a list of the fields in each of your tables.

John W. Vinson[MVP]
 
you could do something along these lines..
create a query that joins the two tables on the ProductID field(A,B,C
etc.) Then in an empty column in the query, in the top row, type the
expression:
Profit_Loss: TableSales.Sales - TableCost.Cost
when you run the query, the calculated 'Profit_Loss' field should
appear in the Query Results

Mark
(e-mail address removed)(no dashes)
http://access-pro.tripod.com
 
I tried to relate the two table and total the profit in Query. The result is
not working. I found that products sold twice would create four matching
entries, product sold at three times create nine matching entries. I now
post the tables as below:
Tablecost TableSale
Date ID Cost Date ID
Sales
Day 1 A 3000 Day1 A
4560
Day 2 B 5000 Day2 B
6822
Day 3 C 3126 Day3 C
2589
Day 4 A 4800 Day4 A
5000
Day 5 B 6500 Day5 B
6000
Day 6 B 6000 Day6 B
6800
Day 7 D 1000 Day7 D
1200
Day 8 C 1500 Day8 C
1900
Day 9 E 2000 Day9 E
2300

PLEASE advise


John Vinson said:
Hi,
I have two tables, i.e TableSales and TableCost, which include five products
(say A, B, C, D & E). I would like to calcuate the profit/loss from
individual product,i.e. Product A sales less Product A Cost. Please advise
what I should do. Thanks a lot.
Roy
Click to expand...

Create a Query joining the two tables by the unique ProductID field.
You may need a Totals query if there are multiple sales per product
(you don't say). In a blank Field cell type

Profit: [Sales] - [Cost]

I *HOPE* you aren't using five fields for the five products. You
should have one *record* per product. If this isn't making sense,
please post a list of the fields in each of your tables.

John W. Vinson[MVP]
Click to expand...
 
I tried to relate the two table and total the profit in Query. The result is
not working. I found that products sold twice would create four matching
entries, product sold at three times create nine matching entries. I now
post the tables as below:
Click to expand...

You'll need to base a Query on Queries, then.

First create a Query based on Sales; click the Greek Sigma icon to
make it a Totals query. Group By the ProductID and Sum the Sales.

Then create another query based on Costs; do likewise.

Finally, create a third query *joining these two queries* by
ProductID, and put the calculated field as in my previous post.

John W. Vinson[MVP]
 
Thanks to your kind advice. It works to calcuate the profit and loss for
each product but when I insert the transaction date (i.e.sales or cost date)
into the query, the records were duplicate. In my side, I need the date in
the report for analysis. Pls advise me.

Roy
 
Thanks to your kind advice. It works to calcuate the profit and loss for
each product but when I insert the transaction date (i.e.sales or cost date)
into the query, the records were duplicate. In my side, I need the date in
the report for analysis. Pls advise me.
Click to expand...

That makes no sense to me. If there are multiple sales dates, which
"date" do you need in the report?

What sort of date criterion are you using? Could you post the SQL view
of the three queries which are giving duplicates?

John W. Vinson[MVP]
 
I solve the problem by inserting date into the third query with both the
Product ID and Date linking between the first two queries. Thank you very
much. But now I have another problem that how I can use access to write the
query in order to have the following result:

Date Product ID Cost Sales Variance
Day 1 A 1000 1200 200
Day 2 B NULL 1600 1600
Day 3 C 2000 NULL -2000

Please advise. Your kind advice is much appreciated.
 
I solve the problem by inserting date into the third query with both the
Product ID and Date linking between the first two queries. Thank you very
much. But now I have another problem that how I can use access to write the
query in order to have the following result:

Date Product ID Cost Sales Variance
Day 1 A 1000 1200 200
Day 2 B NULL 1600 1600
Day 3 C 2000 NULL -2000

Please advise. Your kind advice is much appreciated.
Click to expand...

You can use the NZ() function to convert Nulls to 0 - i.e.

Variance: NZ(Sales) - NZ(Cost)

If you wish to see the word NULL in place of the default blank, try
setting the Format property of each field to

#;-#;0;"NULL"

to specify the format for positive, negative, zero and null values
respectively.

John W. Vinson[MVP]
 
Thank you.
Roy

John Vinson said:
You can use the NZ() function to convert Nulls to 0 - i.e.

Variance: NZ(Sales) - NZ(Cost)

If you wish to see the word NULL in place of the default blank, try
setting the Format property of each field to

#;-#;0;"NULL"

to specify the format for positive, negative, zero and null values
respectively.

John W. Vinson[MVP]
Click to expand...
 
Back
Top