Can ACCESS do this just by Query

[amolin]

I intend to convert the table 1 to Table 2, do I have good method?


Table1

[name] [age] [workWeek1] [workWeek2] [workWeek3]
Marry 20 10020 23302 24000


table 2

[Name] [age] [workWeek] [Value]
Marry 20 workWeek1 10020
Marry 20 workWeek2 23302
Marry 20 workWeek3 24000

Thank you..

 

[Newman]

You should use two tables:

Table1
[Name*] [age]
Marry   20

Table2
[Name*] [workWeek*] [Value]
Marry   workWeek1   10020
Marry   workWeek2   23302
Marry   workWeek3   24000

With * = primary keys
Linking these tables by the field «NAME».
This way, when Marry gets 21, you'll have to change the data only once.

 

[amolin]

I intend to convert the table 1 to Table 2, do I have good method?


Table1

[name]---[age]---[workWeek1]---[workWeek2]---[workWeek3]
Marry ----20 -----10020----------23302---------24000


result by query:

[Name]---[age]----[workWeek]-----[Value]
Marry-----20-------workWeek1-----10020
Marry-----20-------workWeek2-----23302
Marry-----20-------workWeek3-----24000

Thank you..

Thank you, Newman, May be I am not make it clear, I mean, Can I design a query to to get second result? thank you.

 

[ColinEssex]

Your design is not too brilliant

The "person" details should be in one table so you only enter them once and should have an AutoNumber as a PK call it "PersonID" or something. The workweek detail should be in another table with a link on the previous AutoNumber as a Foreign Key and the fields should be "PersonID","WorkweekID" and "WorkweekValue" then you just link the 2 in the normal way in a query to get your result.

You must make sure that there is a new record in the 2nd table for every workweek entry.

What would you do if there is a workweek4 or 5 or 6? add new fields to your table?

Col

 

[amolin]

Your design is not too brilliant

The "person" details should be in one table so you only enter them once and should have an AutoNumber as a PK call it "PersonID" or something. The workweek detail should be in another table with a link on the previous AutoNumber as a Foreign Key and the fields should be "PersonID","WorkweekID" and "WorkweekValue" then you just link the 2 in the normal way in a query to get your result.

You must make sure that there is a new record in the 2nd table for every workweek entry.

What would you do if there is a workweek4 or 5 or 6? add new fields to your table?

Col

Thank you, The Table1 is not under my control, so I intend to convert the Table1 to the Second table2 Format to make it easy to handle. I usurally do this by VBA and ADO before.

I wonder if we can have another easy method to do this?

 

[ColinEssex]

so how many workweek fields have you? - just the 3 or many more?

Col

 

[amolin]

so how many workweek fields have you? - just the 3 or many more?

Col

workweeks fields number is not fixed. But suppose that we only have 10 workweeks fields, how to do this? thank you.

 

[Pat Hartman]

You would need to create an append query for EACH workweek column. You can do this in code if the number of columns isn't fixed or is too large.

 

[neileg]

If table 1 is not under your control, you might be better off using a union query that will give you what you want dynamically. By the way, both NAME and VALUE are bad titles for your fields as they are reserved words in Access.

Try this union query

SELECT Table1.NameField, Table1.Age, "Workweek1" AS Workweek, Table1.Workweek1 AS ValueField
FROM Table1
UNION SELECT Table1.NameField, Table1.Age, "Workweek2" AS Workweek, Table1.Workweek2 AS ValueField
FROM Table1
UNION SELECT Table1.NameField, Table1.Age, "Workweek3" AS Workweek, Table1.Workweek3 AS ValueField
FROM Table1;

 

[amolin]

Thank you all for your great ideas!

May I ask another question?

May I use :
Fields(3).name replace workWeek1,
Fields(4).name replace WorkWeek2,
Fields(5).name replace workWeek3,
or similar method in Access?

because WorkWeeks fields always begin AT 3TH COLUMN.



SELECT Table1.NameField, Table1.Age, "Workweek1" AS Workweek, Table1.Workweek1 AS ValueField
FROM Table1
UNION SELECT Table1.NameField, Table1.Age, "Workweek2" AS Workweek, Table1.Workweek2 AS ValueField
FROM Table1
UNION SELECT Table1.NameField, Table1.Age, "Workweek3" AS Workweek, Table1.Workweek3 AS ValueField
FROM Table1;