Primary Key

K

kr

I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update query
as well. i'm not that confident in vba and just looking to see if there is a
shorter way.
Thanks.
 
J

John Spencer

One method would be to add an Autonumber field to the records and then
use an expression like the following to assign John and Jane in a query.

IIF([AutonumberField] Mod 2 = 0, "John","Jane")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
D

Dale Fye

Assuming that you are importing the record from Excel into a table that
contains an autonumber primary key, then you could write an update query
that updates the AssignedTo field in your table, something like:

UPDATE yourTable
Set [AssignedTo] = IIF([ID] Mod 2 = 0, "John", "Jane")
WHERE [AssignedTo] Is Null

HTH
Dale
 
K

kr

okay, this is great. so let me make sure i have it correct:
1. Import table with records from excel. Add a autonumber field.
2. Create an update query with the expression below and then run the query

Am I following right?

John Spencer said:
One method would be to add an Autonumber field to the records and then
use an expression like the following to assign John and Jane in a query.

IIF([AutonumberField] Mod 2 = 0, "John","Jane")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update query
as well. i'm not that confident in vba and just looking to see if there is a
shorter way.
Thanks.
 
K

kr

This seems about the same as well. I will create using this with SQL

Dale Fye said:
Assuming that you are importing the record from Excel into a table that
contains an autonumber primary key, then you could write an update query
that updates the AssignedTo field in your table, something like:

UPDATE yourTable
Set [AssignedTo] = IIF([ID] Mod 2 = 0, "John", "Jane")
WHERE [AssignedTo] Is Null

HTH
Dale

kr said:
I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John
and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update
query
as well. i'm not that confident in vba and just looking to see if there is
a
shorter way.
Thanks.
 
K

kr

Also, let's say I have the employee names in a table in access. how could i
use the table to add in the names alternatelly for every record? would I use
the same IIF and replace with table? Wouldn't this be a value list as well?

Dale Fye said:
Assuming that you are importing the record from Excel into a table that
contains an autonumber primary key, then you could write an update query
that updates the AssignedTo field in your table, something like:

UPDATE yourTable
Set [AssignedTo] = IIF([ID] Mod 2 = 0, "John", "Jane")
WHERE [AssignedTo] Is Null

HTH
Dale

kr said:
I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John
and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update
query
as well. i'm not that confident in vba and just looking to see if there is
a
shorter way.
Thanks.
 
K

kr

This worked perfect!!!!! thanks!

John Spencer said:
One method would be to add an Autonumber field to the records and then
use an expression like the following to assign John and Jane in a query.

IIF([AutonumberField] Mod 2 = 0, "John","Jane")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update query
as well. i'm not that confident in vba and just looking to see if there is a
shorter way.
Thanks.
 
K

kr

Worked great! Awesome! Thank you sooo much!

Dale Fye said:
Assuming that you are importing the record from Excel into a table that
contains an autonumber primary key, then you could write an update query
that updates the AssignedTo field in your table, something like:

UPDATE yourTable
Set [AssignedTo] = IIF([ID] Mod 2 = 0, "John", "Jane")
WHERE [AssignedTo] Is Null

HTH
Dale

kr said:
I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John
and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update
query
as well. i'm not that confident in vba and just looking to see if there is
a
shorter way.
Thanks.
 
K

kr

Sorry about this but 1 more thing,
coudl you explain the expression? obviously the Mod 2 is telling the
Autonumber Field to react based on every other field right? and let's say
like before, if I have a table of employee names (more than just two) telling
it to respond in the similar way? Like, if i had 5 employees, and still want
to equally divide the records, would it still be mod 2?

John Spencer said:
One method would be to add an Autonumber field to the records and then
use an expression like the following to assign John and Jane in a query.

IIF([AutonumberField] Mod 2 = 0, "John","Jane")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update query
as well. i'm not that confident in vba and just looking to see if there is a
shorter way.
Thanks.
 
H

huangda 晋江兴达机械

kr said:
I have a database in which i import records from excel spreadsheet. once in
the database, i would like to assign the records to my employees -
automatically. I have 2 employees - John and Jane. I would like for John
and
Jane's names to show up alternately for every record. Is there a way to
change the primary key to do this? I tried to come about it from update
query
as well. i'm not that confident in vba and just looking to see if there is
a
shorter way.
Thanks.
 
J

Jellifish

kr said:
Sorry about this but 1 more thing,
coudl you explain the expression? obviously the Mod 2 is telling the
Autonumber Field to react based on every other field right? and let's say
like before, if I have a table of employee names (more than just two)
telling
it to respond in the similar way? Like, if i had 5 employees, and still
want
to equally divide the records, would it still be mod 2?

No, the "Mod" operator returns the remainder from dividing the first
parameter by the second:

16 Mod 10 = 6 (16/10 = 1 remainder 6)
20 Mod 19 = 1 (20/19 = 1 remainder 1)
3 Mod 4 = 3 (3/4 = 0 remainder 3)

So, if you wanted to do this for more than 2 employees you would either have
to nest IIFs (nasty), use a table with key/value pairs (good for lots of
employees) or use the choose function (good for a few employees):

Choose(([AutoNumberField] Mod 5)+1,"Five","One","Two","Three","Four")

the "+1" part ensures that the result of the Mod operator begins at 1
instead of zero.
 
K

kr

Okay thats cool I will try the choose function. But how do i get it to call
kr said:
Sorry about this but 1 more thing,
coudl you explain the expression? obviously the Mod 2 is telling the
Autonumber Field to react based on every other field right? and let's say
like before, if I have a table of employee names (more than just two)
telling
it to respond in the similar way? Like, if i had 5 employees, and still
want
to equally divide the records, would it still be mod 2?

No, the "Mod" operator returns the remainder from dividing the first
parameter by the second:

16 Mod 10 = 6 (16/10 = 1 remainder 6)
20 Mod 19 = 1 (20/19 = 1 remainder 1)
3 Mod 4 = 3 (3/4 = 0 remainder 3)

So, if you wanted to do this for more than 2 employees you would either have
to nest IIFs (nasty), use a table with key/value pairs (good for lots of
employees) or use the choose function (good for a few employees):

Choose(([AutoNumberField] Mod 5)+1,"Five","One","Two","Three","Four")

the "+1" part ensures that the result of the Mod operator begins at 1
instead of zero.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top