Values from different colums....

[konquistador]

Hi Gurus,
Here's my situation. I have three columns in Access:
Attribute Name, DateValue, CharValue.
I wish to create a make table query which does the following:
If Attribute Name = 'approved_date' then take the value from DateValue Column and populate it in a New table 'new'.
but if the
Attribute Name = 'description' the take the value from CharValue column and populate it in the same table 'new' in a different column. Please help, how to achieve this.

Thanks,
Kon

 

[tehNellie]

on the information that you've provided, the question has to be asked, Why? You're going to end up with a two column table with the charvalue column NULL where DateValue is populated and vice versa.

CREATE TABLE new (datevalue date, charvalue text(50));

to create your table, I'm assuming date and text values

INSERT INTO new (datevalue)
SELECT (datevalue)
FROM someTable
WHERE attribute = "approved_date"

then

INSERT INTO new (datevalue)
SELECT (charvalue)
FROM someTable
WHERE attribute = "description"

to populate the data, if you want unique values use SELECT DISTINCT instead, but what does this achieve that simply querying the original table doesn't?

I don't believe you can bundle multiple transactions in Access so you'll have to run a series of docmd.runSQL commands (or create static queries) from VBA (you'll need to check if new exists and either skip the create table statement or drop the existing new table first).