Solved wrong results in report based on query

[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.

 

[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.

 

[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?

 

[Ahmed73]

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

 

[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

 

[arnelgp]

does it get saved to a table?
i don't have any of your form.

 

[Ahmed73]

does it get saved to a table?
i don't have any of your form.

Now I attached herewith db. In supplier PO form, I am entering the conversion but it is not saving in table. I highlighted the fields in supplier PO form and in report as "YELLOW".

 

[Ahmed73]

1. You can join to the currency table twice in the same query. Just add it to the grid a second time. The second instance will have "_1" as a suffix. It is not a duplicate table it is a second reference so you can solve the problem of sales and purchases having different currency types.
2. It makes no sense to have the report in numerous currencies. You need to convert the currency to some common currency at some point. It is probably easiest to store everything in your common currency to facilitate reporting and calculation. Why would you want to do the conversion every time you need the value. You need to store the date of the conversion if it is not the same as the transaction date as well as the transaction currency.

1. noted.
2. Yes you are right but I am not good in designing and coding, so made the work around to get the desired results irrespective it is professional or not. For the time being, i'll apprecaite, if you could make some changes in attached db to get the converted value in report.

Thanks.

 

[Ahmed73]

I know you're new here but are you really asking me to work for free to give you a product that you will get paid for? What is wrong with that picture?

We are here to help you to solve your problems and give you advice not to do your bidding.

Hi Pat,
FYI, it is my project and I am user of this db. so nobody paid for it.
Well this forum is very useful and I appreciated you people to volunteer and help others. I hope once I got such knowledge I will also volunteer myself in this forum.
I would like to thanks @arnelgp who resolved my currency issue on report, I was stuck for many days.