Help fixing a MS Access query

I have 2 tables: INV and PC_EQUIP. INV has 2 pertinent fields, CPU and PRT that contains data the resides in a single field in PC_EQUIP call Serial. I have a query that works as I am using the CPU and PRT fields in INV to match up with the Serial field in PC_EQUIP to return data from the Model field in PC_EQUIP. Code below:

SELECT INV.FULLNAME,INV.OFFICE, INV.location,....... INV.CPU, INV.PRT, PC_EQUIP.Model
FROM INV Left JOIN PC_EQUIP ON (INV.CPU = PC_EQUIP.Serial) OR (INV.PRT = PC_EQUIP.Serial)
WHERE INV.FULLNAME <> 'None'
ORDER BY INV.location ASC, INV.FULLNAME ASC

Open in new window


The code returns all the fields and the PC_EQUIP.Model for CPU in one line and PC_EQUIP.Model for the match to PRT in a second line. Of course the second line will duplicate all the fields that are not part of the JOIN. Is there any way to get the two fields returned to a single line using "AS" or something to distinguish the 2 instances of Model the Join is producing?

Grasping at straws here.

Thanks

DISCLAIMER: I'm not a DBA or web programmer so my SQL programming is negligible.
Mark LewisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
can you provide a database with a couple of rows of sample data?
0
Mark LewisAuthor Commented:
Run the saved query. You'll notice the last records are being created in duplicate because of CPU and PRT pulling info from the same Serial field.
The first two are duplicated because the PRT field is NULL. I am trying to get it to pull the duplicate results together in one line.
Example.mdb
0
Dale FyeCommented:
How about:

TRANSFORM First(PC_EQUIP.[Model]) AS FirstOfModel
SELECT INV.FULLNAME, INV.OFFICE, INV.location, INV.CPU, INV.PRT
FROM INV LEFT JOIN PC_EQUIP ON INV.CPU = PC_EQUIP.Serial    OR (INV.PRT = PC_EQUIP.Serial)
WHERE (((INV.FULLNAME)<>'None'))
GROUP BY INV.FULLNAME, INV.OFFICE, INV.location, INV.CPU, INV.PRT
ORDER BY INV.FULLNAME, INV.location
PIVOT IIf([Inv].[CPU]=[PC_Equip].[Serial],"CPU Model","PRT Model");
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
There are two serious problems with your schema.  Have you considered normalizing it?  You'll have a lot less trouble going forward if you do it sooner rather than later?

1. INV has a repeating group.  CPU and PRT should be separate rows in an Assigned Equipment table.  I'm sure at some point in the future, you will need to monitor additional items such as smart phones and tablets.  With your spreadsheet layout, you will have to modify all forms, reports, queries, and code that deal with this table and that means two more OR's in the join.  However, with a properly normalized schema, You would add a new type code to the type table and just start adding data.  NOTHING ELSE NEEDS TO CHANGE.  Big diference.
2. The relationship between AssignedEquipment and PC_EQUIP should be to the PC_EQUIP.ID (naming all your autonumbers ID is also poor practice.  It makes your schema  much harder to understand unless you provide a relationships diagram.

To answer your specific question, I've attached a sample from Helen Feddema.  You will have to modify it.  I didn't look at the example so I don't know how hard it will be.
accarch125.zip
0
Mark LewisAuthor Commented:
Dale,

The code works great in Access but when I try to inject that into my web page, it returns :

[ODBC Microsoft Access Driver] The Microsoft Jet database engine does not recognize '[CPUModel]' as a valid field name or expression.

Never had a query fail on a page that didnt work in Access but I've never used TRANSFORM or PIVOT before either.
0
Dale FyeCommented:
Is this an ADP?  You might be missing a space in there, try:

PIVOT IIf([Inv].[CPU]=[PC_Equip].[Serial],"CPU Model","PRT Model") IN ("CPU Model", "PRT Model")

Which should force both column headers "CPU Model" and "PRT Model" to display
0
Mark LewisAuthor Commented:
Receiving this after I changed that line:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
0
Dale FyeCommented:
This is an Access FE against an Access BE, right?  or are your BE tables on some other system?

Is this an Access Data Project?
0
Mark LewisAuthor Commented:
Access BE but a web page FE. I believe I am running the code via ASP on the IIS server. The code on the page is using the <%> </%> tag.
0
Dale FyeCommented:
I've never used ASP as a FE for an Access BE.

What driver are you using for your connection string between ASP and Access?

Is your BE an accdb or mdb file?
0
Mark LewisAuthor Commented:
It's an mdb. DRIVER={Microsoft Access Driver (*.mdb).

Dale,

I dont expect you to troubleshoot this page. You gave me code that works perfectly and I appreciate that. I was just hoping you may have an idea off the top of your head but I can work with it or even just deal with the dual half duplicated set of data that the original query returned.

Did I mention I appreciate the help? LOL
0
Dale FyeCommented:
1.  Have you tried saving the query in the Access database, and then running a SELECT query from ASP:

Select * from [SavedQueryName]

2.  ASP classic or .Net?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark LewisAuthor Commented:
And there's something I would never thought to do....

I believe it's .NET
0
Mark LewisAuthor Commented:
THAT, my friend, worked like a charm...let Access do the work! I really appreciate your help. Just in case any others are looking, below is the code I used in the view query:

TRANSFORM First(PC_EQUIP.[Model]) AS FirstOfModel
SELECT INV.FULLNAME, INV.OFFICE, INV.location, INV.CPU, INV.PRT
FROM INV LEFT JOIN PC_EQUIP ON INV.CPU = PC_EQUIP.Serial    OR (INV.PRT = PC_EQUIP.Serial)
WHERE (((INV.FULLNAME)<>'None'))
GROUP BY INV.FULLNAME, INV.OFFICE, INV.location, INV.CPU, INV.PRT
ORDER BY INV.FULLNAME, INV.location
PIVOT IIf([Inv].[CPU]=[PC_Equip].[Serial],"CPU Model","PRT Model");
0
Dale FyeCommented:
happy to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.