UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Do I Create A Comparison Report?, Access 2016    
 
   
lcrews
post Dec 7 2017, 02:48 PM
Post#1



Posts: 42
Joined: 22-August 17
From: Los Angeles


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.

Attached File(s)
Attached File  Screenshot.png ( 45.5K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Dec 7 2017, 02:49 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi,

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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lcrews
post Dec 7 2017, 02:54 PM
Post#3



Posts: 42
Joined: 22-August 17
From: Los Angeles


Thanks DBGuy,

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


This post has been edited by lcrews: Dec 7 2017, 03:10 PM
Go to the top of the page
 
theDBguy
post Dec 7 2017, 02:57 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi,

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

Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lcrews
post Dec 7 2017, 03:11 PM
Post#5



Posts: 42
Joined: 22-August 17
From: Los Angeles


-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
Go to the top of the page
 
theDBguy
post Dec 7 2017, 03:17 PM
Post#6


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lcrews
post Dec 7 2017, 03:23 PM
Post#7



Posts: 42
Joined: 22-August 17
From: Los Angeles


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?
Go to the top of the page
 
theDBguy
post Dec 7 2017, 03:34 PM
Post#8


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lcrews
post Dec 7 2017, 03:52 PM
Post#9



Posts: 42
Joined: 22-August 17
From: Los Angeles


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
This post has been edited by lcrews: Dec 7 2017, 03:56 PM
Attached File(s)
Attached File  Testing_Area.zip ( 669.17K )Number of downloads: 1
 
Go to the top of the page
 
theDBguy
post Dec 7 2017, 03:57 PM
Post#10


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lcrews
post Dec 7 2017, 04:03 PM
Post#11



Posts: 42
Joined: 22-August 17
From: Los Angeles


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 HERE I just added [forms]![frm_WhereUsed]![Text38] to the parameters and YAY!
This post has been edited by lcrews: Dec 7 2017, 04:12 PM
Attached File(s)
Attached File  Testing_Area.zip ( 673.07K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Dec 7 2017, 04:35 PM
Post#12


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi,

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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Dec 7 2017, 04:42 PM
Post#13


UtterAccess VIP
Posts: 8,668
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 09:26 PM