Using a table without a primary key?

[dmckie250]

Hi Everyone,

I have been developing a excel workbook/access database system for the last several months and all of a sudden there's been a change of requirements.

One aspect of the system is the tracking of telecoms equipment that has been provided to employees. Now different employees may have different cell phone accounts over time, and each cell phone account may have a number of items of related equipment (replacement handsets/accessories)

The telecoms request table previously had the primary key Request # as an autonumber and Cell Phone # as a foreign key, and contained all the details of the requests (e.g. request type/equipment details). Cell Phone # is the primary key of the Cell Phone # table, which has Employee ID as a foreign key and contains the details of the cell phone account. Employee ID is the Primary Key of the Personnel Details table.

This was all working fine, until the powers that be have decided that they want the telecoms to be ordered earlier in the new employee recruitment process, so now I have to upload the details of the telecoms request without knowing the Cell Phone Number.

This would be fine except that i can't then use an update/append query to update the Cell phone request via the excel workbook as more details become available. One solution I was considering was using a Requisition approval number for the Request #, which would allow me to omit the Cell Phone number until later when it became available. However, as this requisition number is only for new orders, that would mean I have a blank for Request # for the rest of my requests....

This is quite a complicated problem, and the only way I can think of to get it working in all situations is without using any primary key at all.

Does anyone have any suggestions? I considered using the requisition number as the primary key and appending it into the table containing the Autonumber, but it contains letters and therefore causes a data type mismatch.

I'll post some screenshots of the relationships etc..

Cheers,

Duncan

 

[boblarson]

A BETTER way for this would have been something like this:


Requests Table
RequestID - Autonumber (PK)
RequestDate- Date/Time
...any other info directly related to the request but not the cell or employee

Employees table
EmployeeID - Autonumber (PK)
FName - Text
LName - Text
...etc.

Phones Table
PhoneID - Autonumber (PK)
PhoneType - Long Integer (FK)
PhoneSerialNo - Text
PhoneSIM
PhoneNumber - Text

PhonesEmployees Table
PhonesEmployeesID - Autonumber (PK)
PhoneID - Long Integer (FK)
EmployeeID - Long Integer (FK)
DateAssigned - Date/Time
RequestID - Long Integer (FK)

PhoneTypes Table
PhoneTypeID - Autonumber (PK)
PhoneTypeDesc - Text

 

[dmckie250]

Here is the SQL for the Update/Append Query I am using to add the details from a temporary table into the permanent table:

UPDATE tbl_TEMP_Telecoms_Requests LEFT JOIN tbl_Telecoms_Requests ON tbl_TEMP_Telecoms_Requests.[Request #] = tbl_Telecoms_Requests.[Request #] SET tbl_Telecoms_Requests.[Request #] = tbl_TEMP_Telecoms_Requests![Request #], tbl_Telecoms_Requests.[Cell Phone #] = tbl_TEMP_Telecoms_Requests![Cell Phone #], tbl_Telecoms_Requests.[Request Type] = tbl_TEMP_Telecoms_Requests![Request Type], tbl_Telecoms_Requests.[Request Received] = tbl_TEMP_Telecoms_Requests![Request Received], tbl_Telecoms_Requests.[Request Processed] = tbl_TEMP_Telecoms_Requests![Request Processed], tbl_Telecoms_Requests.[End User Notified of Approval] = tbl_TEMP_Telecoms_Requests![End User Notified of Approval], tbl_Telecoms_Requests.[Order Received] = tbl_TEMP_Telecoms_Requests![Order Received], tbl_Telecoms_Requests.[End User Received] = tbl_TEMP_Telecoms_Requests![End User Received], tbl_Telecoms_Requests.[Telecom Type] = tbl_TEMP_Telecoms_Requests![Telecom Type], tbl_Telecoms_Requests.[Telecom Brand] = tbl_TEMP_Telecoms_Requests![Telecom Brand], tbl_Telecoms_Requests.[Telecom Model] = tbl_TEMP_Telecoms_Requests![Telecom Model], tbl_Telecoms_Requests.[ICCID #] = tbl_TEMP_Telecoms_Requests![ICCID #], tbl_Telecoms_Requests.[IMEI #] = tbl_TEMP_Telecoms_Requests![IMEI #], tbl_Telecoms_Requests.[Blackberry PIN] = tbl_TEMP_Telecoms_Requests![Blackberry PIN], tbl_Telecoms_Requests.Accessories = tbl_TEMP_Telecoms_Requests!Accessories;

And the screenshots of the relationships should be attached

 

[dmckie250]

Thanks for the suggestion Bob, the only issue is that I am only going to be working here for another 5 months and once I leave this system has to be managed by my replacement.

Because of that fact, it has to be fairly simple and easy to maintain data in both the database and excel workbooks. While the addition of PhoneTypeIDs etc makes it a lot easier to manage the database, it makes it much more difficult to keep the workbook current. On top of that, I have bosses that are paranoid that I am developing a system that they will have no idea how to manage once i leave, so they are insisting everything is as flexible as possible and easy for someone else with little to no VBA/SQL knowledge to change in the future.

This table will also be used to track the ordering of accessories, which wont have any serial numbers etc....I guess you're right and I should break it down and organize it better though....

 

[boblarson]

Umm, you don't seem to understand (as probably they don't) that my design there is way more flexible than your current setup. It SEEMS to be more complex but in fact it allows you to add more items without making design changes to the database and by simply adding records. A properly normalized database will do that for you.

 

[dmckie250]

Bob,

While that set-up would work fine in standalone database system, the fact that I have to import half of data into the database from the excel workbook, then at a later date import the other half of the data without creating duplicate records is what makes this complicated.

based on the relationships between the data and the times it becomes availabile this is the table layout I think it will be best to use:

tbl_Telecoms_Accounts Telecoms Account ID Employee ID Cell Phone # Service Provider Billing Account Number Date Activated Date Deactivated
tbl_Telecoms_Equipment Telecom ID Telecoms Account ID Telecom Type Telecom Brand Telecom Model ICCID # IMEI # Blackberry PIN

tbl_Telecoms_Requests Telecoms Request ID Telecoms Account ID Request Type Request Received Request Processed End User Notified of Approval Order Received End User Received

The fields in red are those that won't be available until the second upload.

I think the best way to do this would be to upload the account details using an append query, then run an SQL to find the Telecoms Account ID for the data that had been appended into a new record, store this information in the excel workbook and then use this information to append the rest of the details into their respective tables.

For the second upload I could then use an update query to add the missing data in

There might be issues with this method if the handset was replaced, but this would be done via the database directly and not using the excel workbook. do you think that this would work?

 

[accessfleet]

I am a access2007 newbie about 6 month of concentrated study. Unless I have missed something, the study aids I have used recommend that key field be system generated. Even when their real work is done in the back ground.

This is a little different than my old dos based relational database experience. (Assign meaningful key fields) But it seems to work. Only a few difficulties have presented themselves so far. Things like, lookup fields showing alpha descriptions but referencing key numbers. In a couple of cases I had to figure out the number assignment for the drop down record and reference that number in the code to do the work. So far it really hasn't been a big problem.

Couldn't you just let an auto assigned key field work in the background so that it wouldn't really matter when you were able to supply data that was previously consider key data?