Exporting data from Access to Excel in a particular order.

  • Thread starter Thread starter JDB
  • Start date Start date
J

JDB

I have currently set up 3 tables on my Access database each asking the user
to prompt particular data (weight, temperature etc) pertaining to each group
(group 1, group2, etc). I am trying to export the inputted data into Excel so
that each of the data is automatically combined and listed under each group #
without repeating the group # per table created in Access. How is there a
way to do that? Ideally, it would be great if I could have all the data
listed across each row, with each row defining a particular group # and its
respective data all from each of the 3 tables. I would really appreciate the
help. Thanks!
 
Can you write a single query that will provide the fields in the order and
in a single record that you want? If yes, export that query to EXCEL.
 
I am not familiar with writing queries on Access; I am fairly new to this
software. If you would please explain how I would go about writing one, that
would be great. Thank you.
 
You'll need to describe in detail the tables' structures: table names,
field names, datatypes of the fields, which fields are the ones that link
tables to each other, etc. Then we can assist in writing a query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Hi Mr. Snell,

I have three tables total. They are labeled Blood Draw Log, OMNI Protocol
Form, and Overground Propulsion Protocol. All of these tables ask for Subject
ID and this is the field name that links the tables to each other.
In the Blood Draw Log, the field names are:
Click to expand...
Subject ID (text)
BL_Date (date/time)
BL_TimeC (date/time)
BL_TimeP (date/time)
BL_Init (text)
In the OMNI Protocol Form, there are a total of 73 field names in the format which follows:
Click to expand...
Subject ID (text)
1DYOMB (number) & etc [72 of these set as ‘number’ datatype]
In the third table, Overground Propulsion Protocol, there are a total of 52 field names which is listed as follows:
Click to expand...
Subject ID (text)
1MaxST (number)
180MaxST (number)
1OG_P1L1 (number) & etc [51 of these set as ‘number’ datatype]

I appreciate your time and help in this. Thank you.

Regards,
JDB
 
I apologize for my delay in replying -- work has kept me busy.

OK, now that you've told us the data table structure, now show example
results that you want to see in the EXCEL worksheet, using the field and
table names. Show example data to help us see how you want to combine data
(per your original post).
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





JDB said:
Hi Mr. Snell,

I have three tables total. They are labeled Blood Draw Log, OMNI Protocol
Form, and Overground Propulsion Protocol. All of these tables ask for
Subject
ID and this is the field name that links the tables to each other.
In the Blood Draw Log, the field names are:
Click to expand...
Subject ID (text)
BL_Date (date/time)
BL_TimeC (date/time)
BL_TimeP (date/time)
BL_Init (text)
In the OMNI Protocol Form, there are a total of 73 field names in the
format which follows:
Click to expand...
Subject ID (text)
1DYOMB (number) & etc [72 of these set as 'number' datatype]
In the third table, Overground Propulsion Protocol, there are a total of
52 field names which is listed as follows:
Click to expand...
Subject ID (text)
1MaxST (number)
180MaxST (number)
1OG_P1L1 (number) & etc [51 of these set as 'number' datatype]

I appreciate your time and help in this. Thank you.

Regards,
JDB
Click to expand...
 
Hi Mr. Snell,

That's fine. Although it's difficult to show the format, I'll give you a
general picture as to how the data should be organized in Excel.

Column A (Subject ID #, number of rows dependent on the number subjects
inputted)
Column B - Column E (inclusive, all variables from Blood Draw Log Variables)

Column F - Column BY (inclusive, all variables from OMNI Protocol)
Column BZ - Column DN (inclusive, all variables from Overground Propulsion)

Basically, the three tables' variables will be placed side by side in the
Excel spreadsheet with each row corresponding to a particular Subject ID,
which would be listed in Column A. All I know is that the three tables are
linked to a particular subject ID variable that is present in the three
tables and all the variables from each table corresponding to a particular
Subject ID will be listed in the same row in the columns listed above. I hope
that makes it more clear.

Thanks for your time.

Regards,
JDB
 
My first impression of your data setup is that it is not normalized; you're
storing data information in field names (1, 2, 3, etc.). If you had a
normalized structure, other data operations and queries would be easier to
do.

For your specific question, you need to put all three tables into your
query, and join them on SubjectID. Meaning, join [Blood Draw Log].SubjectID
to [OMNI Protocol Form].SubjectID, and join [Blood Draw Log].SubjectID to
[Overground Propulsion Protocol].SubjectID.

Then you simply add the fields from each table to your query's output. Put
the fields in the order that you want them to appear in the EXCEL worksheet.

The SQL statement would have this type of structure (note that I've left out
most of the fields that are from the same tables):

SELECT [Blood Draw Log].Subject ID, [Blood Draw Log].BL_Date,
[Blood Draw Log].BL_TimeC, [Blood Draw Log].BL_TimeP,
[Blood Draw Log].BL_Init,
[OMNI Protocol Form].[1DYOMB], [OMNI Protocol Form].[2DYOMB],
etc.
[Overground Propulsion Protocol].[1MaxST],
[Overground Propulsion Protocol].[180MaxST],
[Overground Propulsion Protocol].[1OG_P1L1],
[Overground Propulsion Protocol].[2MaxST],
[Overground Propulsion Protocol].[280MaxST],
[Overground Propulsion Protocol].[2OG_P1L1],
etc.
 
Sorry pressed Enter key too soon: Here is "full" SQL Statement:

SELECT [Blood Draw Log].SubjectID, [Blood Draw Log].BL_Date,
[Blood Draw Log].BL_TimeC, [Blood Draw Log].BL_TimeP,
[Blood Draw Log].BL_Init,
[OMNI Protocol Form].[1DYOMB], [OMNI Protocol Form].[2DYOMB],
etc.
[Overground Propulsion Protocol].[1MaxST],
[Overground Propulsion Protocol].[180MaxST],
[Overground Propulsion Protocol].[1OG_P1L1],
[Overground Propulsion Protocol].[2MaxST],
[Overground Propulsion Protocol].[280MaxST],
[Overground Propulsion Protocol].[2OG_P1L1],
etc.
FROM ([Blood Draw Log] INNER JOIN [OMNI Protocol Form]
ON [Blood Draw Log].SubjectID =
[OMNI Protocol Form].SubjectID) INNER JOIN
[Overground Propulsion Protocol] ON
[Blood Draw Log].SubjectID =
[Overground Propulsion Protocol].SubjectID;
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Ken Snell said:
My first impression of your data setup is that it is not normalized;
you're storing data information in field names (1, 2, 3, etc.). If you had
a normalized structure, other data operations and queries would be easier
to do.

For your specific question, you need to put all three tables into your
query, and join them on SubjectID. Meaning, join [Blood Draw
Log].SubjectID to [OMNI Protocol Form].SubjectID, and join [Blood Draw
Log].SubjectID to [Overground Propulsion Protocol].SubjectID.

Then you simply add the fields from each table to your query's output. Put
the fields in the order that you want them to appear in the EXCEL
worksheet.

The SQL statement would have this type of structure (note that I've left
out most of the fields that are from the same tables):

SELECT [Blood Draw Log].Subject ID, [Blood Draw Log].BL_Date,
[Blood Draw Log].BL_TimeC, [Blood Draw Log].BL_TimeP,
[Blood Draw Log].BL_Init,
[OMNI Protocol Form].[1DYOMB], [OMNI Protocol Form].[2DYOMB],
etc.
[Overground Propulsion Protocol].[1MaxST],
[Overground Propulsion Protocol].[180MaxST],
[Overground Propulsion Protocol].[1OG_P1L1],
[Overground Propulsion Protocol].[2MaxST],
[Overground Propulsion Protocol].[280MaxST],
[Overground Propulsion Protocol].[2OG_P1L1],
etc.


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



JDB said:
Hi Mr. Snell,

That's fine. Although it's difficult to show the format, I'll give you a
general picture as to how the data should be organized in Excel.

Column A (Subject ID #, number of rows dependent on the number subjects
inputted)
Column B - Column E (inclusive, all variables from Blood Draw Log
Variables)

Column F - Column BY (inclusive, all variables from OMNI Protocol)
Column BZ - Column DN (inclusive, all variables from Overground
Propulsion)

Basically, the three tables' variables will be placed side by side in the
Excel spreadsheet with each row corresponding to a particular Subject ID,
which would be listed in Column A. All I know is that the three tables
are
linked to a particular subject ID variable that is present in the three
tables and all the variables from each table corresponding to a
particular
Subject ID will be listed in the same row in the columns listed above. I
hope
that makes it more clear.

Thanks for your time.

Regards,
JDB
Click to expand...
Click to expand...
 
Back
Top