Help with sorting

[Don]

I'm using Access 2000. My database collects lots and lots of data. One
aspect of that is to collect dimensional data from multiple checks during
the day. The way I've done that is to have a field for the date and then
multiple columns to enter the individual readings. When I go to query this
data the columns come up "left to right" or landscape if you think of it
that way. An example would be;
Reading One Reading Two Reading Three Data First Data Second
Data Third
.001 .002 .003 abc
efg lmn
I'd like to see it like this;
Reading One .001
Reading Two .002
Reading Three .003
Data First abc
Data Second efg
Data Third lmn
After I query this data I export it to Excel. Because of all the data
collected I spend hours sorting and lining up data. The only way I know to
do this in Excel is to copy/paste special/transpose. This takes forever.
Is there a quick way to do this?
If I haven't been clear on what I'm looking to do please let me know.
Thanks,
Don...........

[storrboy]

Re: Help with sorting




To get them listed vertically, use a report that is formated that way.

[John]

Re: Help with sorting

From what I understand you would like to have the data in Excel formatted
like you described. A little bit of VBA in Access could do this. You could
create an empty table called tblDestination with 2 text fields
"Names_Of_Field s" and "Value_Of_Field ". Then assign the following code to
for instance a form's pushbutton click event. In this code change tblSource
to the name of the table which holds the original data. This code loops
through your source data, record by record, and creates a new record in the
destination table for every field value. In this example this only happens
for the fields Reading_data and Reading_one, so you should add the fields
you want extra. After filling the destionation table you can easily cut and
paste the table to Excel. This is just rough code without error checking.

Private Sub Knop0_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs2 = db.OpenRecordse t("tblDestinati on")

Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblSource" )
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do Until .EOF

rs2.AddNew
rs2!Names_Of_Fi elds = "Reading_da te"
rs2!Value_Of_Fi eld = rs!Reading_date
rs2.Update

rs2.AddNew
rs2!Names_Of_Fi elds = "Reading_on e"
rs2!Value_of_Fi eld = rs!Reading_one
rs2.Update

.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
Set db2 = Nothing
End Sub

john

"Don" <vze2t273@veriz on.netschreef in bericht
news:F7yIh.3480 $d8.814@trndny0 7...

I'm using Access 2000. My database collects lots and lots of data. One
aspect of that is to collect dimensional data from multiple checks during
the day. The way I've done that is to have a field for the date and then
multiple columns to enter the individual readings. When I go to query
this
data the columns come up "left to right" or landscape if you think of it
that way. An example would be;
Reading One Reading Two Reading Three Data First Data Second
Data Third
.001 .002 .003 abc
efg lmn
I'd like to see it like this;
Reading One .001
Reading Two .002
Reading Three .003
Data First abc
Data Second efg
Data Third lmn
After I query this data I export it to Excel. Because of all the data
collected I spend hours sorting and lining up data. The only way I know
to
do this in Excel is to copy/paste special/transpose. This takes forever.
Is there a quick way to do this?
If I haven't been clear on what I'm looking to do please let me know.
Thanks,
Don...........
>
>

[Bob Quintal]

Re: Help with sorting

"Don" <vze2t273@veriz on.netwrote in
news:F7yIh.3480 $d8.814@trndny0 7:

I'm using Access 2000. My database collects lots and lots of
data. One aspect of that is to collect dimensional data from
multiple checks during the day. The way I've done that is to
have a field for the date and then multiple columns to enter
the individual readings. When I go to query this data the
columns come up "left to right" or landscape if you think of
it that way. An example would be;
Reading One Reading Two Reading Three Data First
Data Second Data Third
.001 .002 .003
abc
efg lmn
I'd like to see it like this;
Reading One .001
Reading Two .002
Reading Three .003
Data First abc
Data Second efg
Data Third lmn
After I query this data I export it to Excel. Because of all
the data collected I spend hours sorting and lining up data.
The only way I know to do this in Excel is to copy/paste
special/transpose. This takes forever. Is there a quick way
to do this? If I haven't been clear on what I'm looking to do
please let me know. Thanks,
Don...........
>
>

I assume you are lining up Data First and Reading One, Data
Second and Reading Two.

You really should think about redesigning the table's structure.
DateAndTime, Data, reading, comments. the date/time field serves
as Primary Key for the table.

A small form with the date field's default value set to =now()
and a button for Add new record should ,make it really quick and
easy.
I set up something like that, with a barcode wedge to read the
Job Number from the part's tag, and a serial port connection to
the gauge. Inspector just adds which point he's measuring

We can easily tell what we measured when and the value we got.

If you also set up some queries to do the statistics, you may
not even need to export the data to Excel.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com