Query a Query

[firefly2k8]

I have the following code. It creates an identifier with String1&Date1, then gets all unique records. Then creates an identifier with String2&Date1, and gets all unique records. Then again with String3&Date1. I use Union to stack them all together. But then i want to make sure that there are no duplicates in the result and I want to query it again.

Unfortunately it wont let me do rst1.Open strSQL2. So how do I query the result of the query? I will also want to permanently store the result of this query in a new table.

Thanks for your help as always.

Sub test3()
  Dim strSQL, strSQL2 As String
  Dim rst1 As ADODB.Recordset
  Dim fld1 As Field
  Dim int1 As Integer
 
  strSQL = "SELECT DISTINCT [String1] & [Date1] AS Expr1 " & _
            "FROM [Project Table 1] " & _
            "WHERE [String1] is not null " & _
            "UNION " & _
            "SELECT DISTINCT [String2] & [Date2] AS Expr1 " & _
            "FROM [Project Table 1] " & _
            "WHERE [String2] is not null " & _
            "UNION " & _
            "SELECT DISTINCT [String3] & [Date3] AS Expr1 " & _
            "FROM [Project Table 1] " & _
            "WHERE [String3] is not null ;"
 
 strSQL2 = "SELECT DISTINCT [Expr1] as Expr1 " & _
            "FROM [rst1]"
 
  Set rst1 = New ADODB.Recordset
  rst1.ActiveConnection = "FILEPATH;"
  rst1.Open strSQL
  Debug.Print rst1.RecordCount
 
  rst1.Open strSQL2
  Debug.Print rst1.RecordCount
 
  rst1.Close
  Set rst1 = Nothing
 
End Sub

 

[DCrake]

Create the union query in its own rights in the app then perfrom the rst2 on that query.

 

[namliam]

A property of UNION is that it does not return duplicates, if you want duplicates you have to use UNION ALL... No need to check it...

One thing you may want to consider is how the date is being interperted and shown if you are showing it.

 

[firefly2k8]

The select distinct query doesn't seem to be working for me. namliam - i wonder if this is because of the point you make about data types. I have a table with currencies, and dates. There are no blanks in the dates, but some blanks in the currencies - for good reason. So it looks a little like

Date Curr
01/01/10 GBP
01/02/10 EUR
05/02/10
08/03/10 USD
01/01/10 GBP

I want therefore the output

Date Curr
01/01/10 GBP
01/02/10 EUR
08/03/10 USD

But what I get with "SELECT DISTINCT [Curr] & [Date] WHERE [Curr] IS NOT NULL" is something like:

05/02/10
GBP01/01/10
EUR01/02/10
USD08/03/10

So it is getting rid of duplicates but not ignoring blanks.

Also I will want to deconstruct this back to two columns. Am i trying to do too much at once? Thanks for your replies

 

[JANR]

Your Where clause is a little off it should read:

....WHERE Not [Curr] Is Null;

JR

 

[vbaInet]

Your Where clause is a little off it should read:

....WHERE Not [Curr] Is Null;

JR

Apparently Is Not Null works too but it's not consistent with sql logic. I would go for Not Is Null like JR suggested.

But I suspect that the field is not null but zls so maybe try:

WHERE Not [Curr] Is Null And [Curr] <> ""

In the criteria row it will be:

Not Is Null And <> ""

 

[DCrake]

Should be

Not Is Null OR <> ""

 

[vbaInet]

Should be

Not Is Null OR <> ""

Slight typo there. That's correct David.

 

[vbaInet]

Actually, had another think of the logic and it should be And as I had it. We're talking about exclusion, not inclusion. If it was inclusion it will be Or but exclusion we need And.

 

[JANR]

Actually, had another think of the logic and it should be And as I had it. We're talking about exclusion, not inclusion. If it was inclusion it will be Or but exclusion we need And.

You sure about that, a field in a table can either be Null OR ZLS it can't be both.

Anyway the OP can always change the property of AllowZerolenght to NO and then he only need to check for Not Is Null

JR

 

[firefly2k8]

Thanks for all your help! It was AND in the end and this is working really well now. For completeness here is my query. Final thing. I now want to seperate the currency and date in the result. I was thinking of dumping this result in excel and using the left and right excel functions. Is there something neat i can do in the query?

SELECT DISTINCT [String1] & [Date1] As Identifier
FROM [Project Table 1]
WHERE Not [String1] Is Null AND  [String1] <>""
UNION
SELECT DISTINCT [String2] & [Date1] As Identifier
FROM [Project Table 1] 
WHERE Not [String2] Is Null AND  [String2] <>""
UNION SELECT DISTINCT [String3] & [Date1]  As Identifier
FROM [Project Table 1] 
WHERE Not [String3] Is Null AND  [String3] <>"";

 

[vbaInet]

You sure about that, a field in a table can either be Null OR ZLS it can't be both.
JR

Yes you can have both.

 

[JANR]

I stand corrected Sir, thanks.

JR

 

[vbaInet]

No worries JR. Was just re-iterating.

firefly: I don't understand your request. If you're talking about those two fields, then you shouldn't have concatenated them in the first place. Have them as separate fields and concatenate them in other queries or controls you would be using this union query with.

 

[firefly2k8]

Start point is a table with column of dates, column of currencies (string1), another column of currencies (string2) with some blanks, and a final column of currencies (string3) with some blanks.

I want to get to a table with a column of dates and a column of currencies. This table will be shorter as the identifiers formed of currency and date shall be unique.

My initial thought was to create a table of identifiers, using union. Then run a query on that table, which would be a select distinct query. Hence why the post came to be entitled query a query.

 

[vbaInet]

It still doesn't make your request clear?!?

If you want to separate them then add two more separate fields of Currency and Date? Isn't that what you want?

 

[firefly2k8]

I found the solution. Your posts have still been v helpful. Perhaps the solution will clear up the problem! Sry wasn't clear in first instance.

I've built 5 querys:

Query1:

SELECT [Date1], [String1]
FROM [Project Table 1]
WHERE Not [String1] Is Null And [String1]<>"";

Query2:

SELECT [Date1], [String2]
FROM [Project Table 1]
WHERE Not [String2] Is Null And [String2]<>"";

Query3:

SELECT [Date1], [String3]
FROM [Project Table 1]
WHERE Not [String3] Is Null And [String3]<>"";

Query4:

SELECT [Date1], [String1]
FROM [Query1]
UNION ALL SELECT [Date1], [String2]
FROM [Query2]
UNION ALL SELECT [Date1], [String3]
FROM [Query3];

Query5:

SELECT DISTINCT [Date1], [String]
FROM [Query4];

I didnt need the identifier in the end.

 

[vbaInet]

That was what I meant by splitting it and having the two fields separate. You don't need 5 queries. Go back to your UNION query, then use a second query based on the Union query and perform the DISTINCT on query 1 - just like you did in Query 5.

 

[namliam]

But what I get with "SELECT DISTINCT [Curr] & [Date] WHERE [Curr] IS NOT NULL" is something like:

05/02/10
GBP01/01/10
EUR01/02/10
USD08/03/10

This is indicative if your Curr not being NULL but an empty string.... try
WHERE [Curr] IS NOT NULL and [Curr] <> ""

Though this has been covered, but the "proper" syntax should be AND

So it is getting rid of duplicates but not ignoring blanks.

Also I will want to deconstruct this back to two columns. Am i trying to do too much at once? Thanks for your replies

If you want to deconstruct it, dont construct it ... and if you feel you need to construct it, keeping the deconstructed stuff will not cause an issue:
SELECT DISTINCT [Curr] & [Date] , [Curr] , [Date]...

Even just doing
SELECT [Curr] , [Date]...
Wont cause any issues

FYI, You shouldnt use DATE as a column name it is an access reserved word.