consolidating data with empty fields (1 Viewer)

MilaK

Registered User.
Local time
Today, 08:51
Joined
Feb 9, 2015
Messages
285
Hello,


I would like to consolidate data in a table and write it to another table (please see the attached image).
Is this possible to accomplish with a query, if so how?


Thanks,


Mila
 

Attachments

  • example.jpg
    example.jpg
    28.9 KB · Views: 63

plog

Banishment Pending
Local time
Today, 10:51
Joined
May 11, 2011
Messages
11,611
Why? Both of those structures are probably incorrect. When you start numerating field names, you need to reevaluate your tables.

That data should probably be stored as such:

run_name, sample_name, Fvalue, Fnumber
1, sample1, 62115, 1
1, sample1, 113875, 2
1, sample1, 195050, 3

That is assuming the number after 'F' in your field names is actually needed.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:51
Joined
May 21, 2018
Messages
8,463
Agree with Plog, but if you wanted to do this. You can do it with a more complicated query with some sub queries or do it in parts.

1. Return the unique Run and Sample names
Code:
SELECT DISTINCT SampleRun.Run_Name, SampleRun.Sample_Name
FROM SampleRun;

2. Return just the records that have F1: qryF1
Code:
SELECT SampleRun.Run_Name, SampleRun.Sample_Name, SampleRun.F1
FROM SampleRun
WHERE ((Not (SampleRun.F1) Is Null));

3. do the same for qryF2, and qryF3 by replacing F1 with the correct value
4. Join all four queries by run_name and sample_Name
Code:
SELECT qryUniqueRecords.Run_Name, qryUniqueRecords.Sample_Name, qryF1.F1, qryF2.F2, qryF3.F3
FROM ((qryUniqueRecords INNER JOIN qryF1 ON (qryUniqueRecords.Sample_Name = qryF1.Sample_Name) AND (qryUniqueRecords.Run_Name = qryF1.Run_Name)) INNER JOIN qryF2 ON (qryUniqueRecords.Sample_Name = qryF2.Sample_Name) AND (qryUniqueRecords.Run_Name = qryF2.Run_Name)) INNER JOIN qryF3 ON (qryUniqueRecords.Sample_Name = qryF3.Sample_Name) AND (qryUniqueRecords.Run_Name = qryF3.Run_Name);
4. Use this final query in a make table query
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
Maybe I'm just hungry, but wouldn't a totals query grouping on run and sample and using Max on the other fields return the desired result?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:51
Joined
May 21, 2018
Messages
8,463
but wouldn't a totals query grouping on run and sample and using Max on the other fields return the desired result?

Yeah, that would be a lot easier.
Code:
SELECT 
  Run_Name, 
  Sample_Name, 
  Max(F1) AS MaxOfF1, 
  Max(F2) AS MaxOfF2, 
  Max(F3) AS MaxOfF3 
INTO 
  newtable
FROM 
  SampleRun
GROUP BY 
 Run_Name, 
 Sample_Name;
 

Users who are viewing this thread

Top Bottom