Combining Similar to One Row

[Cameroncha]

I have a table with a bunch of different movies, Some items are available in dvd, vhs, or pal formatting.

each one of these items is keyed fairly consistantly in the database structure via a FormatID field and an indication in the sku itself.

an example of how they are keyed:

SKU - sk756 - FORMATID - V
SKU - sk756dvd - FORMATID - D
SKU - sk756pal -FORMATID - P
SKU - sk756cd -FORMATID - C

So my question is this: I have a report the generate items out that has a lot of visual redundancy and space is important for the pricelist. How could I consolodate 4 redundant lines on the report to 1 line that had an additional field that says something like: "available in: VHS, DVD, PAL, CDROM" formats to save space"

I am not so new to Access but am pretty new to VB.... suggestions????

Thanks for reading.
please note: the attachment is confidantial

 

[Cameroncha]

thanks... this is a great resource.

The SKU's are unique though... even though i have multiple formats, they dont create duplicate (or one-to-many) relatiionships because each item has its own unique key for different formats.... there are not duplicate entries of key values to make a one-to-many situation.

can similar keyed items in the database be consolodated, lets say the first occurance of a sku, then a comparison process looks lets say 4 characters down the SKU field, and if it finds a uniqueness, up for a certain percentage of the characters, it just doesn' t show that row in the report but instead just gives an indication that the item exists.

This probably doesn't make sense, sorry if it is confusing, not quite sure how to explain exactly.

very helpful link though, I can use this for queries that have one-to-many relationships.

Thanks. Cam_)

 

[Cameroncha]

thanks again for you comments....

I do have a format identifier, and i have a sku identifier.... there are two seperate fields, but the sku field does give infomation of the format.

So, what you are saying is if i have a structure that allow relationships to be made between items... or grouped in such a way that allows a group of id's to be identified as 1 item would be the way to go.

i didn't originally design the database that i get items out of. the existing system has a unique record for each format because they have unique sets of information. ... descriptions differ, upc, price, ect....

what would be a good approach to grouping the items together...

you are really getting my mind spinning!!! thanks for the feedback