Update Query how it works

[waqas]

dear all,

i want to update in table "tbloffrep" in column leavecount


from query "qaleave" totallev i am using both table and query in update query.

i created below query but error comes "operation must use an updateable query."

UPDATE qaleave INNER JOIN tblOFFRep ON qaleave.ID = tblOFFRep.ID SET tblOFFRep.LeaveCount = [QALeave].[TotalLev];


kindly guide me about update query.

[rpeare]

Is there a specific reason you're storing a calculated value in a table as opposed to figuring it out in a query? Without an example of your database I'm not sure I can direct you but if you can not change how your application operates I would likely perform this update with a dfunction, dlookup to be exact where I'd update based on the unique key which should be carried in your qaleave query. It also looks like you're trying to update a query not a table

UPDATE qaleave

Unless I'm misinterpriting your structure qaleave is the query that has your calculation and tblOffRep is the table you want to update. This update statement is saying update QALeave

[waqas]

YES qaleave is a query and i am storing a calculate value in table

but i want to update tbloffrep table from this qaleave query

my qaleave query is
SELECT EmpMasterList.ID, Count(EmpClocking.OtherType) AS TotalNWD, First((Format([CIN],"dd-mmm-yy"))) AS NWStart, Last((Format([CIN],"dd-mmm-yy"))) AS NWEnd
FROM EmpClocking RIGHT JOIN EmpMasterList ON EmpClocking.ID = EmpMasterList.ID
WHERE (((EmpClocking.OtherType)<>"OFF"))
GROUP BY EmpMasterList.ID
ORDER BY EmpMasterList.ID;

and table is tbloffrep what should i do to update this table from qaleave query

[rpeare]

can you provide a sample of your database, it will be easier to understand with sample data etc.

[waqas]

dear i have attached my db example

kindly see and guide me how can i use update query in future
from query to table or from table to table with joins

[rpeare]

You have a query that is supposed to delete everything from tblOFFRep, then an append query that appends information to the same table. You have no update query in there so the easiest thing to do would be to run your delete query then run your append query. You are not putting in any criteria so you can just generate this table every time you need.

However, let me point this out. You are basically performing a bunch of calculations in extra tables, this really is not necessary.

In your table tblOFFRep you have these fields:

ID (foreign key to your employee master list table)
OFFCount
LastOFFDate
LastWorkDate
WorkDays
LeaveStart
LeaveEnd
LeaveCount

*all* of these appear to be calculations

OFFCount I am assuming is a count of days marked OFF only
LastOFFDate is just the most recent date marked OFF only
LastWorkDate I'm assuming is the most recent date that does NOT appear in your table EmpClocking?
WorkDays I'm assuming is some sort of monthly/weekly/bi-weekly count of work days in a pay period?
LeaveStart is the start of the most recent leave period marked as anything except OFF in your EmpClocking table?
LeaveEnd is the end of the most recent leave period marked as anything except OFF in your EmpClocking table?
LeaveCount would then be the count of days that are anything except OFF in your EmpClocking table?

If this is correct *all* of this can be done with a query without the need to create another table and update it.

[waqas]

thanks for your good observation.

now i tell you which information i need.

from employee last off date upto lastwork date period how many days he worked excluding off leave,absent,sick leave .....etc

and i want to show in report all columns

id,last off date, last work date, total work days and total no work days, leave start date and leave end date.

including all id numbers who dont have off.

[rpeare]

You didn't answer my questions about what the fields are meant to show.

Here's an example of what I'm talking about doing it as a query though

Create a query with this SQL statement:

SELECT EmpClocking.ID, EmpClocking.OtherType, EmpClocking.CIN
FROM EmpClocking
WHERE (((EmpClocking.CIN) Between [enter the start date] And [enter the end date]));

Call this query qryTimeOffSummaryPre

then create this query:

SELECT qryTimeOffSummaryPre.ID, 10-[daysoff] AS WorkingDays, IIf(Count([OtherType])>10,10,Count([othertype])) AS DaysOff, Max(IIf([othertype]="OFF",[cin],Null)) AS LastOFFDate
FROM qryTimeOffSummaryPre
GROUP BY qryTimeOffSummaryPre.ID;

Call it anything you want.

I assumed your pay periods were 2 week increments because the data you supplied was about a 2 week period.

In the second query it lists the employee number, the number of days they worked (minus 4 days for weekends) the number of days they had off their most recent date that had OFF as the reason

This assumes you have a regular 5 day work week with 2 days off per week. This basic premise can be applied to whatever your situation is though.

[waqas]

no i want to calculate first last off date

for example one employee got his last off at 29-aug-11
after that he got sick leave 2 days at 03-sep and 04-sep
his last working date is 08-Sep-11

period from last off date = 30-Aug-11 to 08-Sep11 = 10 days
in this period 30-Aug-11 to 08-Sep11 he has 2 sick leaves
total worked days = 10 - 2 = 8 days

period should start from his last OFF date +1 means next date

upto last work day and in this period how many days he did not work
minus total period days

[rpeare]

You know what, I am just getting lost in what you are trying to say I have no idea what

i want to calculate first last off date

means.

How about you pick a few employees that are in your sample database and tell me what you EXPECT to see in a report for a given pay period. It would be far easier for me to understand what you want to see than you trying to describe it.

[waqas]

sorry my means to say first i want to get off date.

when he got last off.

for example 25-Aug-11 was his OFF then he worked upto today

in between this range he got two sick leave.

how many days he worked.

by the way i have build this one.

now i need to know how i can overwrite data on existing data in table by importing from excel. kindly guide me how can i import from excel and overwrite.