Need Assistance with Append Query...Thanks

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

Guest

Hi, I have a table with two fields: HEADING and DATA. The info in the
HEADING field/column repeats itself. The info in the DATA column is
different. That is why the info in the HEADING field repeats...every time a
new name appears in the DATA field, then the same info for the new person is
listed. As an example, in the HEADING field I have DOB, NAME, SCHOOL, REVIEW
DATE. IN the DATA field I have 100 different names with the corresponding
DOB, SCHOOL, and REVIEW DATE for each NAME. I want the HEADING field info to
become multiple column headings and so I created a new table that has four
columns: DOB, NAME, SCHOOL, REVIEW DATE. I am stumped in getting the info
from the DATA field into my new table. Can anyone help me figure this out?
Thanks.
 
Please provide some sample data. I don't know how you can resolve this if
you have more than one "NAME" record in the first table you mention.
 
Andy,

Yep, this is a tricky one. As far as I can see, this would not be
possible with your existing data. You would need to add another field
to the table to identify each "set" of records. Since there are only
100 sets, I would do this manually. Add a new field, number data type,
and then open the table. Do *not* do anything to change the sorting of
the table! Then just go down the list in the new field, enter 1 for the
first 4 records, i.e. corresponding to the DOB, NAME, SCHOOL, and REVIEW
DATE records for the first person, then enter 2 in the new field for the
next 4 records, etc. It will be a bit of a time-consuming process, but
I can't think of another way without getting real complicated. After
this, you will be able to use an Append Query, just based on the new
field and the DATA field where the HEADING field is "NAME", into the new
table, followed by a series of 3 Update Queries to enter the data into
the relevant fields for the corresponding ID in the new field for the
HEADING values of "DOB", "SCHOOL", and "REVIEW DATE".
 
Thanks Duane and Steve. Steve, your solution worked successfuly. 1st, I
created an Append query with it's Unique Values property set toy "Yes". I
then created one update query and copied it multiple times, until I had a
update query for each field I needed to update. I then changed each update
query to reflect the specific field being updated. I then ran the Append
query, and then each of the update queries. The result was I got my data
formatted in a table just like I wanted: each row reflects a different
employee, with the emploiyee attributes being the column (field) headings.
However, one small problem...I made a typo when initially presenting my
problem. I typed 100 and meant 1,000. Oh well, such is life. I just wish I
was paid by the hour!!:):)
Andy
 
Back
Top