Reformatting data

[RSW]

Hi all,

I am putting this in General because, frankly, I have no idea where it belongs.

I have a table that looks something like this:

Serial...Part.....Batch
1001......1.........A
1001......2.........A
1001......3.........B
1002......1.........A
1002......2.........B
1002......3.........B
1003...

How can set up a form, or a new table, or something so that the data is displayed like this:

............1.........2..........3
1001......A.........A.........B
1002......A.........B.........B
1003...

You see what I am trying to do? I am trying to make the Parts a column header, and kind of remove Serial/Part/Batch titles.

My first instinct was to take it into Excel and make a pivot table out of it, but that doesn't seem to work? The pivot table keeps counting up everything, and I don't want anything counted, I just want all data displayed.

Can anyone help? Thanks in advance.

 

[Moniker]

You've basically described a crosstab query in Access. Look into those and you should be able to figure out your answer.

 

[CraigDolphin]

What you probably need is a crosstab query. Search on google and here for more information on crosstab queries generally. The hard part is that you will need to specify some aggregate function for the value shown in the 'cells' and since the value is a letter, not a number, you will be limited to choosing 'First', 'Last' or somesuch.

Consider,
1001......1.........A
1001......2.........A
1001......3.........B
1002......1.........A
1002......2.........B
1002......3.........B
1002......3.........C

How would your new 'table' look in the preceding instance?
............1.........2..........3
1001......A.........A.........B
1002......A.........B.........?


You might have rules preventing more than one record for each combination of Serial and Part being present in the original table, but Access does not know that and the table structure does not prevent the situation from occuring. So, when creating a crosstab query, you need to specify how the query is to deal with multiple results for a 'cell' in your new table. If it is a number, you could use average, or sum, or count, or maximum or minimum etc. With letters, you can count, or use the first, or the last to aggregate results. But you need to specify some kind of aggregate 'just in case'.

 

[Rabbie]

You should get what your are looking for using a Cross-Tab query. You may needto tweek the SQL to get exactly what you are looking for

 

[RSW]

Sounds as if I need a cross-tab query I've never done one of those before, but I'm sure I'll be able to figure it out. Thanks all!