Do loop

[mls via AccessMonster.com]

I have access table with 2 columns ( sample and location) and want to create
a third column ( target) using VBA coding. How can I do this?

sample location target
H5VC 1 A8 A8-G8
H5VC 1 B8
H5VC 1 C8
H5VC 1 D8
H5VC 1 E8
H5VC 1 F8
H5VC 1 G8
H5VC 2 A9 A9-G9
H5VC 2 B9
H5VC 2 C9
H5VC 2 D9
H5VC 2 E9
H5VC 2 F9
H5VC 2 G9
H5VC 3 A10 A10-G10
H5VC 3 B10
H5VC 3 C10
H5VC 3 D10
H5VC 3 E10
H5VC 3 F10
H5VC 3 G10
HSC 1 A2 A2-G2
HSC 1 B2
HSC 1 C2
HSC 1 D2
HSC 1 E2
HSC 1 F2
HSC 1 G2

 

[KARL DEWEY]

Try this -
SELECT [sample], [location], (SELECT Min([Location]) FROM YourTable AS [XX]
WHERE [XX].[sample] = [YourTable].[sample]) & " - " & (SELECT Max([Location])
FROM YourTable AS [XX] WHERE [XX].[sample] = [YourTable].[sample]) AS [target]
FROM YourTable;

 

[Bob Quintal]

I have access table with 2 columns ( sample and location) and want
to create a third column ( target) using VBA coding. How can I do
this?

Do not do this. All you need is a query that selects the minimum and
maximum values of location grouped by sample.
something like, with corrected table name.
SELECT sample, min(location) & "-" & max(location) as target from
yourtable group by sample;

If you try to write target to the table, you will discover it very
difficult to maintain, so just use the query.

 

[mls via AccessMonster.com]

Thank you so much. I did not expect the solution to be so simple. It did work
like magic.

I have access table with 2 columns ( sample and location) and want
to create a third column ( target) using VBA coding. How can I do
this?

Do not do this. All you need is a query that selects the minimum and
maximum values of location grouped by sample.
something like, with corrected table name.
SELECT sample, min(location) & "-" & max(location) as target from
yourtable group by sample;

If you try to write target to the table, you will discover it very
difficult to maintain, so just use the query.

sample location target
H5VC 1 A8 A8-G8

[quoted text clipped - 25 lines]

HSC 1 F2
HSC 1 G2