Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

deriving a 'normalized' form 1

Status
Not open for further replies.
uscitizen

uscitizen

Technical User
Jan 17, 2003
672
US
I have a table that's organized like this:


Id Cycle Day Test_Date Test_A Test_B Test_C
A 1 1 mm/dd/yy # # #
A 1 2 mm/dd/yy # # #
A 1 3 mm/dd/yy # # #
B 1 1 mm/dd/yy # # #
C 1 1 mm/dd/yy # # #
C 1 2 mm/dd/yy # # #
. . . . . . .
. . . . . . .
. . . . . . .

The point of the above being that for any Id there will be an unequal number of visits (as represented by rows having Dates and Test_A Test_B Test_C scores), since the person having Id whatever cannot be counted on to arrive as many times as his peers, however all three test values will be recorded when Id arrives on any given Test_Date at Cycle something and Day (within Cycle) something.

What I'd like is to have the following:

Id Visit_1A Visit_2A Visit_3A Visit_3A Visit_4A .... Visit_400A Visit_1B Visit_2B Visit_3B Visit_3B Visit_4B .... Visit_400B Visit_1C Visit_2C Visit_3C Visit_3C Visit_4C .... Visit_400C

in a table based on the above.

We know that we're never going to know what the upper limit of visits (rows) will be associated w/ any given ID so (being generous) we'll say 400 (it could be any number).

What I'm fixing to do is create a form which has a descriptive label (a column of label controls) running top to bottom on the left side with the column heading titled 'Test Name'; the first row under the column titled 'Test Name' would read 'Test_A', the second would read 'Test_B', the third and final one would read 'Test_C'. To the right of this, will be up to whatever the maximum number of columns we finally decide upon beginning with one called 'Day 1 Results', followed by one called 'Day 2 Results',....up to the last one called 'Day 400 Results'. Under the 'Day 1 Results', will appear top to bottom'Visit_1A', 'Visit_1B', 'Visit_1C' controls; beneath the one called 'Day 2 Results' the form will have 'Visit_2A, 'Visit_2B' and 'Visit_2C controls and so forth and so on.

Is this doable in Access 2000?
 
Sort by date Sort by votes
Hi,

What are these lable controls? This sounds like a crosstab query

EXCEPT

your table is NOT NORMALIZED!

To normalize your data...
Id
Cycle
Visit
TestDate
TestID
TestValue

I used Visit instead of Day since Day has the connotation that it's related to TestDate.

?

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
I'm sorry if my description was not clear.....

Assuming the structure of the table (in the 1st part) of my query is clear, the form that supports it, has fields (controls) for each of the columns in the underlying table; therefore, a user sees a data entry form which holds the Id, Cycle, Day, Specimen Date, and Test scores. You are correct, Day is related to Cycle; there might be 4 Cycles, each one requiring the Id to show up for 17 days. So, if the Id satisfied the requirements, there would be 68 rows worth of data for that Id and in the transformed table I am trying to generate there would be one row for this Id, with Test_A1, Test_B1, Test_C1,......Test_A68, Test_B68, Test_C68 having entries in them. [Incidentally, I guess I neglected to add that there'd be Specimen_Date_1,Specimen_Date_2, Specimen_Date_3,.....Specimen_Date_68 entries as well]

I may have violated 'sacred ground' when i stated the subject of my query to involve 'normalizing a form' -- in retrospect that may have even been a bit of a contradiction in terms. with that said, and to rephrase the goal in mind....

what my users want (or at least think they want) is the ability to review the data collected for any given Id by scrolling horizontally across the screen when in form view. there's another product they use that lets them see data from one Id gathered over time as if (to draw an analogy with ms access data tables) there were a column in a table that had Test_Names as its label and was 'frozen' or static so it could not move as you dragged the horizontal slider. Every other column would be headed by the actual date the specimen was collected (or perhaps just an incremental visit counter: Visit_1, Visit_2,.....,Visit_68) below which the user would see the lab values for the Id's tests.

Make more sense.
 
Upvote 0 Downvote
1. To build a database application that will be useable, you must begin with a NORMALIZED table. You will then be able to REPORT and ANALYZE the data MUCH more easily.

Your table should look something like this...
Code: 
id	cycle	visit	date    	testname	testval
A 	1    	1    	mm/dd/yy	Test_A  	1      
A 	1    	1    	mm/dd/yy	Test_B  	2      
A 	1    	1    	mm/dd/yy	Test_C  	3      
A 	1    	2    	mm/dd/yy	Test_A  	4      
A 	1    	2    	mm/dd/yy	Test_B  	5      
A 	1    	2    	mm/dd/yy	Test_C  	6      
A 	1    	3    	mm/dd/yy	Test_A  	2      
A 	1    	3    	mm/dd/yy	Test_B  	3      
A 	1    	3    	mm/dd/yy	Test_C  	4      
B 	1    	1    	mm/dd/yy	Test_A  	5      
B 	1    	1    	mm/dd/yy	Test_B  	6      
B 	1    	1    	mm/dd/yy	Test_C  	7      
C 	1    	1    	mm/dd/yy	Test_A  	5      
C 	1    	1    	mm/dd/yy	Test_B  	6      
C 	1    	1    	mm/dd/yy	Test_C  	7      
C 	1    	2    	mm/dd/yy	Test_A  	8      
C 	1    	2    	mm/dd/yy	Test_B  	9      
C 	1    	2    	mm/dd/yy	Test_C  	8
From a table like this, you can do a crosstab query that gives you a result like this...
Code: 
id	cycle	visit	date    	Test_A	Test_B	Test_C	Grand Total
A 	1    	1    	mm/dd/yy	1     	2     	3     	6       
  	     	2    	mm/dd/yy	4     	5     	6     	15       
  	     	3    	mm/dd/yy	2     	3     	4     	9       
B 	1    	1    	mm/dd/yy	5     	6     	7     	18       
C 	1    	1    	mm/dd/yy	5     	6     	7     	18       
  	     	2    	mm/dd/yy	8     	9     	8     	25       
Grand Total			        	25    	31    	35    	91
or like this with just a few keystrokes...
Code: 
id	A			

Count of testval	cycle   	visit   	date    	
         	1       	        	        	Grand Total
         	1       	2       	3       	
testname        	mm/dd/yy	mm/dd/yy	mm/dd/yy	
Test_A          	1       	1       	1       	3
Test_B          	1       	1       	1       	3
Test_C          	1       	1       	1       	3
Grand Total     	3       	3       	3       	9
I reformatted your data into a NORMALIZED table using the pivottable wizard, and created these 2 reports all in about 15 minutes, between drinking my coffee and eating a piece of Entiman's Pecan Danish Ring -- ALL my favorites!

Think about it!

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
okay, let's see if this makes sense. i'll rehash it and let me know if it's what you intended.

first, here's a given, my data are going to be collected on a form which has an underlying table with the following structure:

Id Cycle Day Test_Date Test_A Test_B Test_C
A 1 1 mm/dd/yy # # #
A 1 2 mm/dd/yy # # #
A 1 3 mm/dd/yy # # #
B 1 1 mm/dd/yy # # #
C 1 1 mm/dd/yy # # #
C 1 2 mm/dd/yy # # #
. . . . . . .
. . . . . . .
. . . . . . .

then, you're saying normalize the above so that when viewed, the tabled data looks like this:

id cycle visit date testname testval
A 1 1 mm/dd/yy Test_A 1
A 1 1 mm/dd/yy Test_B 2
A 1 1 mm/dd/yy Test_C 3
A 1 2 mm/dd/yy Test_A 4
A 1 2 mm/dd/yy Test_B 5
A 1 2 mm/dd/yy Test_C 6
A 1 3 mm/dd/yy Test_A 2
A 1 3 mm/dd/yy Test_B 3
A 1 3 mm/dd/yy Test_C 4
B 1 1 mm/dd/yy Test_A 5
B 1 1 mm/dd/yy Test_B 6
B 1 1 mm/dd/yy Test_C 7
C 1 1 mm/dd/yy Test_A 5
C 1 1 mm/dd/yy Test_B 6
C 1 1 mm/dd/yy Test_C 7
C 1 2 mm/dd/yy Test_A 8
C 1 2 mm/dd/yy Test_B 9
C 1 2 mm/dd/yy Test_C 8

which brings me to question number 1: how?

assuming we get past question '1', we can do a xtab query to give results that can have several 'looks'; the first one results in the following display:
id cycle visit date Test_A Test_B Test_C Grand Total
A 1 1 mm/dd/yy 1 2 3 6
2 mm/dd/yy 4 5 6 15
3 mm/dd/yy 2 3 4 9
B 1 1 mm/dd/yy 5 6 7 18
C 1 1 mm/dd/yy 5 6 7 18
2 mm/dd/yy 8 9 8 25
Grand Total 25 31 35 91

the second one results in the folllowing:

testname mm/dd/yy mm/dd/yy mm/dd/yy
Test_A 1 1 1 3
Test_B 1 1 1 3
Test_C 1 1 1 3
Grand Total 3 3 3 9

the last one just above looks tantalizingly like the one i requested (even though i know not how to create it) until i look a little closer, when it seems as though the entries in the 'cells' are not the values of the Test_A or Test_B or Test_C at each of the visit dates which are symbolized by the generic placeholder 'mm/dd/yy' but the number of times that there was a test value -- am i wrong?

focusing on the 1st x-tab query, it looks quite familiar....does it not seem to resemble the very structure my data are being collected in?

perhaps i'm not understanding something, perhaps i need some hot coffee and a bite of cake to warm up the old neural wetware?
 
Upvote 0 Downvote
Dear uscitizen,

You are naking the mistake that almost every user makes in thinking about data in terms of reports.

A data entry form is much like a report. IT would be unreasonable, for instance, to require the user to enter the test data in the NORMALIZED table format. Data entry would be much easier indeed in the form that you defined.

BUT

that is NOT the form in which the data ought to be STORED. So the data capturing program, maps the data form the form to the table.

The beauty of the NORMALIZED table is that data analysis and reporting is greatly simplified and data maintenence is reduced than storing the data otherwise, in most cases.

The 2 reports I generated are simple craosstab queries (I actually did it all in Excel using a PivotTable Wizard)

The query design grid is GUI and you just drag fields in and out and reposition etc.

The VALUES can be the SUM or COUNT or AVERAGE or MAX or MIN etc with the clock of a button. VERY SIMPLE!

You can report on 1 Id or ALL id's or a selection if Id's.

Lots of flexibility! :)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Hi,

After skimming through this, TestA, B and C should be totally different records (as stated in the very first response).
If this had been done, then the rest of the responses wouldn't have been needed.

Normalize the database design.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk
 
Upvote 0 Downvote
hi -

so it looks like you differentiate between data capture and data storage. i am inferring that the you're proposing some 'mechanism' or 'procedure' is created that re-organizes the way the data are stored as they are being captured into this 'normalized' structure which you laboriously typed.

my problem with what you're telling me is that, even if i had used the pivot table capability you are, is that you're talking about something that aggregates the test values and outputs summary data (in the example you provided, the display 'matrix' lists counts

testname mm/dd/yy mm/dd/yy mm/dd/yy
Test_A 1 1 1 3
Test_B 1 1 1 3
Test_C 1 1 1 3
Grand Total 3 3 3 9

which is all very well and good if you want COUNTS (or to use your words, SUMs, AVERAGEs, MINima, MAXima, etc.) but what I want is THE ACTUAL VALUE OF Test_A at mm/dd/yy (by the way, I hope this isn't responsible for this having taken a direction other than was intended, but 'mm/dd/yy' is just a shorthand way of not actually taking the trouble to have used read dates when i set out to illustrate the way the data are being recorded; in other words, 'mm/dd/yy' isn't a character literal value in the date field)

help me out here, how much coffee cake does it take to understand why your suggestion doesn't seem to get at the solution this is looking for?






 
Upvote 0 Downvote
I laboriously typed NOTHING! I have not even broke a sweat this AM. I have leisurely pointed and clicked, using the PivotTable Wizard, to create the NORMALIZED table format. I actually spent MORE TIME formatting the output for copying the data to TT so everything lines up nice, than I did genning the table and reports.

Supprise! The SUM of a single value...

is drum roll...

THE VALUE!

BUT...

the time will come, if you're not just dabbling in this sort of thing, when the BOSS will say, "Can we get the Sum of..." or "... Average of..." or "..sorted by..." or "...across..." and if your data is NOT normalized, you've got ALOT of scrambling to do. Me, I can, with a high degree of certainty, respond within 5 minutes (sans prettyin' it up)

Hey, I'm not blowin' smoke, here. Darrylles already responded in confirmation. If any of my collegues here respond, they will confirm.

Here's some industry references...


Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
I have seen all kinds and degrees of "horror stories" in big corporations as well as small companies that are related to gobs and gobs of data being stored in all sorts of non-normalized form, and the question is ALWAYS...

How can I get my data into this other form

or

How can I extract this information

...and I end up being like the doctor whose patient wants a solution, but the way to get there is PAINFUL and LONG!

There's a bit of PAIN in properly designing a system. It takes more time and effort than slapping a report format in a table but it's WELL WORTH the effort!

Of course, if you could care less, take the Burger King approch! ;-)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Hey fellow uscitizen,

Thanx for the red star, but that's not why I so doggedly pursued this thread. I've already got my share.

I am on a crusade of sorts on this very issue.

I really would like to see you come back some time later and tell us that you got some really good Tek Tips along the way that resulted in you producing a really good product.

Mean time, feel free to post your questions and hopefully, we all will be able to provide helpful/expert posts. :)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
421
strongm
strongm
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
486
sal21
sal21
remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
584
Andrzejek
Andrzejek
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
413
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top