Use a query in an append or update query

[Rachael]

I have a query based on 4 different tables that gathers bits from each then performs some calculations (I inserted the totals line and have summed one of the fields)

What I want to do and don't seem to be able to is use this query to append the summed column and a few others into a new table.

Is this possible and if so, how?

Thank you for all replies

Rachael

 

[Mile-O]

I think, although you haven'y given much details, that you are looking for a Make Table query.

 

[Jack Cowley]

Hello Rachael!

Generally speaking you do not save calculations but create them, as you have done, as you need them. Data changes and saving totals and other calculations based on todays data may prove to be wrong at a later date. If your current query gives you the answer(s) you need then continue to use it when you need an answer....

I hope this message finds you well....

Jack

 

[Rachael]

Thanks Mile-o-Phile for your answer, I will investigate using make-tables.

Hi Jack, how are you? I'm well, nice to hear from you.

Basically what Jack is saying about data changing and calculations changing is the reason why I'm trying to do what I'm doing. The scenario is a chemical spray diary. Each chemical when purchased has its cost entered into a register. When a spray is applied to the vineyard the amount used is multiplied by the cost of the chemical so that at the end of the year a total cost for chemicals can be calculated. Now, the dilemma is, that chemicals change price, in the one year and from year to year, so my thinking is to have an after update procedure in the spray diary when chemicals are entered that grabs the current cost and puts all the required inro in a new table. therefore as the chem price changes this particular price won't change. I can then perform the end of year tally up of costs on this new table.
I was thinking an append query would do the job, or perhaps an update query to add the cost info directly into the chemical records table.

Clear as mud, I tend to ramble! Hope this makes sense.

Kind regards,

Rachael

 

[Jack Cowley]

Rachael -

We have to keep those vineyards healthy and vibrant so here is my suggestion. You have a table for chemicals with their names, cost and other pertinent data. This table is a 'lookup table'. You then have a table for work done to the vineyards and you have a field for chemical name, cost and quantity used. As you add chemicals you open the work done table and using a combo box you select the chemical and the price from the lookup table. You enter the quantity used.

That is all you need to do. Now your work done table has all the chemicals you used, what you paid for it and how much you used. Now you can create a query and get all the data you need. The next time you buy chemicals you enter the data into your lookup table so every time you use a chemical in the vineyards the price you enter will be the the latest price paid for that chemical and changing the price will not affect the outcome of your calculations for the chemicals purchased and used during the year.

I hope that I have made sense as wine production is very important to me, as you well know! If I have thoroughly confused me just let me know and I will do my best to try and explain myself.

My best to you and your family,

Jack

 

[Rachael]

Thanks Jack,

You make perfect sense, why didn't I think of this , I do the exact same thing with the standard rate of chemical, the brain just tries to complicate things for you sometimes, quite often there is an easy solution.....Do you think the growers in the US would need a simple affordable record keeping program for their vineyards, perhaps I should advertise over there.

Thanks once again Jack, hope your well, we are all fighting fit!

Rachael

 

[Jack Cowley]

Rachael -

You are very welcome and I understand the 'forest for the trees' syndrome better than most....

I am not sure what US wineries use or would use in the way of a record keeping system. We do, as you well know, have lots of small wineries (vineyards) and then there are the rest of them. With your contacts here you might be able to get a list of at least the smaller wineries here in California and then contact them directly by mail to see if they would be interested in your fine product. I am sure that there must be winery organizations here that you could contact that might be able to supply you with a list as well. Being on the consumption end of the winery business that is about all that I can think to tell you. Email your friends at the winery (vineyard) here and see what they have to say. If you need someone to go on tasting expeditions be sure and let me know...

I am glad that you and the family are 'fighting fit' and I hope you follow up with the local wineries (vineyards) as one never knows... It could make you rich and even more famous....

Jack

 

[Rachael]

Thanks Jack,

I just thought I'd post exactly how I solved the problem as wasn't quite straight forward.

I used a spare field I had in the spray diary and in the after update event of the chemical I put
[Spare]=DSum ("[Cost/Unit]","ChemCostQuery","[ChemID]=[form]![ChemID]")

so the Dsum collected the info from a query I created to gather the latest chem costing stuff (excludes empty chems and some other things)

Was I happy when this worked, DSum is my new fav toy.

Rachael

 

[Jack Cowley]

You are welcome Rachael!

If your solution works for you and it does not hinder your production of grapes then I am all for it!

I am having a California Shiraz this evening and it just does not compare with Rosemount or other Australian Shiraz's. I am a confirmed Australian Shiraz and Sauvignon Blanc wine drinker...

Stay well and my best to you and your family.

Jack