Stepping through data in Access

stumped · January 19th, 2005, 05:53 PM

I have never used VBA in Access, but I am an advid user of the tools available within Access. I have a new problem that I do not know how to deal with and think VBA coding is the best solution to my problem. I have over 980,000 records that I need to go through and compare two columns to capture information in a third column to build a new table.
A sample of the data and what I am trying to get the end product to look like is contained below.

Sample Data:

[u]Meats Cook Time</u>
Chicken Broled 35 min
Chicken Fried 7 min
Chicken Baked 30 min
Fish Broled 25 min
Fish Baked 20 min
Fish Fried 6 min
Fish Stew 65 min
Beef Stew 75 min
Beef BBQ 40 min

Output in new table:

BBQ Baked Broiled Fried Stew
Beef 40min 0min 0min 0min 40min
Chicken 0min 30min 35min 7min 0min
Fish 0min 20min 25min 6min 65min

If someone can tell me how I can use VBA code to do this I would greatly appreciate the help. In my mind I would use some kinda loop iterate through the data with some if statements to validate the columns. My problem is I do not know how to write VBA code but I can write Java code. Any help or recommendation toward a reference document would be greatly appreciated.
Thanks,
Stupmed

Bob Bedell · January 20th, 2005, 03:19 AM

Actually you don't need code at all, just a cross-tab query.

To get the output you're looking for, though, you'll need to include all your "0 min" Time values where appropriate. You can easily do that by making "0 min" the default value of your Time field.

So your data values would look like (table output to a text file):

"Chicken","Broiled","35 min"
"Chicken","Fried","7 min"
"Chicken","Baked","30 min"
"Fish","Broiled","25 min"
"Fish","Baked","20 min"
"Fish","Fried","6 min"
"Fish","Stew","65 min"
"Beef","Stew","75 min"
"Beef","BBQ","40 min"
"Beef","Baked","0 min"
"Beef","Broiled","0 min"
"Beef","Fried","0 min"
"Chicken","BBQ","0 min"
"Chicken","Stew","0 min"
"Fish","BBQ","0 min"

The SQL syntax for the cross-tab query is:

TRANSFORM First(tblData.Time) AS FirstOfTime
SELECT tblData.Meats
FROM tblData
GROUP BY tblData.Meats
PIVOT tblData.Cook;

You can just paste this SQL in the QBE window (open a new query in design view and select "SQL View" from the View menu option.

The output looks like:

Code:

Meats     Baked     BBQ     Broiled     Fried     Stew
Beef      0 min     40 min  0 min       0 min     75 min  
Chicken   30 min    0 miin  35 min      7 min     0 min
Fish      20 min    0 min   25 min      6 min     65 min

HTH,

Bob