Insert question

[ams]

I set up a database of civic league members and residences.
I set the primary key to conform to addresses by street numbers.
Unfortunately, I omitted two buildings.
How do I insert the data for these two items where they should be ?
The Primary Key doesn't seem to like that :(

Thanks much,
newbie here

[OM]

Re: Insert question

"ams" <none@cox.net > wrote in message
news:m2Lqc.3135 2$Md.29945@lake read05...[color=blue]
> I set up a database of civic league members and residences.
> I set the primary key to conform to addresses by street numbers.
> Unfortunately, I omitted two buildings.
> How do I insert the data for these two items where they should be ?
> The Primary Key doesn't seem to like that :(
>
> Thanks much,
> newbie here[/color]

I've just done this (and probably did it a very roundabout way), but I used
a make table query, with the records sorted the way I wanted, but without
the Primary key, to make a new Temp table. I then deleted all the records
from the original table, and used an append query to add the sorted ones
from my new Temp table, then deleted the Temp table.

Hey ! Worked for me !

Rob Lepper

[ams]

Re: Insert question


"OM" <lepperrj@loxin fo.dot.co.th> wrote in message
news:c8g0r5$hj6 $1@news.loxinfo .co.th...[color=blue]
> "ams" <none@cox.net > wrote in message
> news:m2Lqc.3135 2$Md.29945@lake read05...[color=green]
> > I set up a database of civic league members and residences.
> > I set the primary key to conform to addresses by street numbers.
> > Unfortunately, I omitted two buildings.
> > How do I insert the data for these two items where they should be ?
> > The Primary Key doesn't seem to like that :(
> >
> > Thanks much,
> > newbie here[/color]
>
> I've just done this (and probably did it a very roundabout way), but I[/color]
used[color=blue]
> a make table query, with the records sorted the way I wanted, but without
> the Primary key, to make a new Temp table. I then deleted all the records
> from the original table, and used an append query to add the sorted ones
> from my new Temp table, then deleted the Temp table.
>
> Hey ! Worked for me !
>
> Rob Lepper
>[/color]
Thanks, Rob. I did the round-about as well. I exported in Excel format.
Inserted the two buildings, and imported back into Access with a new table
name.

Much appreciated.
ams

[Wayne Morgan]

Re: Insert question

The order that items are in a table should be irrelevant to you. If you want
them sorted, use a query. You can set sorting options in a query. Also, if
by inserting into the primary key, you mean that you have an autonumber and
you want to insert into the middle of it then - don't. Autonumbers will give
each record a unique ID, beyond that they have no meaning. You shouldn't
expect to be able to keep them lined up. If you need a field to do that,
you'll need to create one yourself. You could then have updated the ones
beyond where you wanted to insert by running an Update Query to add 2 to
each of the values beyond that point then inserted your other 2 records,
setting the field value to the sequence that you want.

--
Wayne Morgan
Microsoft Access MVP


"ams" <none@cox.net > wrote in message
news:m2Lqc.3135 2$Md.29945@lake read05...[color=blue]
> I set up a database of civic league members and residences.
> I set the primary key to conform to addresses by street numbers.
> Unfortunately, I omitted two buildings.
> How do I insert the data for these two items where they should be ?
> The Primary Key doesn't seem to like that :(
>
> Thanks much,
> newbie here
>
>[/color]

[ams]

Re: Insert question

Wayne,

I was more or less asking, how do you insert a new row with new data in
between existing
rows that already have a primary key assigned. For some reason, in the
table dataview mode,
I am unable to insert such a row between existing ones. I will try out the
Update Query as you
suggested.

I know, I am not explaining myself well. Sorry.
Thank you,
ams


"Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:gKMqc.1933 2$AG7.7679@news svr31.news.prod igy.com...[color=blue]
> The order that items are in a table should be irrelevant to you. If you[/color]
want[color=blue]
> them sorted, use a query. You can set sorting options in a query. Also, if
> by inserting into the primary key, you mean that you have an autonumber[/color]
and[color=blue]
> you want to insert into the middle of it then - don't. Autonumbers will[/color]
give[color=blue]
> each record a unique ID, beyond that they have no meaning. You shouldn't
> expect to be able to keep them lined up. If you need a field to do that,
> you'll need to create one yourself. You could then have updated the ones
> beyond where you wanted to insert by running an Update Query to add 2 to
> each of the values beyond that point then inserted your other 2 records,
> setting the field value to the sequence that you want.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "ams" <none@cox.net > wrote in message
> news:m2Lqc.3135 2$Md.29945@lake read05...[color=green]
> > I set up a database of civic league members and residences.
> > I set the primary key to conform to addresses by street numbers.
> > Unfortunately, I omitted two buildings.
> > How do I insert the data for these two items where they should be ?
> > The Primary Key doesn't seem to like that :(
> >
> > Thanks much,
> > newbie here
> >
> >[/color]
>
>[/color]