Create Loop in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:

Meats Cook Time
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
 
stumped said:
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:

Meats Cook Time
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
Click to expand...

Although I think your sample output has an error at the "Beef /Stew"
intersection, if I understand your intention, this looks like the output
of a crosstab query. If I'm right, there would be no coding required at
all. I suggest you look crosstab queries up in the online help and see
if that's what you're looking for.
 
stumped said:
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:

Meats Cook Time
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
Click to expand...
[...]

You can get this bay using a Crosstab Query. Open the Query Tab in
Access, select New ... Crostab query wizard and select "Meats" for the
row headings and "Cook" for the column headings, and First(TImen) for
the value. Save this query, and use it to create a Make Table query.

BTW with 980000 records: Do you have duplicates in the columns "Meat"
and "Cook"?

HTH
Matthias Kläy
 
Thanks for the help but a crosstab query will not work. I am familiar with
all the basic functions of Access and this function will not work because the
data inside the table is analysed using the sum, avg, first,last, stdv
functions (stats stuff) only. I need to capture Every entry (unique enrty
for Meat but some entries are repeated in Cook column multiple times) from
the two, Meat and Cook, columns and relate that to the time. This is a
matrix that needs to be created by, I believe, stepping through each Meat
entry and matching it with each Cook entry to get the associated time is what
I am looking to do. I just don't know how to get Access to create this
matrix. I tried crosstabs and hlookup and vlookup in Excel and they didi not
work either.
Any and all assistance would be greatly appreciated.
Thanks,
Stupmed twice

Matthias Klaey said:
stumped said:
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:

Meats Cook Time
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
Click to expand...
[...]

You can get this bay using a Crosstab Query. Open the Query Tab in
Access, select New ... Crostab query wizard and select "Meats" for the
row headings and "Cook" for the column headings, and First(TImen) for
the value. Save this query, and use it to create a Make Table query.

BTW with 980000 records: Do you have duplicates in the columns "Meat"
and "Cook"?

HTH
Matthias Kläy
Click to expand...
 
If you don't think a crosstab will work then you should provide the records
that would cause it to not work and how you would expect them to appear in
your result (if not a crosstab).

--
Duane Hookom
MS Access MVP


pootonline said:
Thanks for the help but a crosstab query will not work. I am familiar
with
all the basic functions of Access and this function will not work because
the
data inside the table is analysed using the sum, avg, first,last, stdv
functions (stats stuff) only. I need to capture Every entry (unique enrty
for Meat but some entries are repeated in Cook column multiple times) from
the two, Meat and Cook, columns and relate that to the time. This is a
matrix that needs to be created by, I believe, stepping through each Meat
entry and matching it with each Cook entry to get the associated time is
what
I am looking to do. I just don't know how to get Access to create this
matrix. I tried crosstabs and hlookup and vlookup in Excel and they didi
not
work either.
Any and all assistance would be greatly appreciated.
Thanks,
Stupmed twice

Matthias Klaey said:
stumped said:
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:

Meats Cook Time
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
Click to expand...
[...]

You can get this bay using a Crosstab Query. Open the Query Tab in
Access, select New ... Crostab query wizard and select "Meats" for the
row headings and "Cook" for the column headings, and First(TImen) for
the value. Save this query, and use it to create a Make Table query.

BTW with 980000 records: Do you have duplicates in the columns "Meat"
and "Cook"?

HTH
Matthias Kläy
Click to expand...
Click to expand...
 
Back
Top