Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ How Do I Create A Comparison Report?

Posted by: lcrews Dec 7 2017, 02:48 PM

I have one table that has recipes and another table that shows the percentage of each ingredient in the recipe. I'd like to create a form that has let's say 10 boxes where I could type recipe1, recipe2, recipe3, etc.. Once this form has selections made, I would like to output a report that shows a comparison of [Raw material] and [Percent] like this picture. Now not every recipe is the same (hence the blank spaces next to some ingredients) so the raw materials should all be listed in one column while the percentages should show separately. I'm not sure how to do this, but I really feel like this is something Access can do. The reason I'm trying to do this is that there may be cost-saving options to explore. For example, if 3 recipes are being compared, I can see if 1 recipe is using a more costly ingredient than the other 2 and how much is being used.


 

Posted by: theDBguy Dec 7 2017, 02:49 PM

Hi,

At first glance, it looks like you can use a Crosstab query to produce something like this.

Posted by: lcrews Dec 7 2017, 02:54 PM

Thanks DBGuy,

I'm trying this now. I'll let you know if I can't set it up right.


Posted by: theDBguy Dec 7 2017, 02:57 PM

Hi,

You're correct. It might need a little bit of setting up prior to applying a Crosstab query.

Good luck!

Posted by: lcrews Dec 7 2017, 03:11 PM

-Update-
It works! But I can't filter the field [item] with "Forms![Form1]![Recipe1]". It gives me error: "The MS Access database engine does not recognize ... as a valid field name or expression." Any thoughts?

-Landon

Posted by: theDBguy Dec 7 2017, 03:17 PM

Hi Landon,

Try going to the Design View of the Crosstab query and add a literal criteria just to see if you can filter the result. If you can, then follow the syntax to see if you can get it to work.

If you need further help, please post the SQL statement here, so we can check it.

Posted by: lcrews Dec 7 2017, 03:23 PM

CODE
TRANSFORM Sum(qry_Percentages.[Percent]) AS SumOfPercent
SELECT qry_Percentages.[Raw Material]
FROM qry_Percentages
WHERE (((qry_Percentages.[Item])=[Forms]![frm_WhereUsed]![Text38]))
GROUP BY qry_Percentages.[Raw Material]
PIVOT qry_Percentages.[Item];


I was able to view the results by typing directly, "A452AK" (a recipe). So the crosstab works for sure. It only gives me the error when I add "Forms!..." When I change it to a select query, it works. So it has something to do with "forms!..." right?

Posted by: theDBguy Dec 7 2017, 03:34 PM

Sounds about right. Sorry I can't test it for you.

Posted by: lcrews Dec 7 2017, 03:52 PM

It's not letting me update my post. Here is a stripped copy of my database. Using query, "Test" will show that there is a problem with the criteria, but if you exchange the criteria for "Test", (the recipe on tbl_Formulas) it works correctly.

DBGuy,
Thanks for the help, if you're able to look at this database I'd be grateful, but I understand if you have things you've gotta do.

Landon

 Testing_Area.zip ( 669.17K ): 1
 

Posted by: theDBguy Dec 7 2017, 03:57 PM

Hi Landon,

Thanks for posting a sample db. However, when I tried to open the query, I got a "cannot find table 'tbl_BulkItems'" error.

The file only has tbl_Formulas, but no tbl_BulkItems.

Posted by: lcrews Dec 7 2017, 04:03 PM

I was hoping to fix that before you downloaded it. I added it (That's why my reply has an edit now). Thanks for your patience. My database is rather large and I cut a little too much out. This one has what you need.

-UPDATE-
I found the solution to my problem https://access-programmers.co.UK/forums/showthread.php?t=77772 I just added [forms]![frm_WhereUsed]![Text38] to the parameters and YAY!

 Testing_Area.zip ( 673.07K ): 0
 

Posted by: theDBguy Dec 7 2017, 04:35 PM

Hi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Posted by: projecttoday Dec 7 2017, 04:42 PM

Hello, Landon. You can also export your data to Excel and create a pivot table.