By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,201 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Adding fields to a table

P: 10
I have a table that contains the positions an employee currently works, e.g.

ID, NAME, POSITION
1, BOB SMITH, IT ANALYST
2, BOB SMITH, DBA ORACLE


How do I alter the table so that people who currently hold multiple positions will have their position listed horizontally rather than vertically.

The only other complication is that the number of positions a person holds can vary from 1 to 5, it all depends upon the person. Can fields be added to a table based upon the number of positions a person holds? i.e. If Bob holds 2 positions and Mary holds 1, can a SQL statement be structured to add 2 fields, POS1, POS2?

Thanks

ACCESS 2003.
Aug 16 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 126
I have a table that contains the jobs an employee currently works, e.g.

ID, NAME, JOB
1, BOB SMITH, IT ANALYST
I think you need to give more information about what you're trying to do, did you press submit prematurely?

Using VBA/SQL you could add those fields (assuming they were stored as variables) by:

Docmd.runSQL("INSERT INTO tblJobs VALUES('" & ID & "', '" & NAME & "', '" & JOB & "');")
Aug 16 '07 #2

Expert 100+
P: 126
Either you editted that, or only half of it loaded.
You would have to have more than one position field (eg. POS1, POS2 like you suggested) for EACH employee, no matter how many jobs they had. You could have a table with only one job, and then duplicate this table, but you're still going to have empty/null columns when you construct your query.

I haven't got time to write the query, but doing it through VBA and iterating will help. Take note of the Nz function too. Good luck.
Aug 16 '07 #3

P: 10
Just an example

I'll take a look at the Nz
Aug 16 '07 #4

P: 10
I think you need to give more information about what you're trying to do, did you press submit prematurely?

Using VBA/SQL you could add those fields (assuming they were stored as variables) by:

Docmd.runSQL("INSERT INTO tblJobs VALUES('" & ID & "', '" & NAME & "', '" & JOB & "');")
Yes, sorry, got distracted.
Aug 17 '07 #5

P: 10
I think you need to give more information about what you're trying to do, did you press submit prematurely?

Using VBA/SQL you could add those fields (assuming they were stored as variables) by:

Docmd.runSQL("INSERT INTO tblJobs VALUES('" & ID & "', '" & NAME & "', '" & JOB & "');")
Stwange,

I'll try to clarify

I can certainly use the INSERT INTO statement, but how do you structure the statement to have it add a field to a table multiple times?

if you need more info let me know what else may help you.
Aug 17 '07 #6

Expert 100+
P: 126
Stwange,

I'll try to clarify

I can certainly use the INSERT INTO statement, but how do you structure the statement to have it add a field to a table multiple times?

if you need more info let me know what else may help you.
This isn't the best design, but none of the database design stuff I ever learnt has ever really helped me in real life, so I guess I won't pass judgement :)

If you have the table set out like so:

Employee ID, Name, JOB1, JOB2, JOB3

Then do something like:
dim strSQL as String
strSQL = "INSERT INTO tblJobs VALUES(Name, ID, JOB1"
dim i as integer
for i = 1 to (Number_of_jobs - 1)
strSQL = strSQL & ", " & nextJob"
next i
for i = 1 to (3 - Number_of_jobs)
strSQL = strSQL & ", ''"
next i
docmd.runsql(strSQL & ");")

This should do the trick, how you get the loop to find each job for adding it (eg, an array?) is up to you, but let me know if I have misunderstood what you want to do.
Aug 17 '07 #7

P: 10
Stwange,

Thanks! I'll give it a shot and see how things work out.
Aug 17 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.