Store number or records from a paramter query 1

[kphu]

Greetings,

I have a query that is dependent upon a date range that the user specify. I would like to store the total number of records that the query has compiled into a table.

I am fimilar with using VBA in excel but never done anything in access.

If anyone can provide any suggestions or ideas I would much appreciate.

Thanks in advance.

ken

 

[smurf01]

Ken,
Do you mean that when the user runs the query and let's say it returns 25 records, you then want the 25 records to be saved to a different table.

If this is so there is no need for VBA why not use an append query ??

Regards

Paul

 

[kphu]

Hi Paul,

That's a great sugestion however I've never used the append query. I can check the help section in access for it and will post a reply if I have any problems.

Thanks!

Ken

 

[smurf01]

Ken,
Create your normal query, add the fields you require and also your "date parameter".

Then on your menu bar you should have a control button for a dropdown list of queries, it will probably be 2 buttons to the left of where you would select a table for your query. Select "append Query" from there and it will then ask you which table you wish to append the data to.

I usually create a table before I start with the correct fields already in place, then it is just a matter of running the query

Regards

Paul

 

[kphu]

Hi Paul,

Sorry, I read your reply incorrectly.

I want the value of the total number of records to be stored into a table.

Lets say the query pulls 5 records. I would like that number (5) be stored into a particular field of a table.

Sorry for the confusion.

ken

 

[smurf01]

Ken,
what fields do you have in the table ?

Regards

Paul

 

[kphu]

Pulling information from table called TewksProcess.

Fields in Tewksprocess table.
Record
CompletionDate
HNumber

Query Named NumberofRecords
CompletionDate (Criteria = Between [Type the beginning date:] And [Type the ending date:]
HNumber



Thanks,

Ken

 

[smurf01]

Ken,
What type of info is HNumber ??

Regards

Paul

 

[kphu]

HNumber is text

 

[smurf01]

Ken,
Do you have some sample data I can see? Is it that you want to know how many Hnumbers there are on any given day ?

Regards

Paul

 

[kphu]

Here's the info in the TewksProcess Table.

Record HNumber CompletionDate
1 T09090 1/21/2003
2 T03890 2/28/2003
3 T98487 3/15/2003
4 T53647 3/28/2003
5 T48928 4/15/2003

I created a query called NumberofRecords.

Which gives me all the records based on a criteria of date ranges for completion date.

So say i run the NumberofRecords query.

And I put in a date range of 1/1/2003 thru 3/30/2003.

The query will give me a result of records 4 based on the data above.

What I need to do is to have something that will count the number of records that the query have produced and store it in a table. So in this example we got 4 records. I need the number 4 be stored in a particular table/field.

Basically this number will be used in another formula to give me the result I need and unfortunately there is no other way to do the calculation.

Thanks!

Ken

 

[smurf01]

Ken Thats fine, so basically you do not need to know the HNumbers all you are bothered about is how many there are in a given period.If this is correct let me try something and I will mail it to you. do you have an e-mail address

Also : This other table you are talking about, what is it called ?, what are the fields ?

Regards

Paul

 

[kphu]

Yes that is correct.

The other table is called TewksCost

Fields

Year 3/31 3/31Records 6/30 6/30Records 9/31 9/31Records 12/30 12/30Records

Basically what will happen is that when you run NumberofRecords Query you will always put in a quarterly date range i.e. 1/1/2003 thru 3/31/2003, 4/1/2003 thru 6/30/2003 etc

After it pulls the number of records I would like the value to be stored in the field that is according to the ending date parameter value.

So if we run the query and put in the date range 1/1/2003 thru 3/31/2003

I would like the value to be stored in the TewksCost table in the field 3/31Record because the date ending range is 3/31.

Note that the TewksCost table primary key is the year field.

Let me know if this is clear enough. I appreciate your help.

Ken

 

[kphu]

my email address is kennethp@webmpt.com

Thanks!

 

[smurf01]

Ken, So how many rows will there be in the TewksCost table ??

Regards

Paul

 

[kphu]

As of right now there is only 1 row or record with the year value being 2003.

Each record or row would reflect the year (this is the primary field).

ken

 

[smurf01]

Ken,
Are you still around, is this e-mail address your works or home ?

Regards

Paul