My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Help w/ Code to Routinely normalize an Access table
|
|
 Jan 4 2008, 12:08 AM
Post
#1
|
|
|
New Member Posts: 3  |
I know that this might be easy to code, but I am a beginner. If anyone could point me in the right direction, I'd be extraordinarily grateful. I'll have to run this code often. I will receive updated Access tables quarterly.
Below is the Access Database table that I must normalize -- it has only 3 fields. ( Sample data below.) The tricky thing is TransNo field. The value "Coupon" may appear as a value more than once, or not appear at all. (I've never accounted more than 5 "Coupon" values for a single TransNo.) The TransNo is Number; FName and FData are Text. CODE TransNo FName FData -123 FieldA 197182 -123 FieldB 4333395 -123 FieldC -123 FieldD $20,113.00 -123 Coupon X3331 -123 OpDate 6/12/07 -234 FieldA 4000000853 -234 FieldB 611327 -234 FieldC -234 FieldD $113.00 -234 Coupon XL4385551 -234 Coupon XL4385550 -234 OpDate 1/1/07 2222 FieldA 102003004326 2222 FieldB 71924333458 2222 FieldC 2222 FieldD $17.00 2222 OpDate 1/1/06 Thanks for your help! I will be so grateful if I can get this project started. |
 |
 
|
|
Jan 4 2008, 01:08 AM
Post
#2
|
|
|
UtterAccess VIP Posts: 18,396 From: Oklahoma City, Oklahoma |
Welcome to Utter Access forums!
QUOTE Below is the Access Database table that I must normalize ... It appears to already be normalized. The structure looks fine to me. I am not sure I would change it. What issues are you having? |
|
|
|
|
Jan 4 2008, 09:36 AM
Post
#3
|
|
|
New Member Posts: 3 |
Thanks for replying!
The reason I want to normalize it , or rather , change it, is because running reports in Access with it from turns out to be extremely difficult for us non-programmer types in the office. There are over 50,000 Transaction Numbers, and an average of 9 pieces of data for each trans No. So another reason is that the table has become too huge to manage. Is it possible? Is it possible to write a code in VB or SQL that would set it up horizontally, so there is one row of data for each transaction? CODE Transaction No Field A FieldB FieldC FieldD Coupon1 Coupon2 Coupon3 Coupon4 OpDate -123 97182 4333395 20,113.00 etc, etc . . I'm a novice coder. I'm learning. If you could give me hint it would be great. I'm banging my head on the wall and I have such tight deadlines there's not a lot of time to study my VB. |
|
|
|
|
Jan 4 2008, 10:53 AM
Post
#4
|
|
|
UtterAccess VIP Posts: 18,396 From: Oklahoma City, Oklahoma |
What you are wanting to do is de-normalized the properly normalized data. This is a common need for reports. In a database, you store the data in normalized tables, then use queries to de-normalized the data into the form you want for your reports. In a relational database, the table structure usually does not match the layout of your reports. If it did, tthen you could only have one report layout format (like in Word or Excel) which would not be good in a database.
I normally use a Crosstab query to de-normalized for the type of report you want. This example may help: Dynamic report form a crosstab query |
|
|
|
|
Jan 4 2008, 01:19 PM
Post
#5
|
|
|
New Member Posts: 3 |
Thanks for your quick reply.
Even though the table is Normalized now, I guess we are not experienced with Access enough. My colleagues and I can't figure out how to use the table, probably because we've never used Cross Tab queries. I will study them and try to figure this out -- thanks for the direction! For now, though, if the table were in the 'datasheet' format, w/ one row (record) for each trans. no., we'd be able to use it - dump it in excel or make queries, sort it, filter it, print etc. We are just not advanced enough here to use this table as it is. Do you know of someone we can hire to write simple modules like this? I'd like to study VB to figure this out, but it will take me a long time to get to that level. I am a beginner . I'll keep trying though. thanks, Sandra |
|
|
|
|
Jan 4 2008, 11:38 PM
Post
#6
|
|
|
UtterAccess VIP Posts: 18,396 From: Oklahoma City, Oklahoma |
There is a query wizard to help you make a Crosstab query.
Also in Excel, you could import the data and make a pivot table to get basically the same thing. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 02:57 AM |