Access: many to one from a pre-filled table

[iidlraw]

I want to use data from a pre-filled table that has many unique records (Field ReqNumber) assigned to one person (Field Name) in multiple rows. I want to be able to assign a unique number to the person so that I can get a single record that I can put into a form. I have a basic understanding of relationships, queries, tables, forms, etc.

[June7]

Not clear what you want. Do you want to create a table with a unique record for each person to have a table of data about that person (name, phone, etc)?

[iidlraw]

Yes, I think you've described what I want. I have a table that auto-updates 2x daily from another system with items received from clients. In this table there is a field for the item (ReqNumber) and a field for the client (Name), plus a field for DateReceived. Items received per client come in as separate records and therefore, there are multiple rows per client sometimes separated by days or months. I want to be able to assign each client a unique identifier (that I specify) so that I have a single client record to which I can add other client specific information through a form.

[June7]

Why would you need to assign an ID? Don't the imported records have an ID for each client?

[iidlraw]

No, not for the client. In the table that auto-downloads, the ReqNumber is the primary key (it corresponds to a medical specimen). I have no control over this table, only access to the data.

[June7]

Then you need a lot to happen with the downloaded data.

1. assign existing client IDs to new related records

2. save new clients into Clients table and assign an ID

3. assign the new client IDs to the related records

And the only common identifier is client name? Names are very poor unique identifiers. What if there are two patients named John Smith? What if Betty Carson is misspelled as Bettye Carson in new records?

[iidlraw]

Doc3.doc Hi I've uploaded a short version (with names changed) of the table that I have to work with that includes the other fields. I've got dob as an extra identifier for the client. Are you saying that I have to create a client table and does this mean manually transferring them from the PatientTable?

[June7]

Patient, Client whatever you want to call them.

You tell me what you want. You're the one who said in initial post "I want to be able to assign a unique number to the person so that I can get a single record that I can put into a form".

A normalized structure would be like:

tblPatients
PatientID
LastName
FirstName
DOB
Gender
Phone
Address

tblReqs
ReqNum
ReqDate
PatientID

I have no idea what Panel, State, Pref, SenDate are nor why they are duplicated between ReqNumbers for the same patient.

[iidlraw]

The table in my attachment is how I receive it from another system and each row collects a separate entry so all the fields are downloaded together. In this table, the Reqnumber is the unique sample ID. Panel = test, State = geographic location ie. California, Pref = year prefix, SenDate = date sample received.
Here is my task: You give blood, urine and swab samples (each with their own number "ReqNumber") to your doctor and they are sent to the hospital for testing. The hospital enters this information against you, the patient, on their system. The table I can access is pulling out the samples for processing eg. your 3 samples as 3 rows in the table. I want to get all the samples from the same patient as one record. Perhaps this is not possible?

[June7]

Okay, that helps explain somewhat. Denormalizing data can be tricky. A CROSSTAB query doesn't suit your data. VBA might be the only option. Review http://allenbrowne.com/func-concat.html

[iidlraw]

Hi,
thanks so much for your help, the allen browne scenario seems to be what I want to do. I'll wade through the VBA information and give it a go. If I get stuck, are you familiar enough with this coding for me to ask for help?

[June7]

There are quite a few threads here where I have helped posters with this same code. Search on concatenate related concatrelated and specify my username to further narrow the search.