Query with identifiers formed from columns

[firefly2k8]

I have a table with a string field, call it String1 and a date field, call it Date1.

I want my SQL query to return all unique instances of String1&Date1.

I think i might have this with:

SELECT DISTINCT [String1] & [Date1] AS Expr1
FROM [Project Table 1];

However also in the table is String2, and String3. I want unique instances of String2&Date1 and String3&Date1 that are not already in the result of my String1*Date1 query to be added to the result.

I am still not thinking in SQL/Access terms, so imagine you are in excel. Here I would create 3 columns for identifiers: string1&date1, string2&date1, string3&date1. Then I would copy the identifiers and stack them in one column. Then I would filter for unique indentifiers.

I was hoping the power of Access SQL would mean i wouldnt have to create columns in my table for identifiers.

Anyone feeling brainy this morning?

 

[Galaxiom]

SELECT DISTINCT [String1] & [Date1] AS Expr1
FROM [Project Table 1]
UNION
SELECT DISTINCT [String2] & [Date1] AS Expr1
FROM [Project Table 1]
etc

However, the presence of fields called String1, String2 etc in your tables strongly suggests a denormalized data structure.

 

[firefly2k8]

Thanks, i'll try this. Could you explain "denormalized data structure"?

 

[firefly2k8]

This works nicely but there is an additional thing I need it to do. Some of the records for String2 and String3 are blank. But the corresponding date is not. So the identifier String2&Date1, just returns Date1. I want to skip over these an not add them to my unique list. I tried the following but to no avail;

SELECT DISTINCT [String1] & [Date1] AS Expr1
FROM [Project Table 1]
UNION
SELECT DISTINCT [String2] & [Date1] AS Expr1
WHERE NOT IS NULL  [String2]
FROM [Project Table 1]
UNION
SELECT DISTINCT [String3] & [Date1] AS Expr1
WHERE NOT IS NULL  [String3]
FROM [Project Table 1];

I also tried using WHEN [String3] <>'' before the FROM statement, but the syntax was also rejected.

Thanks

 

[DCrake]

SELECT DISTINCT [String1] & [Date1] AS Expr1
FROM [Project Table 1]
UNION
SELECT DISTINCT [String2] & [Date1] AS Expr1
WHERE [String2] Is Not Null  
FROM [Project Table 1]
UNION
SELECT DISTINCT [String3] & [Date1] AS Expr1
WHERE [String3] Is Not Null  
FROM [Project Table 1];