Updating a table from a query with an expression

[pnabby]

I know that it is bad form to put calculations in a table, however I have a good reason. A long time ago I create a "Restaurant Sales Transactions" table so that I could put each unique ticket number and the total sale in. All of my reports are generated from this table the way it is.

Now I am trying to create new tables where the servers can enter the food items from a form and have it do a quantity total (i.e., for example 2 bowls of she crab soup is 2*4.95 for a quantity total of $9.90). Then I want to add the whole ticket up and add tax. Then I want this total to go into the restaurant sales transactions table along with the employee id.

Is this impossible? I know buying a POS system would be better, however we are a small restaurant and my cheap owner will not pay for a POS system.

Therefore, I am trying to work off the database I have already built.

Any suggestions?

Thanks,
Julie

 

[BNPMike]

You seem to be saying you are changing the design of your system but want to keep and old table for reporting purposes.

Firstly, for everyone else - I'm sure you'be already noticed this, this shows why you should never write programs against tables; always use queries. This allows you to make changes to your system without unnecessary re-programming.

Fortunately Access does not differentiate between a table and a query. You can therefore just write a query from your new tables that sums sales by order in the same format as now, delete the existing table, and use your new query as the source for all your reports - giving it the same name as the old table.

An alternative way of approaching this is to delete and re-create the old table from you new data prior to each report run. Many MIS systems use this kind of snapshot.

 

[pnabby]

I did not write any programs per say against the Restaurant Transactions Table.

However, my reports already have the math with the sums by server and I have other Tipout reports that get this data as well.

I built another table, called PLU table, that has all of the PLU #'s with prices and descriptions.

I am trying to create an update query into another table that does quantities, i.e., there could be 6 items on one ticket, however they are not always distinct. For example I could have 2 crab cakes, 2 pecan encrusted groupers, and 2 flounders on one ticket. I need a quantity total for each item. Then I need a ticket total with tax.

I cannot do an update query, since it says the expression I defined Price: [Qty] *[Price] is not updateable.

Any suggestions?

 

[BNPMike]

You need to update an underlying field in a base table. You can't update an expression as SQL doesn't know which bit of the expression to update - if you say Qty*Price is 25, SQL can't work out whether that is Qty=2 and Price=12.5 or Qty is 5 and Price=5. You can certainly update eg PLUExtended to([Qty]*[Price]).