horizontal to verticle

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

Guest

When users enter data on a form the data is stored in a table in a horizontal
format. 1 row per user per month. All the quantities are in horizontal
individual columns (test a, test b, etc).

I need to flip the data so that it becomes verticle. User Name, Month, Test
Name and Quantity. (Test Name will contain the names of all Tests (test a,
test b, etc).

Anyone know a way to flip it?
 
Thanks for responding.
However, I have far too many columns for tests (80-90 tests).
Can you suggest anything else?
 
Your problem is a result of your tables not being designed correctly. This
might be a good time to redesign the tables and your problem will go away.

TblUser
UserID
UserFirstName
UserlastName

TblTest
TestID
TestName

TblTestResult
TestResultID
TestID
UserID
Quantity

Also note that "Month" is a reserved word in Access and should not be used
as a field name. Sooner or later you will encounter a problem using "Month"
as a field name.
 
I guess I'm being dense, but I don't understand how I can have fields listed
on the form for each test where the user will only enter the quantity that
will populated based on what you said below.

Example:
field name CBC and quantity field
field name BUN and quantity field

The way it works now is that the quantity populates in the column called BUN
(which ultimately is a problem). Since so many test names are very similar I
don't want to use a drop down for test names.

What am I missing?
 
Create a query based on TblTest and TblTestResult. Join TestID in both
tables. In the query window double click on the join line. In the dialog
that appears, change the join type to the one that says something like
"include all values of TblTest and only the matching values in
TblTestResult". Pull down the fields TestName from TblTest and UserID and
Quantity from TblTestResult. Use the form wizard to create a continuous form
based on this query. You should end up with a list of all tests in TblTest
with a place to enter Quantity and UserID next to each test.

Steve
PC Datasheet
 
Back
Top