Creating and assigning new ID's to duplicate records

[drow]

Hello All,

I have been doing a fair bit of searching around looking for examples where others are trying to accomplish a similar task to what I am working on, but I can not seem to find very much so today I joined the forum . Now for the task I am trying to perform.

There is currently a database which contains location information on a variety of different samples, each which is identified by a unique sample number (the primary key). However, when these samples are processed, they are split it half and each is assigned a second identifier for each half (in this case, an A or a B). Therefore, if originally there was sample 1, it will be split into 1A and 1B. My task is essentially to create a second database with further information for each sample half (1A or 1B), while maintaining a link to the main sample database to reflect any changes to the sample location information. So far I have been using a make table query that pulls from the linked table which links back to the main database and combines with another table that simply has one column called "Piece" which has an 'A' in the first row and a 'B' in the second row.



I am quite new to using access, but I have found this task fairly frustrating so far because it feels like I am trying to force access to work in ways that it was not designed. Essentially I would like access to create a duplicate entry for each sample in the original database, and force in an 'A' or a 'B', and combine these fields to create a unique identifier. However, I am running into a lot of issue when trying to append data from the original database etc. and I was hoping that someone would be able to provide a sanity check on the actual task I am doing, and if so, potentially some suggestions on how to accomplish it.

If this has already been covered before and I have missed it, please point me in the right direction!

Thanks,

Danny

[jzwp11]

Welcome to the forum!

So far I have been using a make table query that pulls from the linked table which links back to the main database and combines with another table that simply has one column called "Piece" which has an 'A' in the first row and a 'B' in the second row.

So it sounds like you have 2 linked tables and you need to create a third table to capture the joining of the two linked tables. If you have the tables linked in Access then they should work exactly the same as one that is not in another database. I would guess that the 3rd table would look something like this:

tblSampleSplit
-pkSampleSplitID primary key, autonumber
-fkSampleID foreign key to linked sample table
-Piece

Creating the records is not a big issue, but

and force in an 'A' or a 'B', and combine these fields to create a unique identifier

I would have an autonumber key field as the unique identifier. You can always concatenate the sample & piece values if you want to show the sample as 1A, 2B, etc.

Now to populate the split table (as shown above), I would frankly create the table structure as shown and just use an append query rather than using a make table query.

Create a query that joins the two linked tables

SELECT sampleID, piece
FROM sampletable, piecetable

Using the query above will yield a Cartesian Product of the two tables or in other words A&B for each sample.

Now change the quey type from SELECT to append. Map the query fields to the corresponding split table fields and run the query.

[John_G]

Hi -

Because your split sample data is different form the original, then putting it in another table is fine, in fact correct. What you now have is a one-to-many relationship (where the "many" happens to be 2). The second table has a compound (multiple-fields) PK, The sample number, and the piece number. You are on the right track with a make-table query; what I would suggest is to create the second table, leave it blank, and then use append queries to populate it. These append queries (one for each of pieces "A" and "B") will initialize the second table with the required information from the first table, leaving the rest blank, to be filled in as testing is done.

With this, you are using Access as it is designed to be used.


John

[drow]

Hi jzwp,

Thanks for your speedy response. I am not sure if I did the best job of explaining myself so I am not sure if we are on the same page or not. It is quite possible I havnt completely understood what you have written.

So it sounds like you have 2 linked tables and you need to create a third table to capture the joining of the two linked tables. If you have the tables linked in Access then they should work exactly the same as one that is not in another database. I would guess that the 3rd table would look something like this:

tblSampleSplit
-pkSampleSplitID primary key, autonumber
-fkSampleID foreign key to linked sample table
-Piece

Currently I have one main database that looks similar to as follows:

tblSampleTracking
-Sample#
-Location Information
-Notes

I have created a second access file which has a linked table which is to the database listed above (the files need to be seperate). From this point, I would like to duplicate (twice for each record, or an 'A' and a 'B' for each record) the records in the tblSampleTracking table and create a new table, lets say tblPieceTracking. This new table should have two records for each sample in the main database, but each sample should remain unique because it has now been assigned a 'A' or a 'B' which I can concatenate with the original sample number to create a sample ID "tblSampleTracking.[Sample#]&tblPieceTracking.[Piece]. Also included in this table creation, I would like to add additional columns in the new table tblPieceTracking that are not present in the main database (ie. tbleSampleTracking). Ideally I would like to do this in one step if possible but the way I was doing it before I was running into difficulties because I used the simple A/B table combined with the tblSampleTracking without there being a relationship between the two.

John_G,

Thanks you as well for getting back to me. The one thing that confuses me is the actual mechanism to create the duplicate records and add in the A/B. The way I was using seems like a bit of a hack job. I will keep at it and see if anything becomes clearer using some of the information provided by you and jzwp.

Cheers

[orange]

Using the info that jzwp11 and John G gave, I have attached a sample mdb to show how it works.
3 tables were created.

Samples -- to represent your samples
tblAB -- to represent the "A" and "B" values as jwzp11 discussed with the Cartesian product.
Table2 -- represents the subsamples with fields SampNo and SubNo

All three tables were manually designed, but table Samples was populated with records based on the procedure PopSamples in Module1.
This proc started with a baseNumber 450 and created 500 test records
There were 2 records in tblAB --- an A record and a B record
Then, the Append query AppendRecordsToTable2, was run to populate table2. Thus table2 has 1000 records that can be related to Samples.

Hope it's helpful.

[drow]

orange,

Thank you for your response. It helped me to get over a mental hurdle I was having. Originally I was try to create my entire master table (ie Table2 in your example) through queries and I was having issues because of multiple unrelated tables. Instead, if the append query is used to create the a/b field pull the data from the linked table, while I create all other fields manually in a table (Table2 in your example). Very silly of me!

When I use the append query to pull the data from the linked table and create the new records for a/b, I have added an expression which creates a field called fullsampleid which concatenates the a/b field and the sample number to create a unique ID. Ideally I would like this to become the primary key for my master table so that this field is controlled, but if I do so, I receive errors when running the append query because of table key violations. Any suggestions?

The last task I have to perform to complete the master table is the append/update features. This is required so that any changes or additions in the main database are reflected in the one that I am making. I just need to figure out how to append only new records using some combination of the find unmatched query and the append query and then everything should be up and running and then have this function auto run on file open.

I will post back with an update when (hopefully) everything is sorted out.

Thank you to everyone for your support!

[drow]

Hello All,

I am hoping to put the paddles to this thread and revive it. I have my database pretty much complete, but the last feature I want to add is the update feature so that any changes (excluding additions) to the data in the main database, to which my database is linked, will be reflected in my database. However I am having a heck of the time actually getting this to work. I have created essentially an inverse unmatched query that shows all records in the main database which are present in my database. This query also uses the totals field with the "group by" function for the primary key in the main database with all other fields set to "first" so that only unique records are shown (this is because the records in my database have been duplicated to allow for the A/B renaming. Once this is done I have created essentially the same query as the append query that was used to populate my database in the first place, but this query appears to concatenate the data again: now there is 4 records for each record in the main database instead of 2. Does anyone have experience with performing this type of task, or have suggestions on how to go about doing this?

Any help would be appreciated.