Table with multiple like fields that I need to display into an Array-like format

[dmoro6]

Hi all,


I have a table that contains the following;

Company name, PositionID, Firstname1, Lastname1, title1, gender1, Firstname2, Lastname2, title2, gender2, Firstname3, Lastname3, title3, gender3 [...up to 50 names]

The data is uploaded to my table from a 3rd party database source via .csv file.

I can capture up to 50 names, titles and gender per record. I would like to display this data in a table/array like format. I'm not too good at VBA but I can use expressions for unbound fields.

I have attached a file that shows the data in a format I would like to see in Access displayed in a form.

If this must be done in a multi step process, that's okay too.

David

[June7]

A UNION query could manipulate the records into the normalized structure. UNION query has a limit of 50 lines. Also, VBA to open recordset from UNION query does not work (at least wouldn't for me). There is no wizard or designer for UNION, must type into the SQL View window of Query Designer. Be grateful for copy/paste (Ctrl+C/Ctrl+V).

SELECT [Company name], PositionID, Firstname1 As Firstname, Lastname1 As Lastname, title1 As Title, gender1 As Gender FROM tablename
UNION SELECT [Company name], PositionID, Firstname2, Lastname2, title2, gender2 FROM tablename
UNION SELECT [Company name], PositionID, Firstname3, Lastname3, title3, gender3 FROM tablename
...
UNION SELECT [Company name], PositionID, Firstname50, Lastname50, title50, gender50 FROM tablename;

Then either save the records to a 'master' table in normalized structure or just work with the UNION query as a data source.

To append records to 'master' (assumes fieldnames in master are same as those in the UNION query):
INSERT INTO Master SELECT * FROM Query1;

[dmoro6]

Thanks for the tip June7!!!