Grab first 3 records 2

[cwadams]

I have a tblTemp with 2 fields PropID, and RecordID
I can have many records but I want to always grab the first 3 records and pass to a report.

Any suggestions on the syntax for this VBA code?
I know that SQL uses TOP (3) but I cannot find a reference to this using Access.mdb and VBA.

 

[AlexCuse]

Are you populating your report from a recordset or a query?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[cwadams]

I am going to populate my report from the table or query I create using the TOP 3 records.

Backtrack: I have a table with let's say 68 records. After running a union query....Using SQL I am now left over with 5 unique PropID with a value or star rating in the RecordID field. For Example:
propid recordid
R12345 1
R12458 1
R15655 2
R12455 3
R12222 5
I want to grab the first 3 records in this Select statement and populate in a report. Does this help?

 

[cwadams]

Here is my example:

SELECT [Temp].[RecordID] AS RecordID, [Temp].[Property] AS Property
FROM [Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 1
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 2 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 2)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 3 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 3)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 4 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 4)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 5)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 6)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 7)]. AS [TopThree]

'*** This is where I want to say,,,,
WHERE Row ID <4 or TOP (3) I need the right Syntax???*****

ORDER BY [Temp].[RecordID], [Temp].[Property];

 

[AlexCuse]

I think this is what you want (not tested):

SELECT TOP 3 [Temp].[RecordID] AS RecordID, [Temp].[Property] AS Property
FROM [Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 1
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 2 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 2)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 3 and Temp.Property not in (Select Temp.Property FROM Temp  WHERE Temp.RecordID < 3)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 4 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 4)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 5)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 6)
Union
Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 7)]. AS [TopThree]
ORDER BY [Temp].[RecordID], [Temp].[Property];

As long as you have an ORDER BY, you can usually use TOP as well.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[TheAceMan1]

How are ya cwadams . . .

You could also make another query using your union query as its data source:

[blue]SELECT TOP 3 [purple][b][i]UnionQueryName[/i][/b][/purple].*
FROM [purple][b][i]UnionQueryName[/i][/b][/purple];[/blue]

See Ya! . . . . . .

 

[cwadams]

SELECT TOP 3 [Temp].[RecordID] AS RecordID, [Temp].[Property] AS Property
FROM [Select Temp.Property, Temp.RecordID FROM Temp WHERE Temp.RecordID = 1
Union....................

Thanks Alexcuse!!! It works!!!This works in SQL view of my query. But I want to convert to VBA and have it populate a query so that it processes quicker.
How would I do that?
**********
Set TopThree = CurrentDb.CreateQuerydef("TopThree")
strSQL = SELECT TOP 3 INSERT INTO TopThree(query)......????
**********
I am having a problem with finding the syntax.
...after populating query I run a report with its control source being the query.

 

[Zion7]

Do you not know how to concatenate strings in VBA?

All vba expressions surrounded by..... double quotes.
SQL qualifiers in .....................single quotes
VBA new line..... ................. Space & Underscore
Concatenate ......................... ampersand

 

[cwadams]

Set TopThree = CurrentDb.CreateQuerydef("TopThree")
strSQL = SELECT TOP 3 INSERT INTO TopThree(query)......????

Not to offend. This is not my code. I was trying to be descriptive in what I wanted.
Let me try something different.....

'Code is below
Set TopThree = currentDb..createQuerydef (TopThree)
strSQL = SELECT TOP 3 INSERT INTO TopThree [myVariables]
'I want to select the first three records & I want
'to populate a query with the results which would be
'the first 3 records.