Querry Issue - No Refresh of Existing Table

[PD1978US1]

HI,

I have a query running which creates a table.
It automatically refreshes / runs the query every 5mins.

it adds new data, but it doesn't seem to update data as I have orders which are created with status "Normal", but still have the same status after being shipped. The status should turn to status "Shipped".

Anybody have an idea what I can adjust?

 

[isladogs]

Run an update query to change the status of any existing records from normal to shipped

 

[PD1978US1]

Re: Query Issue - No Refresh of Existing Table

Thanks.
Just read into it.
Just noticed that then I try to append the data it gives me a key violation error.
I have a table with two primary keys.
One being the order number and one the product number.

key Violation error is based on duplicate values.
This is applicable because the order number can be in the table multiple times as I have more than one box which belongs to an order.

Any way to fix this?

 

[isladogs]

Just re-read your first post

I have a query running which creates a table.

If you are using a MAKE TABLE query, it will REPLACE the existing table so you lose all previous data

The sequence should be APPEND query followed by UPDATE query (or vice versa).
You MAY be able to combine these steps as in the attached simplistic example.
See if you can adapt it.
It will append new records & update status. Existing records aren't imported again

I've left warning messages on so you can see what its doing
Let me know if you don't know how to switch off warning messages

 

[Mark_]

The status should turn to status "Shipped".

What code are you using to change the status to "SHIPPED" and where is it being triggered?

 

[Galaxiom]

Re: Query Issue - No Refresh of Existing Table

Thanks.
Just read into it.
Just noticed that then I try to append the data it gives me a key violation error.
I have a table with two primary keys.
One being the order number and one the product number.

key Violation error is based on duplicate values.
This is applicable because the order number can be in the table multiple times as I have more than one box which belongs to an order.

Any way to fix this?

You could add another field as a synthetic key. For example Autonumber.

ProductNumber is unsuitable as a key in an OrderDetail table unless you only sell a product once. (For example if every product is unique.)

You might use a composite key consisting of both OrderNumber and ProductNumber in the same key/unique index. This would prevent the same product being listed twice in the same order.

BTW A table only has one Primary Key. There can be multiple keys but only one can be designated Primary.

 

[isladogs]

Re: Query Issue - No Refresh of Existing Table

BTW A table only has one Primary Key. There can be multiple keys but only one can be designated Primary.

I'm sure i'm going to regret saying this but what about composite primary keys? e.g. 3 fields as a combined primary key

Yes I know its ONE composite PK but that's not what your statement seems to imply

My solution in post 4 used a composite PK

P.S. Before anyone mentions it - Yes I also know Year is a reserved field & that there are 3 TeacherID columns ...

 

[Galaxiom]

Re: Query Issue - No Refresh of Existing Table

I'm sure i'm going to regret saying this but what about composite primary keys? e.g. 3 fields as a combined primary key

Yes I know its ONE composite PK but that's not what your statement seems to imply

I believe my statement is clear. There can only be one Primary Key in a table whether it be composite or otherwise. Which particular part did you think was misleading?

You have even said it yourself. A composite key consisting of multiple fields is still a single key. A key and a field are quite different entities despite a field being able to be used as the base for a key.

 

[plog]

Why must this query move data around? Why must the data be saved somewhere? Why can't you just use a SELECT query?

 

[isladogs]

Hi Greg

I knew perfectly well what you meant but felt this sentence wasn't quite as clear as your usual exemplary explanations ....

BTW A table only has one Primary Key. There can be multiple keys but only one can be designated Primary.

To my mind, this would have been easier for the OP to understand

A table only has one Primary Key. There can be multiple fields used in the key but it is still ONE primary key

Of course, there are some developers who don't believe composite keys should EVER be used ... but that's a whole new discussion.

 

[Galaxiom]

To my mind, this would have been easier for the OP to understand

A table only has one Primary Key. There can be multiple fields used in the key but it is still ONE primary key.

I can see where you are coming from now and your perspective is valid in itself. I think we took different interpretations on what the OP was inferring when they said:

I have a table with two primary keys.

I took it literally but I suspect you saw what they actually meant. Consequently, your interpretation is different from mine.

I was meant is that it is quite possible to have multiple single field keys in a table. They are often called Candidate Keys because they are capable of being used as Primary Keys.

For example, an Automumber synthetic key in an Invoice table and the actual displayed InvoiceNumber field. Both are unique but only one can be the Primary Key.

I touched on the composite key when I wrote:

You might use a composite key consisting of both OrderNumber and ProductNumber in the same key/unique index.

I do hope OP understands both perspectives. Terminology can be quite a barrier for a while.

 

[isladogs]

No problem...

Terminology can be quite a barrier for a while.

I agree. Often half the battle is understanding what the OP means and for them it can be a problem understanding what we mean.

Hope you didn't mind me raising the point.
You have (quite rightly) picked me up at times when I've been sloppy in the language I've used.
Actually that's a euphemism - in my case read as 'when I've been wrong'