View a criteria in Append query

[L'apprentis]

Hi, In the example attached below how can I see the "cut off date" that I am prompt to enter in the fourth field of query1-a (I would like to see it for each one of the record that are displayed when the query is run and then append it to a table)
Cheers,

 

[ScottGem]

You have several design issues here. First, name and date are reserved words in Access and should not be used as an object name. In query q1-b you are appending data to a field named name and your Transaction table has a field called Date. Second, Stock on hand should not be stored. It is a calculation based on your transactions. Storing it denormalizes your database and affect data integrity.

Finally, this is another reason I don't like parameter queries. Instead use a form to supply the date criteria. Create an Unbound form with an unbound control. Then in the query set the criteria to:

<Forms!formname!controlname

You can then use that same reference in a column in a query or to populate a control on a report to display the cut off date. Again, this is not a value you want to store anywhere.

 

[L'apprentis]

Hi ScottGem,
Thanks for your reply,
I did change the field 'name' and 'date' as you advised. For the method I am using for stock on hand i am not entirely sure as for the above example I have been following advice from an other experienced user:


Quoted:
Query #1 - Compute stock on hand as of your cutoff date. Create a single stock-on-hand transaction with the cutoff date. Put it in a temporary table until you need it. This will be a two-layer query. I.e. a query of a query.

Query #1A - a summation query grouped by item number for all transactions earlier than the archiving date so you can get the "inventory of X at archiving date"

Query #1B - an append query that draws from the summation query and feeds it to the temp table. The stock-on-hand record will have the archiving date

Query #2 - Append all active transactions with date less or equal to the selected cutoff date. Assuming 1A and 1B worked, this does NOT need grouping or aggregated functions.

Query #3 - Delete all active transactions, same criteria. Again, only needs the date criteria.

Query #4 - Append the Stock-on-hand transactions you kept earlier from query #1. Needs no criteria, they were all applied earlier.

Query #5. Empty out the temp table that held stock-on-hand transactions. Needs no criteria.


I don't want other users to design my dB for me but follow their advice as I am still level beginner and would like to understand what i am doing right or wrong which, now makes me wonder what are the downfall of parameter queries?
Is the the Db still dernomalised if calculated values are stored only temporarly?

Thanks again,

 

[ScottGem]

I'm not sure I follow the purpose of those queries. Are you trying to get a beginning point from?

I wouldn't object to storing calcs temporarily for certain uses, if that was the only way. I would need to know the why to determine if its necessary.

As for parameter queries... They are just very inflexible. All you can do is display a text box with a prompt. You can't do any input validation, you can't use combo or lists, you can't do any formatting. You just have much greater flexibility using a form.

 

[Rich]

Passing the Parameters to a query via a form still makes it a parameter query

 

[ScottGem]

If you want to call it that, be my guest. In my view a parameter query is one where the parameter is asked for at the time the query is run NOT if its supplied prior to running the query. Since there needs to be some way of differentiating between parameters supplied at run time and criteria supplied by other means, I will continue to refer to parameters queries that way. If you have a better way of differentiating I'm open to suggestions. But I think you do the people we try to help a disservice but not differentiating.

 

[Rich]

But I do differentiate, what you describe as a parameter query where the parameter is asked for when the query opens is actually a Parameter Prompt.
By passing the parameters via a form it then simply becomes a Parameter query, that's how Access defines it by the way, not what I choose to call it

 

[L'apprentis]

Thanx,
The purpose of the queries would be archiving the inventory and keep only relevant data in the dB. The actual inventory amount becomes stock on hand transaction (All transaction prior cut off date) + sum of all subsequent transactions. Then archive tables can be backed up by exporting them.
I seemed to me that this is a good idea to keep the database clean from old data that become irrelevant after a while...
I am still not sure how to put the different queries all together (see: post 3 on this same thread).
How do i get to automaticly add a parameter value in a field for which the record depend on that parameter. I am not sure this is very clear...simplified example:
Imagine I have 10 bags of apples and each bag got a specific record (country, prize, No of apple...) I know how to get a list of the bags with less than 10 apples where <10 is entered for [no of apple] parameter prompt. How do I get to have the value 10 to be added in a new field for eah apple bag record?