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

See this article in the knowledge base:

http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

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_)

There needs to be some identifier that groups like items. The problem I see is that you crippled your ability to group like items by combining the original unique identifier with the media type in a single field. You would have no problem if media type were its own field.

If the constant part of the field is fixed in length (which I doubt) you can use the left() function to extract a fixed portion of it. You could then group on the field -
MyGroup:Left(SKU, 4)

The suffix doesn't seem to be fixed in length either but if there are a limited number of them, you can write your own function to separate the two parts. I'll leave it to you to work out the code. Look up the Left(), Right(), Mid(), and InStr() functions to get started.

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

I didn't say that each format should not have its own record only that mushing two different attributes into a single field makes the field very difficult to work with especially as in your case where there is no clear separation between the values and the parts are not of fixed length. For example, if the value of the field were:

sk756
sk756,dvd
sk756,pal
sk756,cd

You could separate the two parts at the comma. The preferred method of course is to use two separate fields. You would then use a compound primary key. Access supports keys comprised of up to 10 columns.