Combine data from multiple rows to a single record

[GreenLil]

Hello,

I have a problem that I can't seem to wrap my head around. Lets see if I can explain my issue in a way that makes sense. I have an Access database with a Key that doesn't allow duplicates, and need to import data to a temporary table, and the use an update query to move the data to the appropriate fields in the main table. So I have all of this covered ok. The problem lies in the data that is provided for import. The data provided to us has multiple lines with the same Key (which is our Label ID) because it separates the different tests (some examples PZA, INH, RIF, CAP etc).

Example (only showing a few of the many fields):

Label ID First Name Last Name PZA INH RIF CAP
10C000136 Bob Smith S
10C000136 Bob Smith S
10C000136 Bob Smith S
10C000136 Bob Smith R

What I need is to combine this so its all one line so I can run the update query to the main table and not have key violations.

Label ID First Name Last Name PZA INH RIF CAP
10C000136 Bob Smith S S S R


Any suggestions would be greatly appreciated, I don't even know where to start on this one.

Thanks

 

[MajP]

SELECT
[Label ID],
[First Name],
[Last Name],
Max(PZA) AS MaxOfPZA,
Max(INH) AS MaxOfINH,
Max(RIF) AS MaxOfRIF,
Max(CAP) AS MaxOfCAP
FROM someTable
GROUP BY
[Label ID],
[First Name],
[Last Name];

 

[GreenLil]

Wow that was pretty straightforward, thank you.

Unfortunately now I'm having problems with my update query, its giving me an error "Operation must use and updateable query" when I try and update the main table (Entrytable) with data from this query.

Any ideas on this one?

Thanks again for all your help

 

[Golom]

Please post the UPDATE query that you are trying to run.

Usually these things will run if you use an INNER JOIN but not when you use a cross-join and a WHERE clause to identify common records.

 

[MajP]

I am not very good either at doing insert queries when the data comes from a non updateable query. Hopefully someone can respond. The trick I always use in this case is to run a make table query first based on your query. Then you can use the table to run in your update query. It works, but I am sure there is a smarter way.

 

[dhookom]

Is there a good reason for storing/updating values in a table that can be calculated on the fly?

IMO, fields with names like "PZA INH RIF CAP" seem unnormalized. I could easily be wrong since I don't know how they are used.

Duane
Hook'D on Access
MS Access MVP

 

[GreenLil]

Thanks for the help, I was able to do a make table query from the first query and then do an update query from there.

The values aren't calculated, they're data entry of test results. Unfortunately there are some issue with normalizing the data because the access database is connected to a commercial software used for BioAnalysis. I can't change how it connects to the Entrytable.

But I have everything figured out now, and it looks good.

Thanks again.