Counting code

[slames]

Hi, I hope someone here will be able to help as I'ma bit stumped on this.
I'm working on Access 2000
I am dealing with a stock count.
With the use of a query I generate a list of people and series of books they've ordered. E.g:

Cust1 Title 1 from issue 1 to 6 copies 1
Cust2 Title 1 from issue 2 to 8 copies 2
Cust3 Title 1 from issue 8 to 10 copies 1

The titles are not mixed - but if I can figure out how to do it will be in the future. But the issues can vary completely as can the number of copies.

I need to determine the number of copies of each issue in the query , i.e. in the above example:
1 copy of issue 1
3 copies of issue 2
3 copies of issue 3

etc.
Once I have these totals I need to insert them into a table.

Can anyone shed any light on how I can obtain this count, I'm completely blank on this one.

Thanks in advance

Steph

 

[vbajock]

Look at the Dcount() function in the help file. You can pass it a where clause to qualify your records.

 

[slames]

Thanks for the hint i the right direction, should hopefully beable to work it out from here...

 

[MichaelRed]

Set up the recordset as:

Cust[tab]Title[tab]Issue[tab]NumCopies

Then some simple group / aggregate queries will easily return the number of copies of each issue.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[slames]

Hello again, I've been playing with this today, I've still not solved it, MichaelRed would you be able to explain your suggestion in a bit more detail please. I know I need to obtain the record set and loop though it (I think) but my minds blank as to what else. Sorry - I'm a bit of a beginner in this area. My problem is determining the issues when all I have is the first and last issue in the series that they want and I need to count every issue of the series not just the beginning and end issues. Any help greatfully appreciated.

Thanks

 

[MichaelRed]

I'm afraid I'm at a bit of a loss to know how to help here. Your question suggests that you do not have a background in data base functionality, so I checked on some of the threads you have started and many of these also suggest some lack of (perhaps formal) training - yet they often refer to some 'credible' issues and post generally normal SQL queries, asking for what seems like 'trivial' adjustments.

Assuming the above is at least in line with relaity, the conclusion I draw is that you have been thrust into a position to require working somewhat above the level of your knowlege / experience. In which case, you have my sympathy, but not much real help.

To go into the detail which, in my opinion, would be necessary requires more time/effort than I have available presently. Given the suggested recordset structure and types of queries, it would seem to be a reasonable expectation that one who is knowledgeable re standard SQL syntax to write the queries w/o much effort. Further, MS Access has a quite reasonable 'query builder' to assist in the process and the ubiquitous {F1} (a.k.a. Help) aalong with third party test on MS Access and/or SQL in general should be suffucuent to overcome even a novice's hesitation in developing the soloution.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cgarts]

Dear Steph,

Maybe I'm reading your post incorrectly, but it sounds on a quick
reading that you are asking for help in commiting a "database sin".

Excerpting from your initial request for help...

"With the use of a query I generate a list of people and
series of books they've ordered.....
..Once I have these totals I need to insert them into a table."

It sounds like you already have the basic information in your database,
and that you are accessing it with a query, and then wish to create
essentially duplicate information elsewhere in another table.

One principle I try to go by is to have only one location for storing
any piece of information.

Because - if that information changes, I have only one place to go to
fix things.

Because - it's more economica, efficient, elegant, repairable,
updateable...and so on.

I suspect you may wish to revisit and rethink your initial plan.

Charlie