Need advice in splitting table

[Access_rookie]

I have a table that contains different kinds of biological samples, ie. biofluids, tissue blocks, tissue slides. Each sample has an assigned company ID which is basically a numeric increment sequentially. I'm thinking about splitting it up to 3 tables based on the sample type because of the need to add specific info regarding each type. For example, types of stainnings for the slides. I will have another table which contains patient info that will link to each of the 3 new tables. My question is how do I best keep track of the last company ID used when enterring new records for each table. Please advise.


Thank You.

[John_G]

Hi -

What I do in this situation, i.e. where one series of ID numbers is being used in several places (tables), is keep the current ID number in its own little, one-record table. Then, when I need another number in the sequence, I retrieve the current number from that table, increment it by 1, and then update the table with the new number, something like this:

NextNumber = dLookup("CurrentValue","NumberTable") + 1
currentdb.execute "Update NumberTable set CurrentValue = CurrentValue+1", dbfailonerror

Then use NextNumber as the CompanyID in whatever table you are adding data to.

That results in every CompanyID being unique, regardless of which table it is in.

HTH

John

[Access_rookie]

Hi John, Thank you for your feedbacks. However, I think your suggestion only works when a single record was updated at a time. What happen is I have several samples coming in per shipment, and the raw data is provided in Excel. I need to retrieve the latest CompanyID, assign the next one in increment to the new samples, then import them to Access. Is it possible to create a query to compare the CompanyID between the 3 tables and return the max value?

[John_G]

Hi -

My approach here would be to first import the Excel data into a temporary table in Access, then assign the unique numbers as I mentioned, then move the records to the permanent tables in Access. I think you would need a VBA procedure to do it.

One question: what do these company ID values you are generating relate to? It seems a bit strange to me that you would want each of them to be unique, if they are relating to a company.

John

[Access_rookie]

I inherited the table from the previous owner. The companyIDs have already been assigned. It's actually my company's sampleID vs. vendor's sampleID. I can see why, because we received samples from different vendors and they used different system to assign their sample IDs. Some vendors even have same sample ID for different kind of sample types, ie. slides, blocks and biofluids sometimes have same sample ID because they are from the same donor. Also, when the previous owner splitting a biofluid sample into smaller volumn, he used the companyID to design labels for the new aliquots.

[John_G]

I understand - the FieldName Company_ID is very confusing, because you cannot tell whether it is your company or the vendor. I suggest if you can you rename that field VendorID, or VendorSampleID, and use something like SampleID for the number your company assigns to the sample (which is what you are doing). The reason I suggest that is because a field name CompanyID suggests that the field is identifying a COMPANY, which it isn't.

HTH

John