Update Query - ANSWER NEEDED, HELP

[Lori]

I am trying to create a simple update query and for some reason I can't get
it to do anything!!!

I am using Office 2003.

Okay, I'm dealing with a total of 3 tables.
The Timesheet Hours table is completed by Individuals entering in time on a
timesheet form. In an employee table I have billing rates. The Jobsite table
has budgets for salaries and right now an empty field called "Salaries
Billed".

I have a query that calculates the number of hours billed to a specific job
multiplied by the billing rate. I've created a second query (because access
kept giving me an error when I tried to use an expression) that provides the
Job # and the Total Billed. Now, my update query pulls in the "Salaries
Billed" field from the Jobsite Table and is supposed to "Update To" Total
Billed but it either gives me a message stating that my query needs to be
updateable or it give me nothing. What am I doing wrong?

I've posted the sql but as you can see its very simple.

UPDATE Jobsites SET Jobsites.[Salaries Billed] = [qrySalaryTotals]![Bill];


HELP!!

 

[tkelley via AccessMonster.com]

I'm interested to see if a guru has a fancier answer. But to get you by
until then:

Try dumping into a table using a make-table query first. Then do what you're
doing below using the table instead of the query.

I am trying to create a simple update query and for some reason I can't get
it to do anything!!!

I am using Office 2003.

Okay, I'm dealing with a total of 3 tables.
The Timesheet Hours table is completed by Individuals entering in time on a
timesheet form. In an employee table I have billing rates. The Jobsite table
has budgets for salaries and right now an empty field called "Salaries
Billed".

I have a query that calculates the number of hours billed to a specific job
multiplied by the billing rate. I've created a second query (because access
kept giving me an error when I tried to use an expression) that provides the
Job # and the Total Billed. Now, my update query pulls in the "Salaries
Billed" field from the Jobsite Table and is supposed to "Update To" Total
Billed but it either gives me a message stating that my query needs to be
updateable or it give me nothing. What am I doing wrong?

I've posted the sql but as you can see its very simple.

UPDATE Jobsites SET Jobsites.[Salaries Billed] = [qrySalaryTotals]![Bill];

HELP!!

 

[Lori]

Didn't help, its strange, there are 5 jobs that I am billing with my test
data and the query looks at the jobs and gives me 5 blank lines almost like
it's doing 1/2 the job. Any other ideas?
--
Lori A. Pong

I'm interested to see if a guru has a fancier answer. But to get you by
until then:

Try dumping into a table using a make-table query first. Then do what you're
doing below using the table instead of the query.

I am trying to create a simple update query and for some reason I can't get
it to do anything!!!

I am using Office 2003.

Okay, I'm dealing with a total of 3 tables.
The Timesheet Hours table is completed by Individuals entering in time on a
timesheet form. In an employee table I have billing rates. The Jobsite table
has budgets for salaries and right now an empty field called "Salaries
Billed".

I have a query that calculates the number of hours billed to a specific job
multiplied by the billing rate. I've created a second query (because access
kept giving me an error when I tried to use an expression) that provides the
Job # and the Total Billed. Now, my update query pulls in the "Salaries
Billed" field from the Jobsite Table and is supposed to "Update To" Total
Billed but it either gives me a message stating that my query needs to be
updateable or it give me nothing. What am I doing wrong?

I've posted the sql but as you can see its very simple.

UPDATE Jobsites SET Jobsites.[Salaries Billed] = [qrySalaryTotals]![Bill];

HELP!!

 

[tkelley via AccessMonster.com]

Are you talking about this query: qrySalaryTotals

Can you post the SQL?

Didn't help, its strange, there are 5 jobs that I am billing with my test
data and the query looks at the jobs and gives me 5 blank lines almost like
it's doing 1/2 the job. Any other ideas?

I'm interested to see if a guru has a fancier answer. But to get you by
until then:

[quoted text clipped - 26 lines]

 

[Lori]

the qrySalaryTotals works fine, its when I try to use it to update the
Jobsite Table that I have a problem. Here is the SQL

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

I'm using the "Bill" field to update the "Salaries Billed" field in the table.

Ideally, what the boss wants done is a way for the people to enter in their
time, before it saves the hours it needs to look at the "Salaries" budget in
the table, compare it to the Salaries billed and pop up with a message if
there isn't enough money left for them to bill their time to a specific job.

All help is appreciated.
--
Lori A. Pong

Are you talking about this query: qrySalaryTotals

Can you post the SQL?

Didn't help, its strange, there are 5 jobs that I am billing with my test
data and the query looks at the jobs and gives me 5 blank lines almost like
it's doing 1/2 the job. Any other ideas?

I'm interested to see if a guru has a fancier answer. But to get you by
until then:

[quoted text clipped - 26 lines]

HELP!!

 

[tkelley via AccessMonster.com]

So when you turned this:

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

into a make-table query (called NewTable here) by doing this:

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
INTO NewTable
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

then you did this:

UPDATE Jobsites SET Jobsites.[Salaries Billed] = [NewTable]![Bill];

that still didn't work?

As far as reaching your ideal, there may be a better way. But for now, I'm
curious about why that make-table query approach didn't work.

the qrySalaryTotals works fine, its when I try to use it to update the
Jobsite Table that I have a problem. Here is the SQL

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

I'm using the "Bill" field to update the "Salaries Billed" field in the table.

Ideally, what the boss wants done is a way for the people to enter in their
time, before it saves the hours it needs to look at the "Salaries" budget in
the table, compare it to the Salaries billed and pop up with a message if
there isn't enough money left for them to bill their time to a specific job.

All help is appreciated.

Are you talking about this query: qrySalaryTotals

[quoted text clipped - 8 lines]

 

[John W. Vinson]

I am trying to create a simple update query and for some reason I can't get
it to do anything!!!

I am using Office 2003.

Okay, I'm dealing with a total of 3 tables.
The Timesheet Hours table is completed by Individuals entering in time on a
timesheet form. In an employee table I have billing rates. The Jobsite table
has budgets for salaries and right now an empty field called "Salaries
Billed".

I have a query that calculates the number of hours billed to a specific job
multiplied by the billing rate. I've created a second query (because access
kept giving me an error when I tried to use an expression) that provides the
Job # and the Total Billed. Now, my update query pulls in the "Salaries
Billed" field from the Jobsite Table and is supposed to "Update To" Total
Billed but it either gives me a message stating that my query needs to be
updateable or it give me nothing. What am I doing wrong?

I've posted the sql but as you can see its very simple.

UPDATE Jobsites SET Jobsites.[Salaries Billed] = [qrySalaryTotals]![Bill];

You can't just include some other arbitrary query (or table) in an UPDATE
statement - you must include both the table you're updating *and* the query or
table providing the value in the query, with an appropriate join.

HOWEVER... a Totals query (such as you have here in qrySalaryTotals) is never
updateable. Unless you have a *demonstrated* (rather than assumed) need to do
so, say for performance reasons, you should generally not store calculated
fields -such as totals - *at all*. If you add a new record, or delete a
record, or change a salary in the underlying table, then your [Salaries
Billed] field will be WRONG, with no easy way to determine that it's wrong,
other than wiping out the field and rerunning the update query! Just base your
salaries report directly on a Totals query summing the values on the fly.

 

[Lori]

The one I did had a error in it apparently but when I copied the one you did
it worked perfectly, thank you for that. Any ideas on how to get a form to
run the query before it saves data?

The way they want this written is: have individuals enter in their time for
each specific project then before it saves check the budget to make sure that
there is enough money in the bank before going further. I've already written
an IIF based on the Make Table then Update queries that closes the project if
the $$ drops too low. I'm just having issues with running the query each line.
--
Lori A. Pong

So when you turned this:

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

into a make-table query (called NewTable here) by doing this:

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
INTO NewTable
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

then you did this:

UPDATE Jobsites SET Jobsites.[Salaries Billed] = [NewTable]![Bill];

that still didn't work?

As far as reaching your ideal, there may be a better way. But for now, I'm
curious about why that make-table query approach didn't work.

the qrySalaryTotals works fine, its when I try to use it to update the
Jobsite Table that I have a problem. Here is the SQL

SELECT [qrySalary Billing for Update].ProjectID, Sum(([qrySalary Billing for
Update]!SumOfBillableHours*[qrySalary Billing for Update]!Rate)) AS Bill
FROM [qrySalary Billing for Update]
GROUP BY [qrySalary Billing for Update].ProjectID;

I'm using the "Bill" field to update the "Salaries Billed" field in the table.

Ideally, what the boss wants done is a way for the people to enter in their
time, before it saves the hours it needs to look at the "Salaries" budget in
the table, compare it to the Salaries billed and pop up with a message if
there isn't enough money left for them to bill their time to a specific job.

All help is appreciated.

Are you talking about this query: qrySalaryTotals

[quoted text clipped - 8 lines]

HELP!!

 

[tkelley via AccessMonster.com]

I'm not sure what your form looks like, whether it's connected or
disconnected, whether it has a subform, etc.

I would have approached it differently to begin with, so it's difficult for
me to make a suggestion for you. But based on my understanding of what
you're doing, I'll describe what I'd have done. Then maybe you can pull an
idea or two from that.

I like to use disconnected forms, then do all my data manipulation upon the
user clicking on a [Submit] button. That way I can do all kinds of rule
checking, etc.

I would have a listbox that listed the available projects using a query, that
the user would use to select their project. That listbox would have a column
in it that would hold the number of available dollars. That way I *think*
you could just use a totals query to pull your ProjectID and your
SumOfSalaries for that project to fill your listbox. I think that would
render your Jobsites.[Salaries Billed] field unnecessary and you avoid doing
what John Vinson was talking about regarding storing values that can be
calculated using other fields. (I happen to agree with him--I haven't been
on this site long, but it is clear that he is one of the uber-gurus.)

Once the user clicked [Submit], I would have checked that column to see if
their entered value is > the value in the column. If so, I could prompt them.
If it was okay, then I would fire the action queries to append the new data.
Then I would requery the listbox which would pull the new value of available
dollars. This assumes you don't have a bunch of users who would be appending
to the same project simultaneously, or near-simultaneously.

Anyway, I hope that can be at least a little helpful and you can maybe re-
think a couple of things and have time to optimize some of your methodology.
I optimize and learn something new with every project--even inside the same
application.

Otherwise, I don't know enough about what you've done so far to give you a
good answer on where to go next.

The one I did had a error in it apparently but when I copied the one you did
it worked perfectly, thank you for that. Any ideas on how to get a form to
run the query before it saves data?

The way they want this written is: have individuals enter in their time for
each specific project then before it saves check the budget to make sure that
there is enough money in the bank before going further. I've already written
an IIF based on the Make Table then Update queries that closes the project if
the $$ drops too low. I'm just having issues with running the query each line.

So when you turned this:

[quoted text clipped - 41 lines]