MS Access 2013 Combining two columns from each of 4 recordsets into a single two-dime

[NightGod]

I have four recordsets (rstToP, rstToHP, rstToHS, rstToF) each with three columns (Item, Location, Type). I've used code like

With rstToF
    .MoveLast
    .MoveFirst
    If (.RecordCount) > intRecordCount Then intRecordCount = (.RecordCount)
End With

ReDim varToArray(intRecordCount-1, 7)

to help me count the records and make sure I have the array dimensioned properly. Now what I'm trying to do is move column Item and column Location from each of the four recordsets into the varToArray (with rstToP filling columns 0 and 1, rstToHP filling 2 and 3, etc).

As I'm typing this, I suspect it may be easier to have them fill the rows of the array instead of the columns, if so, that is definitely equally workable from my perspective.

[twinnyfo]

If the fields of your recordsets are identical, then fill the rows. Dimension the array for the total number of rows.

How are you creating your recordsets? If they are from four separate queries, you could save some time by using a UNION query which would combine all the results into one.

[jforbes]

Depending on what you are trying to do, you might want to use the Union as Twinnyfo suggests and select all your records into the RecordSet then use the RecordSet instead of the Array:

SELECT Item, Location, Type
FROM rstToP
UNION ALL
SELECT Item, Location, Type
FROM rstToHP
UNION ALL
SELECT Item, Location, Type
FROM rstToHS
UNION ALL
SELECT Item, Location, Type
FROM rstToF

[NightGod]

They are from four separate queries, but I need them to be lined up abreast, rather than "end-to-end" like a UNION would.

ie:

item1 loc1 item2 loc2 item3 loc3

not

item1 loc1
item2 loc2
item3 loc3


I'm mainly wondering if there is a simple way to add them to the recordset while forcing which rows they each go into. I know if you use varToArray = rstToP.GetRows( 9999), for instance, it would put them all in, but I'm unsure of how to do that for the other three recordsets while forcing the second into rows 2,3 and third into 4,5 and fourth into 6,7.

Maybe I'm just overthinking the whole thing.

[jforbes]

Ah, I see. Using an Array is cool, but if you can use a query to do the work, it should be faster. It might be easier also, depending on what you are up too. =)

You might be able to use a query like this to get things inline:

SELECT rstToP.Item
, rstToP.Location AS  rstToPLocation, rstToP.Type AS  rstToPType
, rstToHP.Location AS  rstToHPLocation, rstToHP.Type AS  rstToHPType
, rstToHS.Location AS  rstToHSLocation, rstToHS.Type AS  rstToHSType
, rstToF.Location AS  rstToFLocation, rstToF.Type AS  rrstToFType
FROM rstToP
LEFT JOIN rstToHP
ON rstToP.Item= rstToHP.Item
LEFT JOIN rstToHS
ON rstToP.Item= rstToHS.Item
LEFT JOIN rstToF
ON rstToP.Item= rstToF.Item

The one thing about this way to note is that rstToP is used as a the base table an the other tables are linked to it. So if the an Item exists in one of the Linked tables, but not rstToP, it wont show up in the result set. To make sure that the query gets every single Item, the query can have a Union of the Items for the base and then link out to all four Tables (there is another way to do this, but it's less interesting):

SELECT Items.Item
, rstToP.Location AS  rstToPLocation, rstToP.Type AS  rstToPType
, rstToHP.Location AS  rstToHPLocation, rstToHP.Type AS  rstToHPType
, rstToHS.Location AS  rstToHSLocation, rstToHS.Type AS  rstToHSType
, rstToF.Location AS  rstToFLocation, rstToF.Type AS  rrstToFType
FROM 
(SELECT Item  FROM rstToP
 UNION SELECT Item FROM rstToHP
 UNION SELECT Item FROM rstToHS
 UNION SELECT Item FROM rstToF
) AS Items
LEFT JOIN rstToP
ON Items.Item= rstToP.Item
LEFT JOIN rstToHP
ON Items.Item= rstToHP.Item
LEFT JOIN rstToHS
ON Items.Item= rstToHS.Item
LEFT JOIN rstToF
ON Items.Item= rstToF.Item

I didn't test these out, and did a lot of copy/paste and replacement magic, so it might not work right out of the box.

Good luck

[NightGod]

Thanks for the help! I actually was looking to use an array because I can reference cells out of order a lot easier that way (for instance, I might need to reference the first three from rstToP and then get the first in the list from rstToHS, go back and get the fourth from rstToP and then get the first two from rstToF), but I think I'm just going to stick with using four recordsets and using MoveNext to go through them, since I'll only ever have to move forward through the list.

Not as interesting as building the recordsets into an array and traversing it, but I'm running up against a deadline and at this point I'll take functional and possibly go back for interesting later (read: I'll never go back and do the interesting way ;) )