[Resolved] Undefined column problem

Sorce

I am trying to write a query for a msaccess database which I did not write and it is not designed well at all.

Most of the data that I need is in a single table with the columns "name" and "value".

For example

rpt---------Name----------------Value
1-----------Processor------------1000mhz
1-----------Memory--------------512mb
1-----------OS-------------------Windows 2000 Professional
2-----------Processor------------800mhz
2-----------Memory--------------256mb
2-----------OS-------------------Windows 2000 Professional

I am trying to pull only certian values but need multiple ones. Is there a way to pull for example both the processor and memory in the same sql statment and have them be listed as columns rather than seperate rows?

I think I can probably do it with a scripting language and multiple queries, just wondering if there is a way to get the info in one sql statement.

Also I have used PL/SQL before in oracle. Is it possible to do any scripting like this in access?

laserlight

It would probably be better just to normalise the database, in the long run.

Anyway, why not just select what you need then loop through the rows returned?

Roger_Ramjet

I agree with laserlight. While it would be an easy query in Access, with its support for UNION and Subqueries, the result will be cumbersome and inflexible. Better to just run a Make Table query that stores the data the way you need it. I do this frequently in Access, intermediate tables can make queries a whole lot simpler. Most importantly, they can cut execution time dramatically compared to one complex query because of the way Access works. You can run the make table as often as you need to keep the data current.

Roger_Ramjet

And I read the rest of your post again Sorce.
Yes, Access uses VBA for scripting. You can use it to build and run sql statements, or to execute a series of stored queries within 1 transaction, etc.

Queries can use other stored and named queries as input, so you can build a recordset in 1 query and select, group and order from it as you wish in another.

VBA is just a flavour of visual basic and easy enough to pick up.

I'm not familiar with the ODBC functions for php/access so I can't tell you how you call a stored query or function from within your php script. Maybe someone can tell us if it is possible?

Sorce

Thanks for the input guys. I was afraid I was going to have to use another language to get the data into another database in a form I can use.