how do I write the contents of an array to an access table

[Guest]

I would like to populate an array with data from a table, manipulate some or
all of the data, then write the array contents to a different table. The
first two steps are relatively straight forward but the last step has me
baffled. Any suggestions?

 

[John W. Vinson]

I would like to populate an array with data from a table, manipulate some or
all of the data, then write the array contents to a different table. The
first two steps are relatively straight forward but the last step has me
baffled. Any suggestions?

Open a recordset; loop through the array, use AddNew to create a record, and
populate the fields.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("yourtablename", dbOpenDynaset)
For i = <suitable loop terms>
rs.AddNew
rs!thisfield = arrayname(i,0)
rs!thatfield = arrayname(i,1)
rs!theotherfield = arrayname(i,2)
rs.Update
next i

John W. Vinson [MVP]

 

[John Nurick]

Hi Old Guy,

Are you certain you need to use an array? Very often it's possible to
express the manipulation in SQL and implement it as a query. Ideally you
wouldn't even store the manipulated data in a table, but generated it on
the fly.

If the array is essential, to write the data to the other table you do
something like this. Ar() is your array:

Dim j As Long
Dim f As Long
Dim rsR as DAO.Recordset
Set rsR = CurrentDB.OpenRecordset("OtherTable")

With rsR
For j = 0 to UBound(Ar,0) 'loop through records
.AddNew
For f = 0 to UBound(Ar,1) 'loop through fields
rsR.Fields(f).Value = Ar(j,f)
Next r
.Update
Next j

.Close
End With

 

[Guest]

Thanks for your suggestion. Upon inital review, it appears this will work
just fine. I'm building a db pgm for my wife's business which involves a
product table, an inventory table, an invoiced table among others and am just
trying to cut down on the number of queries involved in the invoicing and
inventoring procedures. I used to do this type of thing often as a COBOL
programmer years and years ago. Separate inventory and invoicing tables are
necessary as prices/packing/etc changes and I want to keep old data for
comparison purposes. Thanks again for your help.

John

 

[Guest]

Thanks for your suggestion. Upon inital review, it appears this will work
just fine. I'm building a db pgm for my wife's business which involves a
product table, an inventory table, an invoiced table among others and am just
trying to cut down on the number of queries involved in the invoicing and
inventoring procedures. I used to do this type of thing often as a COBOL
programmer years and years ago. Separate inventory and invoicing tables are
necessary as prices/packing/etc changes and I want to keep old data for
comparison purposes. Thanks again for your help.

John

 

[John W. Vinson]

Thanks for your suggestion. Upon inital review, it appears this will work
just fine. I'm building a db pgm for my wife's business which involves a
product table, an inventory table, an invoiced table among others and am just
trying to cut down on the number of queries involved in the invoicing and
inventoring procedures.

umm....?

So in place of one simple query you want to write extensive VBA code to copy
data out of a table into an array, manipulate it somehow, and write it back
into the same or another table? Sounds like false economy to me!

Queries are the lifeblood of any Access application. If you can do this
operation (and I don't know what the operation actually is) in a Query it
would be preferable to do so.

John W. Vinson [MVP]

 

[The Old Guy]

Mr Vinson,

Thanks for your reply and I appreciate the question you posed; using a query
vice using a table. I have managed to be able to extract the data but only
in a method that would make you shudder I'm sure. The problem is this:
Invoiced are entered on a daily basis for products received from several food
suppliers. Since these products are ordered on a daily basis, there are a
lot of records in the invoice table for the same product (i.e. 2% milk for
ex). The program executes an inventory program at the end of the month,
looking at only the products received in the last 3 months. Since there are
quite a few records for the same product during that time period, I wanted to
sort the inventory data by product, then run through the data, selecting the
first instance of the product and then cycling through until a new product is
encountered. I then use the resulting records for use by the inventory data.
issues of which price to use, etc., have been hammered out to the
satisfaction of all parties involved. If you have any suggestions on how to
extract the data using a query, I would be extremely ecstatic.

Thanks in advance for any advice you may have. I realize it has been
several months since you answered but I hope you are still around.

John (the old guy)

 

[John W. Vinson]

Mr Vinson,

Thanks for your reply and I appreciate the question you posed; using a query
vice using a table. I have managed to be able to extract the data but only
in a method that would make you shudder I'm sure. The problem is this:
Invoiced are entered on a daily basis for products received from several food
suppliers. Since these products are ordered on a daily basis, there are a
lot of records in the invoice table for the same product (i.e. 2% milk for
ex). The program executes an inventory program at the end of the month,
looking at only the products received in the last 3 months. Since there are
quite a few records for the same product during that time period, I wanted to
sort the inventory data by product, then run through the data, selecting the
first instance of the product and then cycling through until a new product is
encountered. I then use the resulting records for use by the inventory data.
issues of which price to use, etc., have been hammered out to the
satisfaction of all parties involved. If you have any suggestions on how to
extract the data using a query, I would be extremely ecstatic.

Thanks in advance for any advice you may have. I realize it has been
several months since you answered but I hope you are still around.

Well, I'm still around, getting older myself... <g>

Without knowing anything about the actual structure of your tables it's hard
to be specific, but no looping and no code are necessary for what you want,
assuming at least some level of normalization in the table design.

You can create a Query selecting the earliest instance of each product within
a time range, by using a subquery. If you could post the tablename and
relevant fieldnames I suspect we could get you a query that would do this with
no VBA code or arrays at all.

 

[The Old Guy]

Greetings,
Thanks for responding. Here is the info you requested.
table name: MasterInvoiceTbl

field names:
DateReceived, ProductNumber, ProductName, VendorNbrID, SoldById,
MasterCaseUnits, MasterCaseDescID, FscCategoryID, FscDepartmentID,
UsfCategoryID, ItemSize, CasePrice, QtyReceived.

The fields ending in ID are linked to the appropriate description tables for
each product. I hope this makes sense.

Thanks.
John

 

[John W. Vinson]

Greetings,
Thanks for responding. Here is the info you requested.
table name: MasterInvoiceTbl

field names:
DateReceived, ProductNumber, ProductName, VendorNbrID, SoldById,
MasterCaseUnits, MasterCaseDescID, FscCategoryID, FscDepartmentID,
UsfCategoryID, ItemSize, CasePrice, QtyReceived.

The fields ending in ID are linked to the appropriate description tables for
each product. I hope this makes sense.

Does the table have a primary key??? Is there any way to uniquely identify a
single record? It's not obvious that the table is properly normalized: is
there a separate product table?

Try this tentative solution: first create a query

SELECT ProductNumber, Min([DateReceived]) AS Earliest
FROM MasterInvoiceTbl
GROUP BY ProductNumber
WHERE DateReceived >= [Enter start date:] AND DateReceived < DateAdd("d", 1,
[Enter end date:]);

Save this as qryProductEarliest.

Then create a second query

SELECT A.*
FROM MasterInvoiceTbl AS A INNER JOIN qryProductEarliest AS B
ON A.ProductNumber = B.ProductNumber
AND A.DateReceived = B.Earliest;

If there are two or more records for a given product both received on the same
date, you'll get duplicates, but it will certainly narrow your search space.

 

[The Old Guy]

Greetings JOhn,
Ok, I'm back in town now and I want to thank you for replying. Yes, every
table has a unique key. I use the unique keys from most of the supporting
tables, storing them in the Master tables. I'm going to catch some shut-eye
and then try your suggestion. Later..ZZZZZZZZ

John

Greetings,
Thanks for responding. Here is the info you requested.
table name: MasterInvoiceTbl

field names:
DateReceived, ProductNumber, ProductName, VendorNbrID, SoldById,
MasterCaseUnits, MasterCaseDescID, FscCategoryID, FscDepartmentID,
UsfCategoryID, ItemSize, CasePrice, QtyReceived.

The fields ending in ID are linked to the appropriate description tables for
each product. I hope this makes sense.

Does the table have a primary key??? Is there any way to uniquely identify a
single record? It's not obvious that the table is properly normalized: is
there a separate product table?

Try this tentative solution: first create a query

SELECT ProductNumber, Min([DateReceived]) AS Earliest
FROM MasterInvoiceTbl
GROUP BY ProductNumber
WHERE DateReceived >= [Enter start date:] AND DateReceived < DateAdd("d", 1,
[Enter end date:]);

Save this as qryProductEarliest.

Then create a second query

SELECT A.*
FROM MasterInvoiceTbl AS A INNER JOIN qryProductEarliest AS B
ON A.ProductNumber = B.ProductNumber
AND A.DateReceived = B.Earliest;

If there are two or more records for a given product both received on the same
date, you'll get duplicates, but it will certainly narrow your search space.

 

[The Old Guy]

John,
Thanks for being patient. I'm a regional field service tech (retiring in
two weeks YAY!!) and have been on another call. I'll be glad when it's over
for sure. I tried the code you suggested and it worked as advertised. Your
comments and guidance are appreciated. I'm glad you decided to work with an
'old' man on this. I have decided I need to procure a developers handbook if
I want to continue working this project. Once again, thanks for all your
help!

John (the old guy)

Greetings,
Thanks for responding. Here is the info you requested.
table name: MasterInvoiceTbl

field names:
DateReceived, ProductNumber, ProductName, VendorNbrID, SoldById,
MasterCaseUnits, MasterCaseDescID, FscCategoryID, FscDepartmentID,
UsfCategoryID, ItemSize, CasePrice, QtyReceived.

The fields ending in ID are linked to the appropriate description tables for
each product. I hope this makes sense.

Does the table have a primary key??? Is there any way to uniquely identify a
single record? It's not obvious that the table is properly normalized: is
there a separate product table?

Try this tentative solution: first create a query

SELECT ProductNumber, Min([DateReceived]) AS Earliest
FROM MasterInvoiceTbl
GROUP BY ProductNumber
WHERE DateReceived >= [Enter start date:] AND DateReceived < DateAdd("d", 1,
[Enter end date:]);

Save this as qryProductEarliest.

Then create a second query

SELECT A.*
FROM MasterInvoiceTbl AS A INNER JOIN qryProductEarliest AS B
ON A.ProductNumber = B.ProductNumber
AND A.DateReceived = B.Earliest;

If there are two or more records for a given product both received on the same
date, you'll get duplicates, but it will certainly narrow your search space.