getting data from record to record in the same table (1 Viewer)

hello guys

i have a table that contain 3 fields
1- serial number
2- sub serial number
3- number
4- description

Serial Number | Sub Serial Number | Number | Description
1 | 1 | 1200 | Empty
2 | 1 | 1546 | One 1200
3 | 1 | 1652 | One 1200 - Two 1546
4 | 4 | 1750 | Empty
5 | 4 | 1890 | One 1750

what i want is
for example serial number 1 - 2 - 3 have the same sub serial number which is the number of the first record of this group in the first one the description is empty in the second one the description will contain the number of the first record in the third one the description will contain the number of the first record and the second record

i need this because we have files with updated information so we should entered again but in the same time linked to the old data of the same file

N.B: the sub serial will be manual entered because every file the serial number will be writed on the file
any solution?

First you will need to build a query that groups by "2- sub serial number" you want to have the order by clause for the query set to order ascending on the field "1- serial number" ... Then have a look at the code in the example database in this thread ---

Attached Files File Type: zip --- FieldDataHorizontalyOnReport.zip (22.7 KB, 514 views)

list details horizontally

You should be able to adapt the code to grab the descriptions and concatenate them into one set of data...

Actually thinking about it, I'm not sure "group by" is the way to go you might need an inner and outer record set loop...

thank you i'll try and if i can't reach my way i'll be back

thanks again

Let's step away from the keyboard and think about what you are trying to do. The specific request to store this data violates 2nd normal form because the description field does not depend entirely on the PK of the row. It depends on some indeterminate number of other records that "precede" this one.

What exactly is storing this data doing for you that a query that selects a set of data based on "Sub Serial Number" and then sorts them ascending by "number" won't tell you?

Pat Hartman said:

Let's step away from the keyboard and think about what you are trying to do. The specific request to store this data violates 2nd normal form because the description field does not depend entirely on the PK of the row. It depends on some indeterminate number of other records that "precede" this one.

What exactly is storing this data doing for you that a query that selects a set of data based on "Sub Serial Number" and then sorts them ascending by "number" won't tell you?

Click to expand...

i don't know if i an do it this way
because the records will not be entered the same time maybe the second record will enter after one month maybe 6 maybe one year and not all records will have another record for the same case
so if i made aquery i should make it another one as update right ?

If you select sub serial number = 1, then what ever rows are currently in the table will be returned. It doesn't matter when they were entered. What matters is what is there when you are looking at the data.