Creating Temporary Queries on the Fly

[bacon333]

Similar to table variables and cursors but for queries. I'm trying to filter out all records with a particular "part number" then keep that query because I need to filter out more records from that query. The whole point of this is do that I don't run checks on all of the million records once i find the part number. Any ideas?

 

[Mucman]

How about using temp VIEWs?

Say you have a SP that takes in an id.

You can do:

CREATE VIEW viewTemp AS (
SELECT *
FROM SomeTable
WHERE id = @id)

... do stuff with view

DROP VIEW viewTemp

 

[KLin]

Originally posted by: Mucman
How about using temp VIEWs?

Say you have a SP that takes in an id.

You can do:

CREATE VIEW viewTemp AS (
SELECT *
FROM SomeTable
WHERE id = @id)

... do stuff with view

DROP VIEW viewTemp

depends on if his data resides on a database server or not

 

[Mucman]

Originally posted by: KLin

Originally posted by: Mucman
How about using temp VIEWs?

Say you have a SP that takes in an id.

You can do:

CREATE VIEW viewTemp AS (
SELECT *
FROM SomeTable
WHERE id = @id)

... do stuff with view

DROP VIEW viewTemp

depends on if his data resides on a database server or not

True.... I assumed a lot when I posted what I did

 

[bacon333]

Will VIEW work with linked tables in Access?

 

[KLin]

can you give us a big picture idea of what it is you're trying to do? that would help us help you.

 

[bacon333]

Sorry bout that, here's the big picture:

[partnum] [line] [proccode]
9594 STD temp1
9594 STD temp2
9212 STD temp4
9594 PGA temp5
1234 PGA temp6
9594 PGA temp7

okay the table looks like that but imagine like 900,000 more records. The user specifies a part number and a line. I first need to check if the part number exists. In my case, I'm looking for all of the part numbers that are 9594. Then after I find that part number I want to grab the "line" that the user specified, let's assume it's STD. I just need the quickest way (without having to go through 900,000+ records) to select and count only those records. Bottom line is I want to know if the part number exists and how many of those records with that particular part number is of the STD line.

I've done a Dlookup to check if the part exists and a Dcount to count the number of records that are both part number 9594 and line STD however I'm not sure if Dcount goes through all the records or somehow has a quicker algorithm that will pull records out faster than Select Count(*)

I hope this clears things up.

 

[KLin]

Originally posted by: bacon333
Sorry bout that, here's the big picture:

[partnum] [line] [proccode]
9594 STD temp1
9594 STD temp2
9212 STD temp4
9594 PGA temp5
1234 PGA temp6
9594 PGA temp7

okay the table looks like that but imagine like 900,000 more records. The user specifies a part number and a line. I first need to check if the part number exists. In my case, I'm looking for all of the part numbers that are 9594. Then after I find that part number I want to grab the "line" that the user specified, let's assume it's STD. I just need the quickest way (without having to go through 900,000+ records) to select and count only those records. Bottom line is I want to know if the part number exists and how many of those records with that particular part number is of the STD line.

I've done a Dlookup to check if the part exists and a Dcount to count the number of records that are both part number 9594 and line STD however I'm not sure if Dcount goes through all the records or somehow has a quicker algorithm that will pull records out faster than Select Count(*)

I hope this clears things up.

well that's how Dcount should work. HEre's a quick way to see if it's working. Take the number of records that a dcount returns, then do a query to count all the records where the partnum = 9594 and the Line = "STD" and see how many records are returned. Using Dcount is probably going to be the best way to do it in VBA code.

 

[bacon333]

running both the dcount and dlookup works but requires two searches in a huge database. would the VIEW method work faster since i'm filtering out just the part number 9594 into a temporary table/query so I can just "play" with that table/query instead of running queries on the entire database?

 

[cchen]

Originally posted by: bacon333
running both the dcount and dlookup works but requires two searches in a huge database. would the VIEW method work faster since i'm filtering out just the part number 9594 into a temporary table/query so I can just "play" with that table/query instead of running queries on the entire database?

OT, but, why are you using Access when you have 1 million records? Access is not robust enough to handle that.....

Ever considered moving over to SQL Server?

 

[bacon333]

My company uses Access, they're going switch to sql in a few years *shrug*

I couldn't get the CREATE VIEW to work in access. I also couldn't create a temporary table CREATE TABLE #temptable.

my temp table code:
Set cnnCursor = CurrentProject.Connection
strCursor = "CREATE TABLE #theCursor (age int(2))"
rstCursor.Open strCursor, cnnCursor, adOpenStatic, adLockPessimistic
rstCursor.Close

my view code:
strView = "CREATE VIEW theVIEW (age int(2))
docmd.RunSql strView

the temporary table is the same as a cursor right? I might be a little confused on that.

Has anyone got any kind of temporary table to work in Access?

 

[MrChad]

Originally posted by: cchen

Originally posted by: bacon333
running both the dcount and dlookup works but requires two searches in a huge database. would the VIEW method work faster since i'm filtering out just the part number 9594 into a temporary table/query so I can just "play" with that table/query instead of running queries on the entire database?

OT, but, why are you using Access when you have 1 million records? Access is not robust enough to handle that.....

Ever considered moving over to SQL Server?

No kidding. A million records will bring Access to its knees. Your application will perform like sh1t, too.

 

[bacon333]

heh, well some how they managed to keep it pretty stable for about 5 years.

 

[MrChad]

Why don't you setup a query in Access that filters out that part number? Queries in Access are equivalent to SQL Server views.

 

[bacon333]

because if more than one person uses the application at the same time, it might overwrite that query or what not. i want to create a query/table in memory so that when another person submits a form a seperate temporary query will be created for that particular user.