Cocktail Recipes

[SteveF]

I don't know whether this is a query or table question, so am trying it here first.

I have a stock movement/consumption database and want to add cocktail recipes to it and am struggling.

I have attached a JPEG of the product table which is where everything lives. What I want to be able to do is build recipes using the existing products as ingredients and then feed the total back into the product table in the 'cocktail sales' column.

What I should be able to do is create a recipe using a form and enter up an amount of each product used in that recipe. The trouble is I can't get the consumption to relate to the product, I think I am making fairly basic errors.

I don't know what the basic structure should be, any help would be appreciated and could well save what is left of my hair

I'm using Access 2007

 

[David Eagar]

Something along these lines I think

 

[SteveF]

Very much along those lines I think; thanks. I'm trying to extend it further than that though by feeding the consumption back into the product table.

 

[David Eagar]

OK, have added a Stock table & added to the Cocktails table - you would then need to build a query to calculate the amount of Drinks sold and reduce the amount of stock by that amount

 

[SteveF]

This is brilliant, really gives me something to work with. I can't tell you how many blind alleys I have headed up trying to get this working

 

[David Eagar]

Well, if you're going to resort to flattery, I'll take my business elsewhere !!
Good luck

 

[SteveF]

It was maintaining the integrity between the product and quantity that I couldn't get. I think all I need to do now is extend your logic and calculate total consumption per product, once I have that it should be easy enough to add the consumption back into my own product table.

I'm getting there, I think...

 

[vbaInet]

Steve: I think I know where I will be getting my drinks from at such good rates

 

[SteveF]

he is working in Aussie dollars though, by the time we convert to sterling and add our galactic new VAT rate...

 

[vbaInet]

I thought pack size was litres :O

Yeah, I know. A rather steep rise. We'll survive.

Anyway, I will leave you to carry on with the work at hand

 

[SteveF]

Ok, I'm getting there but am not quite...

I now have a query working out consumption with related product id numbers so that's a huge step forward. What I need now is to consolidate all the consumption of each product.

How would I go about adding up all the consumption of each instance of, for instance, product no 107 which appears in 3 x recipes and therefore has 3 x consumption amounts?

Much head scratching occurring over here today, many thanks for help.

 

[David Eagar]

Added some new queries to a calculate usage and update stock - essentially it is a 'Group by Sum' and then Update query to adjust stock

 

[SteveF]

Eureka moment, we have consumption

 

[David Eagar]

Eureka moment, we have consumption

Then take 2 asparins and call me in the morning....or better still, given the nature of the project, a few stiff drinks

 

[SteveF]

There have been stiff drinks involved for a little while already; project slowing down considerably...

 

[vbaInet]

There have been stiff drinks involved for a little while already; project slowing down considerably...

Go easy on the cocktails

When I first saw the title of your thread, "Cocktail Recipes", I thought you were offering some tips on recipes for cocktails. lol

 

[SteveF]

I'm very very close with this now, but I can't get the update query to work. I'm aware that storing calculated values is not usually the done thing but i'm using that value in another calculation so I really do need to calculate it and then store it.

Any help with updating a table with the result of a query?

 

[David Eagar]

You have caught me at a very bad time (see Watercooler/The Ashes and you will understand) also, in NOT keeping with the theme, I have had too much Aus premium wine -not cocktails....

At the back of my mind, update queries don't work when Group By are involved - Append do (ask Bill Gates why this is so)

Create a temp table - Append new values and then Upadte from temp table

Any other members following this post, correct me if I am wrong

 

[SteveF]

I was avoiding the mention of cricket

 

[vbaInet]

You have caught me at a very bad time (see Watercooler/The Ashes and you will understand) also, in NOT keeping with the theme, I have had too much Aus premium wine -not cocktails...

Premium!! The good stuff mmm...

At the back of my mind, update queries don't work when Group By are involved - Append do (ask Bill Gates why this is so)

Any other members following this post, correct me if I am wrong

Yes, even the Totals option isn't available. I also think you can't have a subset of records to update from in an Update statement in Access.

I was avoiding the mention of cricket

Don't worry, you can talk about cricket... I'm not watching it