Number Sequence (1 Viewer)

[Jmclel]

Hi Folks

I am in the process of starting to make a small database to record orders for door access cards. easy so far, however I need to record the number required as well as the number range start and end. i.e. 200 cards starting 1 - 200. Next batch would be 201 - + Quantity. I have tried messing with a query but don't really know where to start. I want it to auto add the number from the previous issued cards to give me the number range. I have done this in Excel but in Access seem to have got a bit lost. Any help would be much appreciated.

 

[gemma-the-husky]

the difference in access compared with excel is that you don't look at the previous row (as there isn't one - genuinely - the point of a database is that the order of data is immaterial). Instead you find the biggest one in all the rows. it actually works better for managing data. If you reordered the rows in your spreadsheet, a formula that looked at the previous row would no longer work. In access, the row order is immaterial. This is no doubt why you got a bit lost, and is actually an important concept. Databases just cannot refer to other rows. You deal with things by examining the entirety of a collection of rows, or importantly a sub-selection of those rows.

you can deal with individual rows by processing them with vba code, but often that isn't necessary.


so the last number used is given by this. dmax finds the largest value in a column in a given table or query (subset/domain)

dmax("cardnumber","cardtable")

so the next number will be

nextnumber = nz(dmax("cardnumber","cardtable"),0)+1
(nz is a special function to deal with a blank result)



what are you doing exactly with the card numbers? do you need to store them individually, or are you more subtly managing the card groups as ranges. It's just that I posted a demo for someone previously about managing ranges.

 

[Jmclel]

Thanks for the quick reply,

I kind of realized that I could not reference the previous row as in a spreadsheet this is as you say where I am getting stuck Lol.

As to the recording of cards I only need to record or "calculate" the number range and not the individual card numbers. i.e the next start number and the end number.

Your answer has pointed me to go and investigate a bit more as I was thinking along the lines of a sub query.

Cheers

 

[Jmclel]

Thanks for the pointer, do you happen to remember the demo for the range problem so I may have a look, I tried searching but to no avail.

 

[Jmclel]

Found A Solution using Dmax, went along the lines of: -

Me.Card_Start = Nz(DMax("Card_End", "Card_List", "Facility_Id = Combo17.value"), 0) + 1

Thanks