fill blank cells of duplicate records

[deso]

Hi, I have a database with duplicate records IDs. I need the duplicates, each duplicate has different water quality data. I want to fill in the blanks of certain fields for each duplicate, where a record for that field occurs in any duplicate of the same record. I need to organise it this way as a statistical package i am using needs it all in one table.

ID	depth	geology	date	data 1	data 2
A	2	sand	june	A1.1x	A2.1.x
A			august	A1.2.x	A2.2.x
B			janurary	B1.1.X	B2.1.X
B	11	rock
C	9	mud	october	C1.1.X

I need the blanks for the depth and geology fields to be filled in and be the same value as any duplicate records for depth and geology. So to look like this:

ID	depth	geology	date	data 1	data 2
A	2	sand	june	A1.1x	A2.1.x
A	2	sand	august	A1.2.x	A2.2.x
B	11	rock	janurary	B1.1.X	B2.1.X
B	11	rock
C	9	mud	october	C1.1.X

Can anyone help with ways to achieve this using a query/sql? Part of the problem is that sometimes the duplicate field with the value is above the empty duplicate field, and sometimes it is below.

[June7]

Will depth and geology always both be blank (or both not blank) for same record? Will there not be more than two records for each ID?

If so, try something like:

query DataNotNull
SELECT Table1.ID, Table1.depth, Table1.geology
FROM Table1
WHERE ((Not (Table1.depth) Is Null)) OR ((Not (Table1.geology) Is Null));

query DataNull
SELECT Table1.ID, Table1.depth, Table1.geology
FROM Table1
WHERE (((Table1.depth) Is Null)) OR (((Table1.geology) Is Null));

UPDATE DateNotNull INNER JOIN DataNull ON DateNotNull.ID = DataNull.ID SET DataNull.depth = [DateNotNull].[depth], DataNull.geology = [DateNotNull].[geology];

[deso]

Thanks for your reply. Yes depth and geology will always be blank or not blank in the same record, but sometimes there will only be 1 record, sometimes 2 duplicates, sometimes up to 15 duplicates. Each time however, regardless of how many duplicates there will only be one cell filled or depth and geology

Will depth and geology always both be blank (or both not blank) for same record? Will there not be more than two records for each ID?

If so, try something like:

query DataNotNull
SELECT Table1.ID, Table1.depth, Table1.geology
FROM Table1
WHERE ((Not (Table1.depth) Is Null)) OR ((Not (Table1.geology) Is Null));

query DataNull
SELECT Table1.ID, Table1.depth, Table1.geology
FROM Table1
WHERE (((Table1.depth) Is Null)) OR (((Table1.geology) Is Null));

UPDATE DateNotNull INNER JOIN DataNull ON DateNotNull.ID = DataNull.ID SET DataNull.depth = [DateNotNull].[depth], DataNull.geology = [DateNotNull].[geology];

[rpeare]

Here's what I'd do. Create a query with your ID, DEPTH and GEOLOGY. put IS NOT NULL in both DEPTH AND GEOLOGY and browse through that to make sure there are no duplicates. If there aren't change the query to a summation query (look for the SIGMA, looks like a capital E, button on your ribbon). This should give you a shortened list of ID's Then create a query based on your table and this query you've created and instead of showing the DEPTH AND GEOLOGY from your main table link it to your query through the ID field and use the DEPTH AND GEOLOGY from your summation query.