Problems updating Access 2003 Database

  • Thread starter Thread starter ehess00
  • Start date Start date
E

ehess00

We currently use an Access 2003 data base to create and send forms within the
organization. We are seeing instances where the new records are not
appending and believe it may be the query that is causing the issue. The
INSERT query is:

INSERT INTO table_headerdata ( wso_num )
SELECT Max([table_headerdata]![wso_num])+1 AS Expr1
FROM table_headerdata
WHERE wso_num < 1000000;

We would appreciate any help to resolve this issue.
 
We currently use an Access 2003 data base to create and send forms within the
organization. We are seeing instances where the new records are not
appending and believe it may be the query that is causing the issue. The
INSERT query is:

INSERT INTO table_headerdata ( wso_num )
SELECT Max([table_headerdata]![wso_num])+1 AS Expr1
FROM table_headerdata
WHERE wso_num < 1000000;

We would appreciate any help to resolve this issue.
Click to expand...

Some possibly pointless questions: what's the datatype of wso_num in the table
definition? Are there any records in table_headerdata when you run this (you
might be creating new databases... I don't know!)? If there are records, are
there any with values of wso_num less than a million? Is wso_num the Primary
Key, or does it have a unique index? If you change the query from an APPEND
query into a SELECT query do you see the values you would expect? Do you get
any error, or just 0 rows inserted?

John W. Vinson [MVP]
 
ehess00 said:
We currently use an Access 2003 data base to create and send forms within the
organization. We are seeing instances where the new records are not
appending and believe it may be the query that is causing the issue. The
INSERT query is:

INSERT INTO table_headerdata ( wso_num )
SELECT Max([table_headerdata]![wso_num])+1 AS Expr1
FROM table_headerdata
WHERE wso_num < 1000000;

We would appreciate any help to resolve this issue.
Click to expand...


That select query only returns at most one record. How many
records are you trying to insert?

Why do you need sequential numbers instead of just any old
unique number.

What are you trying to accomplish?
 
John,

Here are the answers to your questions:
1) wso_num is a number field and is not the Primary Key to the table.
2) All wso_num values are less than one million
3) What is happening is that when a user selects "Create WSO" from a menu,
a new wso_num is created using the last one plus one. But, we have instances
of two users getting the same WSO number and it is merging the data from each
user into one, but applying only one header. There is a dummy_header_num
field that is the primary key to this table.

Should we make wso_num the primary key? Also, this database was recently
converted from Access 97 to Access 03.




John W. Vinson said:
We currently use an Access 2003 data base to create and send forms within the
organization. We are seeing instances where the new records are not
appending and believe it may be the query that is causing the issue. The
INSERT query is:

INSERT INTO table_headerdata ( wso_num )
SELECT Max([table_headerdata]![wso_num])+1 AS Expr1
FROM table_headerdata
WHERE wso_num < 1000000;

We would appreciate any help to resolve this issue.
Click to expand...

Some possibly pointless questions: what's the datatype of wso_num in the table
definition? Are there any records in table_headerdata when you run this (you
might be creating new databases... I don't know!)? If there are records, are
there any with values of wso_num less than a million? Is wso_num the Primary
Key, or does it have a unique index? If you change the query from an APPEND
query into a SELECT query do you see the values you would expect? Do you get
any error, or just 0 rows inserted?

John W. Vinson [MVP]
Click to expand...
 
Back
Top