Speed of Code

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have an acess database and Table1 has 1800 records. Table2 has 14,000
records. What I need to do is look up an item in Table1 view its
corresponding entries in Table2 and pull a couple of fields from Table2 into
Table1 where those ITEMS are equal and the date is of the latest date.

How I do this now is open table 1 as a recordset, open table 2 as a
recordset. Start with the first item in table1 and cycle through match items
in table2 using a DO LOOP and where the date is the latest date I grab my
fields. When I reach the end of Table2 I use a DO LOOP to start table 1 at
record 2 and start over.

This process take 15 minutes to run through. Is there another way I can do
this without such a time penalty?
 
I have an acess database and Table1 has 1800 records. Table2 has 14,000
records. What I need to do is look up an item in Table1 view its
corresponding entries in Table2 and pull a couple of fields from Table2 into
Table1 where those ITEMS are equal and the date is of the latest date.

How I do this now is open table 1 as a recordset, open table 2 as a
recordset. Start with the first item in table1 and cycle through match items
in table2 using a DO LOOP and where the date is the latest date I grab my
fields. When I reach the end of Table2 I use a DO LOOP to start table 1 at
record 2 and start over.

This process take 15 minutes to run through. Is there another way I can do
this without such a time penalty?
Click to expand...

Yes; an Update query. Access uses Queries as a primary tool; it's only
rarely that you would need to use recordset walking to update a table!
IF (and it's a very big if and a big worry about storing data
redundantly!) you really want to do this, you should be able to create
a Query:

UPDATE Table1 INNER JOIN Table2
ON Table1.ITEMS = Table2.ITEMS
SET Table1.FieldX = [Table2].[Fieldx],
Table1.FieldY = [Table2].[FieldY]
WHERE Table2.datefield = DMax("[datefield]", "[Table2]", "[ITEMS] = "
& [Table11].[Items])

You'll need a unique Index on Items in Table1 and it will help to
establish a relationship between Table1 and Table2. Given those
indexes, I'd expect this to run in a few seconds for an 1800 record
table.

My design concern is that after you run this query, the moment that
you add a new record to Table2 the data in table1 is now WRONG, and
known to be wrong. If that's acceptable, then feel free to use this
query.
 
Using a Do loop on a table defeats the purpose of using a database
product like Access. Access is designed to deliver results liek you need
in a fraction of a second, using its built in tools (Jet SQL in particular).
Use queries to achieve what you need. If you need help setting up the
queries, post your tables configurations and you will be helped. With so
few fields, you should be getting your results instantly using a query.
Pavel
 
Thanks very much for the help and for the comments about the data integrity.
Unfortunately, our MRP/LEAN planning system isn't able to provide the info
needed in the form needed so what this program actually does is on demand it
grabs a list of our products and lines them up with their costs and then
adds in the last price paid. The MRP system has the Items and Costs in a
table where Items are the primary keys and duplicates are not allowed so
that is a simple query. However, the system does not maintain a last price
field so I head to a shipments table where Items is not a key or index and I
have to find the latest date in order to show the last price. That's why
speed was my original concern because the user executes it on demand and it
provides them an up to the moment report/spreadsheet.

Your query certainly helps the speed and works great! Thanks! I have a
problem with the last part of the query - ie, the '[Items] =
[Table1].[Items]' code where once I add that to the query, Access returns an
error that just says "Unknown". If I run without that part of the query -
although the results are not what I want, I do not get the error. So, I must
have something typo'd. Table1's Items field is indexed and is also the
primary key. Any chance you can glance at the query below and spot where I
made my error?

"UPDATE Table1 INNER JOIN Table2 ON Table1.Items = Table2.Items SET
Table1.Date = [Table2].[Date], Table1.Cost = [Table2].[Cost] WHERE
Table2.Date = DMax('[Date]', '[Table2]', '[Items] = [Table1].[Items]');"

thanks again!


John Vinson said:
I have an acess database and Table1 has 1800 records. Table2 has 14,000
records. What I need to do is look up an item in Table1 view its
corresponding entries in Table2 and pull a couple of fields from Table2 into
Table1 where those ITEMS are equal and the date is of the latest date.

How I do this now is open table 1 as a recordset, open table 2 as a
recordset. Start with the first item in table1 and cycle through match items
in table2 using a DO LOOP and where the date is the latest date I grab my
fields. When I reach the end of Table2 I use a DO LOOP to start table 1 at
record 2 and start over.

This process take 15 minutes to run through. Is there another way I can do
this without such a time penalty?
Click to expand...

Yes; an Update query. Access uses Queries as a primary tool; it's only
rarely that you would need to use recordset walking to update a table!
IF (and it's a very big if and a big worry about storing data
redundantly!) you really want to do this, you should be able to create
a Query:

UPDATE Table1 INNER JOIN Table2
ON Table1.ITEMS = Table2.ITEMS
SET Table1.FieldX = [Table2].[Fieldx],
Table1.FieldY = [Table2].[FieldY]
WHERE Table2.datefield = DMax("[datefield]", "[Table2]", "[ITEMS] = "
& [Table11].[Items])

You'll need a unique Index on Items in Table1 and it will help to
establish a relationship between Table1 and Table2. Given those
indexes, I'd expect this to run in a few seconds for an 1800 record
table.

My design concern is that after you run this query, the moment that
you add a new record to Table2 the data in table1 is now WRONG, and
known to be wrong. If that's acceptable, then feel free to use this
query.
Click to expand...
 
Your query certainly helps the speed and works great! Thanks! I have a
problem with the last part of the query - ie, the '[Items] =
[Table1].[Items]' code where once I add that to the query, Access returns an
error that just says "Unknown". If I run without that part of the query -
although the results are not what I want, I do not get the error. So, I must
have something typo'd. Table1's Items field is indexed and is also the
primary key. Any chance you can glance at the query below and spot where I
made my error?

"UPDATE Table1 INNER JOIN Table2 ON Table1.Items = Table2.Items SET
Table1.Date = [Table2].[Date], Table1.Cost = [Table2].[Cost] WHERE
Table2.Date = DMax('[Date]', '[Table2]', '[Items] = [Table1].[Items]');"

thanks again!
Click to expand...

Well... bear in mind, I don't know the structure of your tables! But
*IF* the field [Items] is a Numeric field, and is present in Table2 as
a foreign key (ideally with a non-unique index; if you don't have an
index it will slow right down again) you can just pull the criterion
out of the quotes:

DMax('[Date]', '[Table2]', '[Items] = ' & [Table1].[Items])

That is, if the current record in the UPDATE query is for [Items] =
123, the DMax expression will use the criterion

[Items] = 123

and find the maximum date for that item.
 
Any chance you can glance at the query below and spot where I
made my error?

"UPDATE Table1 INNER JOIN Table2 ON Table1.Items = Table2.Items SET
Table1.Date = [Table2].[Date], Table1.Cost = [Table2].[Cost] WHERE
Table2.Date = DMax('[Date]', '[Table2]', '[Items] = [Table1].[Items]');"
Click to expand...
Click to expand...


John Vinson said:
Well... bear in mind, I don't know the structure of your tables! But
*IF* the field [Items] is a Numeric field, and is present in Table2 as
a foreign key (ideally with a non-unique index; if you don't have an
index it will slow right down again) you can just pull the criterion
out of the quotes:

DMax('[Date]', '[Table2]', '[Items] = ' & [Table1].[Items])

That is, if the current record in the UPDATE query is for [Items] =
123, the DMax expression will use the criterion

[Items] = 123

and find the maximum date for that item.
Click to expand...

Thanks for the info. The Items field is a text field and in Table 2 is an
indexed field non-unique but not a foreign key. For whatever reason, when it
runs it returns a Microsoft Access error message that just says "Unknown".
Since it is a text field I also tried modifying and concantinating in quotes
for [table1].[items] but it still returns the "Unknown". Also tried on a 2nd
PC just to make sure. As another test to make sure that Dmax itself wasn't
the issue, I ran the query dropping the Items=..... code and it runs fine
(but this was a test and not the results I need). Once I add the '[ITEMS] =
'...... I just get error "Unknown". I suppose one other thing I can think of
immediately is that my Table2 probably has Nulls in the ITEMS field in some
spots. I wonder if that could do anything, otherwise both tables are
straightforward - ITEMS is text in both, COST is number in table1, DATE is
date in both (it has values in Table2. In Table1 it starts at 01/01/1980 and
is updated to the latest date for each ITEMS from Table2.), PRICE is number
in both (it has values in Table2. In Table1 it starts at 0 but is updated
with the price from Table2 for the latest date), and a field I do not use
called ID is a number in Table2 (this field has no correlation to table1).
So, what we're doing is just updating the DATE and PRICE field in Table1
whith whatever the most recents values are of these same field in Table2 for
each of the ITEMS in Table1. I shall have to try knocking the Null ITEMS out
of table2 and see if I still get the error, but I'm open to all ideas!
thanks!
 
Thanks for the info. The Items field is a text field and in Table 2 is an
indexed field non-unique but not a foreign key. For whatever reason, when it
runs it returns a Microsoft Access error message that just says "Unknown".
Since it is a text field I also tried modifying and concantinating in quotes
for [table1].[items] but it still returns the "Unknown".
Click to expand...

Since Items is Text the correct syntax (with the admittedly very
confusing quotes, using Chr(34) as ") would be

strSQL = "UPDATE Table1 INNER JOIN Table2" _
& " ON Table1.Items = Table2.Items" _
& " SET Table1.Date = [Table2].[Date]," _
& " Table1.Cost = [Table2].[Cost]" _
& " WHERE Table2.Date = DMax('[Date]', '[Table2]', "
& " '[Items] = " & Chr(34) & [Table1].[Items] & Chr(34) & "');"

Or you might do better to create the Update query directly in the
query database window, save it, and execute it from your code:

UPDATE Table1 INNER JOIN Table2 ON Table1.Items = Table2.Items SET
Table1.Date = [Table2].[Date], Table1.Cost = [Table2].[Cost] WHERE
Table2.Date = DMax("[Date]", "[Table2]", "[Items] = '" &
[Table1].[Items] & "'");
 
Back
Top