Get deleted primary keys

[Erico Vasconcelos]

Hi,

Is it possible to do the following:

1) I have a members table. It has member ID column as primary keys. It has
1000 members. Some members has been deleted, so our table has some slots in
the member ID sequence. I would like to create a report containing the
deleted members ID (the ones that do not exist anymore).

Had tried it using querys, macros and VBA, but dosen´t had success in any
way. Any help?

Best regards,
Erico

 

[Jeff Boyce]

Enrico

What is the data type of the MemberID field in the table?

If it is an Autonumber, you need to consider that an Access Autonumber is
really only intended to provide a unique row identifier, and really isn't
fit for human consumption.

By the way, why would you keep a database in which members were deleted?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

1) I have a members table. It has member ID column as primary keys. It has
1000 members. Some members has been deleted, so our table has some slots in
the member ID sequence. I would like to create a report containing the
deleted members ID (the ones that do not exist anymore).

FWIW I would never delete members. Instead I have an Inactive Yes/No
field or an Inactive status field.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John Spencer]

Easiest way if the Member Id is an integer number field.

Create a small table with one field
Table Name: Iotas
Field Name: Iota (Number field type Long Integer)

Add the numbers 0 to 9 to the table.

Make a query to get generate the numbers from 1 to 1000
-- Add iotas to the query three times
-- Create the following calculated field
1 + Iotas.Iota + 10*Iotas_1.Iota + 100*Iotas_2.Iota

Save that as q1To1000

Now use the umnatched query wizard to find all the number in the query
that are not in the members table.

If Member Id is not an integer number field, you will need to describe
it and describe how it is generated.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Erico Vasconcelos]

Hi Spencer,

Thank you very much, it does exactly what I intented to do. But the number
of members is continualy growing is there a way I limit the query q1to1000 to
the id of the last member?

For example:

Our query would create the numbers until 10000:
1 + Iotas.Iota + 10*Iotas_1.Iota + 100*Iotas_2.Iota + 1000*Iotas_3.Iota

But it show only until the number 3020 (for example), which would be the ID
of our last registered member..

Best regards,
Erico Vasconcelos

 

[John Spencer]

Sure, just limit the returned results to be less than the maximum in your
table. In SQL the query would look something like the following. You will
have to replace the made up field and table names with your field and
table/query names.

SELECT CalculateNumber
FROM q1To10000 LEFT JOIN [MEMBER]
ON q1To10000.CalculateNumber = [MEMBER].[MemberNumber]
WHERE [MEMBER].[MemberNumber] is Null
AND q1To10000.CalculateNumber < (Select Max([MemberNumber]) From [MEMBER])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Erico Vasconcelos]

Hi Spencer,

Thank you very much!! That´s exactly what I was looking for! Realy thank you
for your time and support!

Best regards,
Erico
(e-mail address removed)

Sure, just limit the returned results to be less than the maximum in your
table. In SQL the query would look something like the following. You will
have to replace the made up field and table names with your field and
table/query names.

SELECT CalculateNumber
FROM q1To10000 LEFT JOIN [MEMBER]
ON q1To10000.CalculateNumber = [MEMBER].[MemberNumber]
WHERE [MEMBER].[MemberNumber] is Null
AND q1To10000.CalculateNumber < (Select Max([MemberNumber]) From [MEMBER])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi Spencer,

Thank you very much, it does exactly what I intented to do. But the number
of members is continualy growing is there a way I limit the query q1to1000 to
the id of the last member?

For example:

Our query would create the numbers until 10000:
1 + Iotas.Iota + 10*Iotas_1.Iota + 100*Iotas_2.Iota + 1000*Iotas_3.Iota

But it show only until the number 3020 (for example), which would be the ID
of our last registered member..

Best regards,
Erico Vasconcelos