11-27-06 Concatenating rows into a diffrent table row

[Chris Lewis]

Access Help

My job: I have a task to organize drawings and keep out duplicates.

Problem: I am working with drawings that have multiple tracking numbers that
have repetitive information among the tracking numbers. These tracking
numbers are made of a series of pieces of the drawing to make a DWG#, Line#
and Flowchart#.

Example drawing:
DWG#: B123456-HPDK30001
LINE#: 4HPDK - 001-1
FLOWCHART: HPDKMO30-1
Parts of the drawing:
Group# Section Code Amount other … Page# Sheet
B123456 4 HPDK 30 MO30 001 1
…
…

Continue Problem: My table looks like the one above with everything in
parts. I did it this way to save time by only typing the info in once per
row. Each part can have the same info as the part above, but when the parts
become line#, they have there own unique # from the line# above. I don’t know
how to stop duplicate rows when all the parts can have similar info.

Example similar rows:
Section Code Page# Sheet
4 HPDK 001 1
5 HPDK 001 1
4 HPSR 001 1

What I have been trying to do is concatenate the 4 rows above into a linked
relationship table called Line#. When this table sees duplicates it alerts
the other table and makes you change the row as if a column was indexed (no
Duplicates). I just haven’t got this concept to work yet.

Please let me know if you have better ways of doing this same problem.

 

[TonyT]

Hi Chris,

You could acheive this by making the combination of each field your primary
key (ie. section/Code & Page) or you could just put code in the before update
event to check to do a dlookup to see if the data already exists.

The best approach would almost certainly be to split the common data out
into separate tables, and then use form event codes to populate the fields
with the same data as the preceeding line.

TonyT..