Autonumber Problem

[Amir88]

Hello everyone!

I have a really urgent problem. I have 3 tables

_______________________________________________________
Customer
--------
CustomerID
Name
Phone
..
Password
_______________________________________________________
Employee
--------
EmployeeID
FirstName
LastName
..
Password
_______________________________________________________
Vendor
--------
VendorID
Name
Phone
..
Password
_______________________________________________________

As you can see there are many columns but what all of these tables have in common is the IDs (Primary Keys) and Passwords.

I need to make a sign-in page for my project where each of the customers, employees, or vendors can use to access their accounts. I was planning on using the ID's as their usernames and the Password field as their Password.

Although using MS-Access autonumber I can get unique keys for all my tables within themseleves. But what can I do if a CustomerID = VendorID or a VendorID = EmployeeID... ?

Basically My question is : How can I assign autonumber values to make sure they are the continuence of other tables values. In PostgreSQL or MySQL I would use the SEQUENCE object. What can I do here?

 

[MichaelRed]

You cannot do this in the "native" implementation. It also my not be the better approach, as UserNames are not generally assigned, as this decreases the security a bit.

If you MUST do so, you need to generate the value seperatly a somewhat more complex soloution than you require is seen if fag700-184, If you are able to simplify it or are willing to include the additional characters, it could be useful.





MichaelRed

 

[JeremyNYC]

Why are these all stored in separate tables. If you have control over the data structure, I would put all of these into one table. If there are a huge number of fields that only get used for one type of person and not the others, youcould break that out into a separate table, but nowadays there's very little need for this, unless there are huge number of fields, records, and operations performed on them.

If you put them all in one table (with a field to indicate how to classify a person) then you will get unique ID fields values.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[cmmrfrds]

You could seed each identity field (i.e. primary key) with a different starting value so that the numbers don't fall in the same range.

 

[petegoodman]

Instead of that, why don't you try adding a listbox onto the user-login screen, where the user selects if they are a customer, an employee or a vendor.

It may not be the right idea for you, but it is what I would try.

 

[Amir88]

Thanks everyone for all your help

I tried Jeremy Wallace's way and it worked. All I had to do was to put them in the same table and restructure the rest of my database's tables and views to keep the rest of the functions working.

Thanks again!

 

[JeremyNYC]

Amir,

I'm glad that post was helpful to you. If you're just getting into this whole database thing, you might want to check out "The Fundamentals of Relational Database Design", an article by Paul Litwin that I have posted (with his permission) on my old website at

http://www.abcdataworks.com,

in the Developers' section.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York