Append Query or Update Query To Do Something Specific

[NewYorkFashionToGo]

hello and thanks for looking at this post. I have a table that imports sales some of them paid others are not paid. So I have been using Update queries to update the table with Yes and No based on when a customer pays It automatically moves it into a shipping query. I have that down ,
My problem here is I want to Update a different piece of Information that is Unique on every single transaction(on the same update, a transaction ID basically. a way for me to trace the transaction. When I originally import it and The Invoices are not paid for, The transaction ID does not exist yet, because it didnt go down just yet. I am having a bit of trouble with this and not sure how to tackle it.

Any help would be appreciated, Thanks

 

[Darrylles]

Hi NYFTG,

Can you explain exactly what you are doing?
Also, you are using capitals as if starting new lines, and full-stops without cpaitals on the next line - very hard to make any sense of what you want.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[NewYorkFashionToGo]

Hi Darrylle, thanks for your post. I will try to make my typing more clear. I know that my typing is poor.

I import my sales off the internet. Some may be paid immediately others are not paid and just left in shopping cart and paid at a later time. I am able to run an update query to simply mark a checkbox that I have in the original table as Yes Or No. That is not my issue. All of the information is the same. On where I need to update other information. Every single customer is assigned a unique number a payment verification number. I am not configuring it properly to accept an update of that nature ( every row has a different value on Update query)...

Upon my newer imports it is not allowing me to import the verifcation numbers. Because they did not exisit (yet) the payment did not happen yet based from my original upload into access. I need to update the table with that information. Again I can Update a simple checkbox to yes or no perfectly fine. I need to update a different unique value on every line based on a completely seperate upload.

 

[Darrylles]

Hi MYFTG,

Is this the case:

1) You are importing sales info. from the 'Net' into a db table.
2) On import, you are flagging each row as paid or not with a Yes/No field (this works ok).
3) If paid, you are updating a field for that row with a unique number or code : 'payment verification number or PVN - this works ok.
4) For the unpaid imported rows: the PVN field is blank after the update (because it hasn't been paid and therefore shouldn't have a PVN).

Are you then wanting to update these blank PVN's on subsequent updates when they are eventually paid?

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[NewYorkFashionToGo]

Yes that is exactly it. Exactly what I am trying to do. I am doing something wrong. Access is not allowing me to do it.

 

[Darrylles]

NYFTG,
OK, you say:

Upon my newer imports it is not allowing me to import the verifcation numbers.

How exactly is it not allowing you?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[NewYorkFashionToGo]

It is not allowing me to import it because I have a Primary key set on a piece of information that would be uploaded twice and not give it a unique value. It goes into other major issues in the database, Inventory, Knowing what exactly is listed on what site.

 

[NewYorkFashionToGo]

As I was writing back and forth to you, I created a delete query to wipe out the (old) unpaid items so upon import it will re-import the new data(with payment details)

Its not my first choice. I am building here... In Time i am afraid it may be a nuisance and slow down the database.

 

[Darrylles]

OK,

You therefore need a query to UPDATE current unpaid rows, and a seperate query that APPEND's new transactions.

You need a 2 part process mate.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[NewYorkFashionToGo]

I understand Darrylles. I did something similar to that in a different database. Afterwards. It just effects many other things I have already done. Guess I just have to bite down and just do it. I thought perhaps there was another way out of this..

I do appreciate your time to post and help. Thank you for that.

Enjoy