Append Query/ Custom PK

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eka1618 - 27 May 2008 20:05 GMT

Hello,

I have created an Append Query (qryAppendTests) that looks like the following:

INSERT INTO tblQueue ( REQUEST_NO, REQUESTOR, DUE_DATE, TEST_TYPE, CYCLE_NO,
TEST_ID )
SELECT tblTest.REQUEST_NO, tblRequest.EMP_ID, tblRequest.DUE_DATE,
tblTest.TEST_TYPE, tblTest.CYCLE_NO, tblTest.TEST_ID
FROM tblRequest RIGHT JOIN tblTest ON tblRequest.REQUEST_NO =
tblTest.REQUEST_NO
WHERE (((tblTest.REQUEST_NO) Like [forms].[frmRequestNumber].[REQUEST_NO]));

tblQueue has a composite PK that consists of Q_ID and Q_YEAR. I have created
a custom sequence on Q_ID when I am inside a form using the following code:

queueID = DMax("[QID]", "tblQueue", "[Q_YEAR] = " & Year(Date))

If Me.QID.Text = 0 Then
Me.QID.Text = queueID + 1
End If

My problem now is that when i run the query, it gives me append violations
because there is no value for the PK's in tblQueue because I am trying to do
it after i add the records.

I cannot use the AutoNumber data type for Q_ID because this field needs to
be set back to 1 often.

Does anyone have any sugestions on how I can combine these two methods
durring the INSERT?

~Erica~

Reply to this Message

Jeff Boyce - 27 May 2008 22:10 GMT

"Why?"

As in "why does the Q_ID need to be set back to 1 often?"

An Access Autonumber is designed to provide a unique row identifier. If you
are using it for anything else, be aware that it really isn't fit for human
consumption.

You've described 'how' you are doing something, but not much about 'what'
and 'why'. If you'll explain more about the underlying business need (and
not the technique you've chosen), folks here may be able to offer alternate
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

> Hello,
>
[quoted text clipped - 34 lines]
>
> ~Erica~

Reply to this Message

Eka1618 - 28 May 2008 12:52 GMT

Jeff,

Where I work, They currently use a method in which they label each test
conducted in a year as "2008-0001, 2008-0002" and so on untill a new year
starts. Then it goes "2009-0001, 2009-0002".

I have 2 fields: Q_ID and Q_YEAR which stores these values. What I want to
do works, but only if I have an additional AutoNumber field in tblQueue. This
field,(Q_INC), allows me to use the append query so that when the records are
appended, there is a PK established.

They want Q_ID to automatically generate and that is how I ended up comming
up with the code below. I just wanted to delete the Additional Field (Q_INC)
and use Q_ID and Q_YEAR as a composite PK. I have tried to do so, but since I
cannot get Q_ID to update and increment with my own code, it tried to Append
a record with the same number for Q_ID each time.

I realize that AutoNumber is not what I want and that's why I do not want to
use that as a PK. I just would like to create my own sequence and trigger it
to assign a number (from the conditions below) to Q_ID each time an insert
orrcurs from the query.

I hope this explains it more for you!

~Erica~

> "Why?"
>
[quoted text clipped - 52 lines]
> >
> > ~Erica~

Reply to this Message

Jeff Boyce - 28 May 2008 18:07 GMT

Another reason not to use Autonumber data type is because you are NOT
guaranteed sequential numbering!

If you want to "roll your own" sequential numbering, take a look at
mvps.org/access and/or search on-line for "Custom Autonumber". This is a
misnomer, but you get the idea.

The basic concept is to have Access look up the largest previous sequence
number, add one, and use that as your next sequence number.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

> Jeff,
>
[quoted text clipped - 93 lines]
>> >
>> > ~Erica~

Reply to this Message