gaps in data in report

[benwah]

Hi

I have been pondering this problem for a while and the resident access expert in the IT department has told me it isn’t possible. I have built this database and now want the user to be able to print a statistic report. Each record has a unique AutoNumber called ‘RefNum’. The problem stems from the fact that once a record is deleted the number wont be used again so you get gaps. Thus the report is printed (numbers are the RefNums):

ALL RECS | RECS OPENED | RECS CLOSED
1 1
2 2
3 3


6 6
7 7

9 8
10 10
11 11

13 13

So as you can see there are gaps all over the place. This will become a real prob once a sizable amont of records has been entered.

PS the 3 headings on the report are seperate subreports.

so i guess the qn is how can i list the RefNums without the gaps!?

Thanks in advance
wah

 

[benwah]

sorry my example report didnt display as i thought it would....

 

[Rich@ITTC]

Hi Benwah

I agree with your IT resident access expert. Autonumbers are there to automatically add one number to a sequence of already allocated numbers. Delete an earlier number and the gap is for ever.

However, autonumbers are intrinsically meaningless - they bear no relation to the data stored, they are just a guaranteed unique identity number, so printing out reports of these numbers is not usually something one wants to do. Surely the data/information linked to each key number is the critical stuff (some people hide their autonumbers from their users both in forms and reports - other display then as a quick field for basing a search on).

If you want a sequential number without any gaps then autonumbers are not for you (though the idea of changing all the ID numbers after deleting some records seems an idea likely to lead to problems).

Can't you learn to live with those gaps ... you may come to even like them!

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 07-25-2001).]

 

[benwah]

thanks for the depressing news Rich! I dont think i will be able to learne to love them. especially as the user can define a period the report it to be based upon, so down the track the report will end up being a couple of blank pages and then one page of data! If you get what i mean.. Back to the drawing board i guess...

Thanks for your help
wah

 

[Rich@ITTC]

Hi Wah

Aah ... now you didn't say about blank pages/blank spaces being a problem! Something can be done about this by setting the CanShrink property for the subreports (as a control/object within your main report) to CAnShrink=Yes. Similarly you can set the Detail/background of the main report to Yes for CanShrink.

This may well mean that you get round the blank pages painlessly!

HTH

Rich Gorvin

 

[Rich]

Why can't you base the report on data that actually exists, and if you require data to be kept for historical purposes then don't delete it, just archive it.

 

[benwah]

Hi Rich

No, the Can Shrink property thing didn’t work. I might change the RefNum from an AutoNumber to an integer and have a bit of code that makes sure whatever is entered hasn’t been used before.... Theoretically no records should be deleted as they would not become redundant (it is a Disabled Parking Bay Application dB). Only reason I can see is if someone enters the same application more than once into the system....

Maybe if I use the AutoNumber to locate the last record and then add 1 to the new integer control 'RefNum'... Anyway at least i have something to keep me going for a bit!

Thanks for all your help Rich

wah

 

[benwah]

if u r interested, in the queries i converted the autonumber to a text field:

Exp1: Str$(refnum)

and that did the trick! YAY! now i have to find myself something to do tomorrow!