Make Table Query

[Guest]

Apologies if this is a bit basic - I'm rather new to Access.

Am using Access 2003 on XPSP2

I have one table with data that looks a bit like this:

UserID1, Field1, Field2, Field3, Field4 . . .Field36, CreationDate
UserID2, Field1, Field2, Field3, Field4 . . .Field36, CreationDate

I'd like to make a new table which builds up the data from the first table
as follows:

KeyField, UserID1, FieldNo=1, Field1Data, CreationDate
KeyField, UserID1, FieldNo=2, Field2Data, CreationDate
keyField, UserID1, FieldNo=3, Field3Data, CreationDate
..
..
..
keyField, userID1, FieldNo=36, Field36Data, CreationDate
keyField, userID2, FieldNo=1, Field1Data, CreationDate
..
..
keyField, userID2, FieldNo=36, Field36Data, CreationDate

Sequence isnt important - happy for keyfield to be an AutoNum - I can
manipulate the data happily once I get it into the new table - so data can be


I've managed to write a CreateTable query to create the first set of data -
i.e. to take all of the data from FieldNo1 for all users and put this into a
new table.
I've also managed to write an AppendTable query to add the data from the
second Field to that of the first, but figured there MUST be a better way
than to create 36 seperate queries.

I am reasonably familiar with VBA in Excel, but a lot less so in Access -
but will happily have a go!

Hope someone can help.

 

[Tom Lake]

Apologies if this is a bit basic - I'm rather new to Access.

Am using Access 2003 on XPSP2

I have one table with data that looks a bit like this:

UserID1, Field1, Field2, Field3, Field4 . . .Field36, CreationDate
UserID2, Field1, Field2, Field3, Field4 . . .Field36, CreationDate

I'd like to make a new table which builds up the data from the first table
as follows:

KeyField, UserID1, FieldNo=1, Field1Data, CreationDate
KeyField, UserID1, FieldNo=2, Field2Data, CreationDate
keyField, UserID1, FieldNo=3, Field3Data, CreationDate

You really should use TWO tables:

Table1
UserID, Creation Date

Table 2
UserID, Fielddata

Then relate them as one to many. Table 1 is the One side and UserID is a
Primary Key
in that table. Table 2 is the many side and UserID is a key but NOT a
unique or Primary Key.
Each record in Table 1 will have 36 related records in Table 2.

Does Field1 have a CreationDate unique from Field2, 3, etc? The structure
of your
first example doesn't seem to say so. It looks like there's only one
CreationDate for all
Field1,Field2,...Field36. That's how I designed the improved version.

Tom Lake

 

[Guest]

Tom

Thanks for the quick response. In effect I think what you're suggesting is
what I am after - and as an aside, the creation date is purely data so can
stay in the first table.

The situation is that I already HAVE a table populated with plenty of data
as I describe and I'm looking for a way to create the one to many related
tables. Elsewhere I have the data relating to the user - so I just need to
find a way to tranpose the data in the 36 field table into a two, three or
four field table where as you suggest I will have 36 related records for each
user.

The source data comes in to me from elsewhere, so (at this stage anyway) I
don't have access to change the import method, so am trying to make the data
more usable.

Any advice greatly received.

Thanks

Giles

 

[John W. Vinson]

Apologies if this is a bit basic - I'm rather new to Access.

Am using Access 2003 on XPSP2

I have one table with data that looks a bit like this:

UserID1, Field1, Field2, Field3, Field4 . . .Field36, CreationDate
UserID2, Field1, Field2, Field3, Field4 . . .Field36, CreationDate

a badly denormalized table, as I gather you have decided! Good on ya for
fixing it!

I'd like to make a new table which builds up the data from the first table
as follows:

KeyField, UserID1, FieldNo=1, Field1Data, CreationDate
KeyField, UserID1, FieldNo=2, Field2Data, CreationDate
keyField, UserID1, FieldNo=3, Field3Data, CreationDate

A "Normalizing Union Query" can do this. Open a new Query with nothing
selected as a Table and go to SQL view (you'll see a big textbox with just
SELECT; in it). Edit it to

SELECT UserID, (1) As FieldNo, Field1 AS FieldData, CreationDate
FROM yourtable
WHERE Field1 IS NOT NULL
UNION ALL
SELECT UserID, (2) As FieldNo, Field2 AS FieldData, CreationDate
FROM yourtable
WHERE Field2 IS NOT NULL
UNION ALL
SELECT UserID, (3) As FieldNo, Field3 AS FieldData, CreationDate
FROM yourtable
WHERE Field3 IS NOT NULL
UNION ALL
<etc through all 36 fields>

Open this query as a datasheet to see if it fits what you expect; then base an
Append query on it to append it to your (empty) new table. The autonumber
keyfield should not be included in the Append query, it will increment
automatically for you.

John W. Vinson [MVP]

 

[Guest]

John,

Thanks for this - I shall give it a try.

I feared that I was going to be restricted to writing multiple update
queries - not a task to be relished!!

Giles