Selecting specified number of records

[trebor3900]

I have a database that allows the user to order clothing items.
Each size of each item has a different stock number.

I have [StockNo] - [StaffNo] - [Qty] i need to generate [OrderNo].

The problem i have is that [OrderNo] is incremented after every five five items (ie[StockNo]).
This gives me my first problem of selecting just the first five records of a table in order to allocate an order number.

The second problem i have is that in order to allocate an order number, i have to summarise the [Qty] by [StockNo].
This gives me the opportunity to select the order number but i lose the individual [StaffNo] information.
This information, together with the [OrderNo] is needed for a master order table.

I have created a crosstab query which summarised the [StockNo]but how do i retrieve the [StaffNo] information from the column headings.

I am new to Access and would be grateful for any help or comments as to whether i am going about solving this problem in the best way.

Thanks

 

[The_Doc_Man]

At first blush, it sounds to me like your table is not properly normalized and your design to limit orders to 5 items (before you change order numbers) is also starting to chafe a little bit.

I think the best thing you could do for yourself is to Google-Search the topic "Database Normalization" - which return probably a gazillion hits. So what you do is to narrow that down to only look at reputable sites, usually from the .EDU domain. Preview a couple of write-ups, download and print the ones you like, and study them.

I'll bet you will find some normalization problems in your DB. When you resolve these problems, you will find it FAR easier to handle the problem you described to us.