Need quick help...store results of query in a table field

[MarcieFess]

Hi. I'm a basic Access user, but a company contacted the temp agency I work for and asked for someone who can create a relatively simple Access application. I have the tables all created and linked and a lot of information in them, but I have one field that has to be populated by the results of a query.

Here's the query:
QuantityOnHand: (([P].[Package]*[P].[Size]*[SP].[Cases])/[P].[ConversionRate])

It runs perfectly.

However, now I want the the results of the query to be stored in the SP.QOH field. How do I do that? Everything I've tried (and I'm a basic beginner at Access queries) returns a syntax error.

This client's first mistake was to call an office resource agency to fill an IT position...but I'm here now so I need to finish it.

Once I get this query running, all I have left to do is to finish formatting the various reports and the job is done. They want a "proof of concept" by noon tomorrow.

 

[pr2-eugin]

You should not store calculated fields inside tables.. Check out this link for information..

 

[MarcieFess]

Maybe I should explain. This is a database of hazardous products for sale at a retail store. We have a contract with a major company to provide the required reports, individualized for each store.

I must run three reports using this information.

Report 1: Must return QOH for each individual product, sorted by which aisle in the store the particular product is located on. No totals required. Each aisle on a separate report.

Report 2: Must return QOH for each individual product, sorted by Hazard Classification...detailed report indicating QOH, all products within a particular Hazard Class must be totaled.

Report 3: Using the Hazard Class total from Report 2, must generate a report indicating whether permits are or are not required for each hazard class, comparing the total for that hazard class to the parameters of the permits and checking the correct check box based on a yes or no answer to that question.

QOH is required for 2 of the 3 reports, and then the total QOH is required for 2 of the reports, too.

Appreciate your help!

Marcie Fessler

 

[pr2-eugin]

You can still use the result of one Query on other queries.. There is no need for it to go on tp the table...

 

[MarcieFess]

OK one more question...I deleted the QOH field from my StoreProducts table. I also deleted that field from my report, and then created another QOH field in the report...but when I do a Print Preview on the report, it's asking me to enter a parameter value for Store Products.QOH. Evidently I failed to remove something from my report. How do I get the QOH from the query into my report?

 

[pr2-eugin]

Go into SQL view of the Query and copy it into word use the Find and Replace functionality and search for it and safely remove it from the SQL statement, and paste it back into the SQL View of the Query... It should probably be in the Group By clause or Sort by clause of the Query.. Save it and run again it should do the trick..

 

[MarcieFess]

SELECT SP.[Store Product Key], (([P].[Package]*[P].[Size]*[SP].[Cases])/[P].[ConversionRate]) AS QOH
FROM Product AS P INNER JOIN [Store Products] AS SP ON P.UPC = SP.UPC;


That's my entire query. When I run it, I get a table. That's fine.

It's the report that's asking for the parameter value.

 

[pr2-eugin]

Go into the Report Design view and check from the list of controls if somthing has its data source set to that field.. If so delete it.. or change it to the new field name..