Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 19th, 2005, 05:53 PM
Registered User
Join Date: Jan 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stepping through data in Access

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.

Old January 20th, 2005, 03:19 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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:

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ch5 : Form4.php pg 156-8 ... Not stepping iamdaniel BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 2 January 27th, 2006 12:58 PM
SQL Access/ASP.NET data access issue saeta57 SQL Server ASP 1 July 4th, 2004 04:29 PM
SQL Access/ASP.NET data access issue saeta57 Classic ASP Databases 1 July 4th, 2004 03:32 PM
Data access page access problem :) kev_79 Access 0 September 4th, 2003 04:02 PM
stepping on .net raja .NET Web Services 1 July 14th, 2003 02:15 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.