Combining data on a single table

[BIGGY]

I have a table which has 3 columns PCNAME PRODUCT, and SERIAL. For the PRODUCT I've narrowed them down to cpu's and monitors, along with their serial number. So basically a good portion of the work is done. However on the output the report of course lists most pc names twice since each one has both a monitor and a cpu associated with it. Ideally I would like for it to find the matching pc name and list all of the information on one row...so instead of it looking like this...

PCNAME PRODUCT SERIAL
pc0001 18monitor abcdef
pc0001 28cpu ghij
pc0002...etc

To look more like

PCNAME CPU CPUSERIAL MONITOR MONSERIAL
pc0001
pc0002...etc

Thanks!

 

[calmwind]

check out the attachment.

I added four fields to the query two for the monitor condition and two for the cpu condition. Then I showed the summary and grouped it by product name.

hope that helps.

 

[BIGGY]

Yep, that worked. Thanks! Now what if you have multiple instances. I noticed that when I tried to do an OR...that the table output was incorrect. So basically I had to do embedded IF's since I was searching for monitors by brand. I basically copied the entire line...here's part of the code to show what I did. Is there a more condensed way to do it?

First(IIf([product] Like "*Optiquest*",[product],IIf([product] Like "*Sony*",[product],IIf([product] Like "*NEC*",[product],IIf([product] Like "*VIEWSONIC*",[product],IIf([product] Like "*Compaq*",[product],"")))))) AS Monitor

 

[calmwind]

How can you tell if a product is a monitor or cpu by looking at the data? Can you have a Compaq monitor and cpu?

 

[BIGGY]

No. All of our CPU's are IBM, but even there I was looking for specific models. So I did the same thing for the CPU's.

 

[BIGGY]

Ok, for some reason the output is not correct. Attached is a zip with part of the table and the query I'm running on that table. Any ideas as to why it's not outputting correctly?

 

[calmwind]

First let me say, I'm only good for one great answer a year and I already posted that.

lol

1) Length of if then statement:

What if you shortened it to:

First(IIf([Product] Not Like "ibm" & "*",[Product],"")) AS Monitor

and

Last(IIf([Product] Like "ibm" & "*",[Product],"")) AS CPU,

Then if you are just looking for computers with Sony monitors, you can just put *Sony* in the criteria field.

2) Output problem:

I can't tell you why all the data isn't showing up. However, on your table you sometimes list the monitor information then the cpu information and the rest of the time you list cpu information then monitor information. For whatever reason, it is only pulling the data if the monitor is listed first. Again, don't know why. But, once the data is consistent, it works. (The monitor information just has to be listed sometime before the cpu information).

Hope that helps.