Access Update Query

[darinmc]

Hi
I'm using access 2003. I'm trying to do an update (tblPayInv) query taking information from a table (Table1INVnumbers)

SETTING tblPayInv.InvNo = [Table1INVnumbers]![invNO]

The 2 criterias are
(tblPayInv)WEdate must be IN Table1INVnumbers(wedate)
AND
(tblPayInv)InvNo must be IN Table1INVnumbers(invNO)

UPDATE tblPayInv SET tblPayInv.InvNo = [Table1INVnumbers]![invNO]
WHERE (((tblPayInv.InvNo)=[Table1INVnumbers]![invNO]) AND ((tblPayInv.JobsID)=[jobid]) AND ((tblPayInv.WEdate)=[week end]));

I have tried quite a few times but still not getting anywere. I also noticed that I THINK I should be using the IN (SELECT.... ) Statement

Appreciate ALL help

Thx
Darin

 

[Remou]

This looks a little odd. You have joined the table on InvNo, which means they must be the same, so there is no point in saying:
SET tblPayInv.InvNo = [Table1INVnumbers]![invNO]

Did you want to add records to tblPayInv once they met some criteria?

 

[darinmc]

thx Remou

There are 2 different tables, that might explain why it asked me for invoice numbers...

I need to Update records in tblPayInv

tblPayInv (Table Holds Payroll Info)
Fields = WEdate, InvNo, JobsID + others

Table1INVnumbers (Table Holds the invoice Numbers -Once they are created)
Fields = invNO, jobID, wedate

---------------
Query should be something like....

Update tblPayInv SET InvNo = (invoice numbers IN Table1INVnumbers, There might be just 1 record or 5 records per invoice number) WHERE WEdate=wedate AND JobsID=jobID

---------------

Thx
Darin

 

[Remou]

I am sorry but I am still not clear on what you want to do. You descriptions suggest that records should be added, but your pseudo-query shows updating. Perhaps you could post some sample data and the required outcome of the query?

 

[darinmc]

Hi

(Table1INVnumbers - data below) This table only has the 3 fields, it is created about a week later. Where the jobID and wedate match, I need to transfer invNO across to tblPayInv
jobID invNO wedate
300 20426 06/07/2008
95 20280 06/07/2008
192 20281 06/07/2008

tblPayInv holds all the payroll data, the hours, rates and notes etc, there is a field (InvNo) which about a week later needs to be filled in using data from the above table
(tblPayInv - Part of data set for E.g)
JobsID WEdate EmpRegNo InvNo ....etc etc
142 06/07/2008 359
20 06/07/2008 357
20 06/07/2008 441
20 06/07/2008 441
20 06/07/2008 370
20 06/07/2008 370
20 06/07/2008 6
21 06/07/2008 370
21 06/07/2008 441
21 06/07/2008 370
21 06/07/2008 359
21 06/07/2008 359
21 06/07/2008 6


InvNo needs to be populated when the JobsID & WEdate match the 1st table..

How would this be achieved?

Thx
Darin

 

[Remou]

Ok. This does not look like a good idea from a normalization point of view. It would be better to create a query that shows this information, otherwise you are going to end up with a table with duplicate data that could easily become quite difficult to manage. Are you sure you want to do this? Have you read

http://www.r937.com/relational.html?

 

[darinmc]

Hi

Yes, the below table is temporary, its created from a spreadsheet.
If the InvNo is repeated, thats fine, The invoice number must be assigned to each record in the tblPayInv so that when ay user clicks on that record, they will find which Invoice it relates to and go straight to sage to deal with any queries...

If I could import the InvNo directly from a spreadsheet, that would save some messing around BUT not sure how easy that would be...

(Table1INVnumbers - data below) This table only has the 3 fields, it is created about a week later. Where the jobID and wedate match, I need to transfer invNO across to tblPayInv
jobID invNO wedate
300 20426 06/07/2008
95 20280 06/07/2008
192 20281 06/07/2008

Thx
Darin

 

[Remou]

You can link the spread sheet and then create a query that joins tblPayInv to the linked speadsheet. This would get the same result (invoice shown against tblPayInv) without the hassle of constant updates. Do you see this as an option?

 

[darinmc]

Hi
I think I would prefer an update method.. The spreadsheet changes each week ending date.

Payroll is open all the time and when we view a record, would be much better without running the query. Once the update query is written, i'm assuming the process would be very quick.

something like
Update tblPayInv, InvNo where JobsID IN Table1INVnumbers AND WEdate = [e.g. 6/7/08]

Would this be easy enough to create and how?

Thx
Darin

 

[Remou]

Try:

UPDATE tblPayInv INNER JOIN Table1INVnumbers
ON (tblPayInv.JobsID=Table1INVnumbers.JobsID)
AND (tblPayInv.WEDate=Table1INVnumbers.WEDate)
SET InvNo=Table1INVnumbers.InvNo

 

[PHV]

SET [!]tblPayInv.[/!]InvNo=Table1INVnumbers.InvNo

 

[darinmc]

Hi
I got it to work well, thx for help

UPDATE tblPayInv INNER JOIN Table1INVnumbers ON tblPayInv.JobsID = Table1INVnumbers.jobID SET tblPayInv.InvNo = Table1INVnumbers.InvNo
WHERE ((tblPayInv.JobsID)=[jobid]) AND (((tblPayInv.WEdate)=[Week Ending Date]));

Thx
Darin