Solved wrong results in report based on query (1 Viewer)

[Ahmed73]

Hi everyone,
I made a Sale-Purchase database, forms are working fine but sales results reports is not appearing correctly.
For instance, I am trying to make a report in which I want to show the Sale price - Cost = Profit. But result is not correct.
Data in forms like this:
Item 1 Price: USD 10
Item 2: Price: USD 20
Total: USD 30
Packing: USD 5

The result on report should be 10+20+5 = USD 35 but result is coming USD 40, as it is repeating USD 5 packing charges for both line items.
Please suggest to resolve.

Thanks.

 

[plog]

This is just a query issue. So talking about forms and reports just obfuscate the matter. To solve this we need to see your data in the tables and your query. Can you post your SQL for starters?

 

[Ahmed73]

This is just a query issue. So talking about forms and reports just obfuscate the matter. To solve this we need to see your data in the tables and your query. Can you post your SQL for starters?

Thank you. should I send here the relevant table and query?

 

[plog]

Yes please. Put just what I need in a seperate database, zip it and upload to the forum.

 

[Pat Hartman]

as it is repeating USD 5 packing charges for both line items.

The packing charge is in the wrong table. It should be in the order table where it occurs only once instead of once for each item. To solve the problem without fixing the root cause (poor table design), you need to create a query that sums the Item but uses some other function such as Min() for the packing amount. Then you can create another query that adds the SumAmt field to the MaxPacking field.

 

[Ahmed73]

Yes please. Put just what I need in a seperate database, zip it and upload to the forum.

Luckily the results are now fine but unfortunately I did it unprofessionally. There is another issue report is not showing currency.
Please check the attachment.

 

[Ahmed73]

The packing charge is in the wrong table. It should be in the order table where it occurs only once instead of once for each item. To solve the problem without fixing the root cause (poor table design), you need to create a query that sums the Item but uses some other function such as Min() for the packing amount. Then you can create another query that adds the SumAmt field to the MaxPacking field.

Thanks Pat. I attached related table, query and report on above reply.

 

[Pat Hartman]

It is always best to actually fix the root cause. From now until this app dies, you will be working around the design flaw. It really isn't more work to fix the actual problem than it is to add a bandaid.

If you want a field on a report to have a currency format, you have to set the format property on the control on the report.

IMPORTANT - you are NOT joining your tables on the correct fields. You MUST fix this design flaw before moving on. You can't just put a bandaid on it.

 

[Ahmed73]

It is always best to actually fix the root cause. From now until this app dies, you will be working around the design flaw. It really isn't more work to fix the actual problem than it is to add a bandaid.

If you want a field on a report to have a currency format, you have to set the format property on the control on the report.

IMPORTANT - you are NOT joining your tables on the correct fields. You MUST fix this design flaw before moving on. You can't just put a bandaid on it.

Thanks Pat. Let me work on root cause.
I have purchase table and sales table, should I kept purchase currency in different table and sales currency in different table?

 

[Pat Hartman]

Unless you want to futz around with currency symbols, don't use them. Just include the name of the currency.

 

[Ahmed73]

Unfortunately still I couldn't sort out the issue. Is any one can correct the query and associate report in attached database file.

 

[Pat Hartman]

What did you try? what do you want the report to show?

The formatting goes on the REPORT not in the query.

 

[Ahmed73]

What did you try? what do you want the report to show?

The formatting goes on the REPORT not in the query.

I got the result as shown in the report but it was work around and not professionally. What I want:
1. based on table, report query could be improved
2. currency name shown in report on sales side but on purchase side it is showing currency ID and not currency name.
3. how I can show a textbox data on report from a form.

Thanks.

 

[arnelgp]

the Currency is in your Currency table so you Join [Customer RQF] table with Currency table on [Customer RQF].Currentcy = [Currency].CurrencyID

 

[Ahmed73]

the Currency is in your Currency table so you Join [Customer RQF] table with Currency table on [Customer RQF].Currentcy = [Currency].CurrencyID

currency from currency table joining with Customer RFQ is appearing but another currency "PO Currency" from Supplier RFQ is not appearing on report.

 

[arnelgp]

your currency table is not in the db.
so i make a currency table.
look at your report in design view.
i use Dlookup() to get the currency name.

 

[Ahmed73]

your currency table is not in the db.
so i make a currency table.
look at your report in design view.
i use Dlookup() to get the currency name.

Thanks. It means you used one currency table for both Sales and Purchase, whereas, I was using two different records.
Please also mention how to get a textbox data in this report from a Form. This is because you see the Sales and Purchase currencies are different so simple subtracting purchase from sales did not give correct profit value. so I want to get a converted value from a form textbox to report.

 

[arnelgp]

Sales and Purchase currencies are different so simple subtracting purchase

you'll need to find a way how to convert them.
or during data entry, you enter the original currency and the prevailing conversion (to dollar) during that time.

 

[arnelgp]

on the report, the record with JPY, i think this is in dollar.
maybe wrong inputting. customer price and supplier price is small difference.
while 1 USD = 110.55, then it should be higher than than that in the report.

 

[Ahmed73]

you'll need to find a way how to convert them.
or during data entry, you enter the original currency and the prevailing conversion (to dollar) during that time.

Yes, I converted the amount on supplier order form but that textbox I want to populate on this report. When I used =[Forms]![SUPPLIER PO]![SupPOValGBP] on report textbox it giving error #Name