A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update queries



 
 
Thread Tools Display Modes
  #1  
Old October 12th, 2004, 04:31 PM
wheelz
external usenet poster
 
Posts: n/a
Default Update queries

I am trying to set up an update query to update 12 holidays for 20 people.
But this is my first time doing an update query. The user picks a holiday,
and based on that choice each person gets the holiday off and their number of
holidays given is displayed. The only fields on the form are the employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any suggestions
or hints to help me out.

Matt
Ads
  #2  
Old October 12th, 2004, 04:54 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

It's not what's on the form that matters, it's what's in the tables. When
you say 'update 12 holidays for 20 people', what does that mean in terms of
which fields you want to set to what values in which records of which
tables?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am trying to set up an update query to update 12 holidays for 20 people.
But this is my first time doing an update query. The user picks a
holiday,
and based on that choice each person gets the holiday off and their number
of
holidays given is displayed. The only fields on the form are the
employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any
suggestions
or hints to help me out.

Matt



  #3  
Old October 12th, 2004, 06:27 PM
wheelz
external usenet poster
 
Posts: n/a
Default

On the table there is a list of 20 names. Each name get every holiday and a
set number of holiday hours for it. I am trying to update the table so that
when the update query runs, every name get all 12 holidays. On the table I
want it to show the name, the holiday and their holiday hours for all 20
names, it should display all 12 holidays for all 20 names.

Matt

"Brendan Reynolds" wrote:

It's not what's on the form that matters, it's what's in the tables. When
you say 'update 12 holidays for 20 people', what does that mean in terms of
which fields you want to set to what values in which records of which
tables?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am trying to set up an update query to update 12 holidays for 20 people.
But this is my first time doing an update query. The user picks a
holiday,
and based on that choice each person gets the holiday off and their number
of
holidays given is displayed. The only fields on the form are the
employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any
suggestions
or hints to help me out.

Matt




  #4  
Old October 12th, 2004, 06:52 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

If I understand this correctly, you've answered two of the four questions to
which I would need answers in order to be able to help you. It seems that
you want to update all records in one table. But I still have no answer to
the other two questions - what values you want to put into what fields? Your
update query will look something like this ...

UPDATE YourTableName SET YourFieldName(s) = YourValue(s)

.... but given the information provided so far, there is no way for me to
know what "YourFieldName(s)" or "YourValues(s)" should be.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
On the table there is a list of 20 names. Each name get every holiday and
a
set number of holiday hours for it. I am trying to update the table so
that
when the update query runs, every name get all 12 holidays. On the table
I
want it to show the name, the holiday and their holiday hours for all 20
names, it should display all 12 holidays for all 20 names.

Matt

"Brendan Reynolds" wrote:

It's not what's on the form that matters, it's what's in the tables. When
you say 'update 12 holidays for 20 people', what does that mean in terms
of
which fields you want to set to what values in which records of which
tables?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am trying to set up an update query to update 12 holidays for 20
people.
But this is my first time doing an update query. The user picks a
holiday,
and based on that choice each person gets the holiday off and their
number
of
holidays given is displayed. The only fields on the form are the
employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any
suggestions
or hints to help me out.

Matt






  #5  
Old October 12th, 2004, 07:17 PM
wheelz
external usenet poster
 
Posts: n/a
Default

I am sorry, I don't mean to make this hard. I am teaching myself as I go.
The field names are :
EMP_NAME: employee's name
HOLIDAY: holiday
H_HRS: holiday hours (8,7,5,4.4, or 3.2)
H_DATE: holiday's date

The holidays a
New Years 1/1/04
Martin Luther King Jr. Day 1/19/04
Washington's Birthday 2/16/04
Memorial Day 5/31/04
Independence Day 7/04/04
Labor Day 9/6/04
Columbus Day 10/11/04
Election Day 11/02/04
Veteran's Day 11/11/04
Thanksgiving Day 11/25/04
Day after Thanksgiving 11/26/04
Christmas Day 12/25/04

Basically I want the table to look like this:

EMP_NAME HOLIDAY H_DATE H_HRS
Matt NewYrs 1/01/04 8
Jen NewYrs 1/01/04 7
Matt ID4 7/04/04 8
Jen ID4 7/04/04 7

but for all the holidays in the order the holidays occur during the year.

Matt
"Brendan Reynolds" wrote:

If I understand this correctly, you've answered two of the four questions to
which I would need answers in order to be able to help you. It seems that
you want to update all records in one table. But I still have no answer to
the other two questions - what values you want to put into what fields? Your
update query will look something like this ...

UPDATE YourTableName SET YourFieldName(s) = YourValue(s)

.... but given the information provided so far, there is no way for me to
know what "YourFieldName(s)" or "YourValues(s)" should be.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
On the table there is a list of 20 names. Each name get every holiday and
a
set number of holiday hours for it. I am trying to update the table so
that
when the update query runs, every name get all 12 holidays. On the table
I
want it to show the name, the holiday and their holiday hours for all 20
names, it should display all 12 holidays for all 20 names.

Matt

"Brendan Reynolds" wrote:

It's not what's on the form that matters, it's what's in the tables. When
you say 'update 12 holidays for 20 people', what does that mean in terms
of
which fields you want to set to what values in which records of which
tables?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am trying to set up an update query to update 12 holidays for 20
people.
But this is my first time doing an update query. The user picks a
holiday,
and based on that choice each person gets the holiday off and their
number
of
holidays given is displayed. The only fields on the form are the
employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any
suggestions
or hints to help me out.

Matt






  #6  
Old October 13th, 2004, 11:58 AM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

I don't think you can do what you want with an update query. An update query
changes values in existing records, but I think you probably need to add new
records that don't exist yet. You can do that with an append query, but
again, you need to tell the append query, in very precise terms, exactly
what values to put into each field.

I think you probably need to reconsider your database design. I can't be
completely sure, because I don't have enough information, but I suspect that
you probably need three tables rather than one, Employees, Holidays, and an
intermediate linking table, for example let's call it EmployeeHolidays, to
model the many-to-many relationship between employees and holidays (one
employee takes many holidays, one holiday is taken by many employees).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am sorry, I don't mean to make this hard. I am teaching myself as I go.
The field names are :
EMP_NAME: employee's name
HOLIDAY: holiday
H_HRS: holiday hours (8,7,5,4.4, or 3.2)
H_DATE: holiday's date

The holidays a
New Years 1/1/04
Martin Luther King Jr. Day 1/19/04
Washington's Birthday 2/16/04
Memorial Day 5/31/04
Independence Day 7/04/04
Labor Day 9/6/04
Columbus Day 10/11/04
Election Day 11/02/04
Veteran's Day 11/11/04
Thanksgiving Day 11/25/04
Day after Thanksgiving 11/26/04
Christmas Day 12/25/04

Basically I want the table to look like this:

EMP_NAME HOLIDAY H_DATE H_HRS
Matt NewYrs 1/01/04 8
Jen NewYrs 1/01/04 7
Matt ID4 7/04/04 8
Jen ID4 7/04/04 7

but for all the holidays in the order the holidays occur during the year.

Matt
"Brendan Reynolds" wrote:

If I understand this correctly, you've answered two of the four questions
to
which I would need answers in order to be able to help you. It seems that
you want to update all records in one table. But I still have no answer
to
the other two questions - what values you want to put into what fields?
Your
update query will look something like this ...

UPDATE YourTableName SET YourFieldName(s) = YourValue(s)

.... but given the information provided so far, there is no way for me to
know what "YourFieldName(s)" or "YourValues(s)" should be.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
On the table there is a list of 20 names. Each name get every holiday
and
a
set number of holiday hours for it. I am trying to update the table so
that
when the update query runs, every name get all 12 holidays. On the
table
I
want it to show the name, the holiday and their holiday hours for all
20
names, it should display all 12 holidays for all 20 names.

Matt

"Brendan Reynolds" wrote:

It's not what's on the form that matters, it's what's in the tables.
When
you say 'update 12 holidays for 20 people', what does that mean in
terms
of
which fields you want to set to what values in which records of which
tables?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am trying to set up an update query to update 12 holidays for 20
people.
But this is my first time doing an update query. The user picks a
holiday,
and based on that choice each person gets the holiday off and their
number
of
holidays given is displayed. The only fields on the form are the
employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any
suggestions
or hints to help me out.

Matt








  #7  
Old October 13th, 2004, 12:11 PM
wheelz
external usenet poster
 
Posts: n/a
Default

Thanks for the help Brendan, but I did find a way to do it with an update
query. All of the holidays are on a separated table that are accessed
through a drop down box by their respective dates. When the user clicks the
update button on the form, it runs the update query and stores the date and
holiday with each name. I then have a merge query running that takes all the
data and copies into the main table. This is then repeated for each holiday.
Thanks again for you assistance.

Matt

"Brendan Reynolds" wrote:

I don't think you can do what you want with an update query. An update query
changes values in existing records, but I think you probably need to add new
records that don't exist yet. You can do that with an append query, but
again, you need to tell the append query, in very precise terms, exactly
what values to put into each field.

I think you probably need to reconsider your database design. I can't be
completely sure, because I don't have enough information, but I suspect that
you probably need three tables rather than one, Employees, Holidays, and an
intermediate linking table, for example let's call it EmployeeHolidays, to
model the many-to-many relationship between employees and holidays (one
employee takes many holidays, one holiday is taken by many employees).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am sorry, I don't mean to make this hard. I am teaching myself as I go.
The field names are :
EMP_NAME: employee's name
HOLIDAY: holiday
H_HRS: holiday hours (8,7,5,4.4, or 3.2)
H_DATE: holiday's date

The holidays a
New Years 1/1/04
Martin Luther King Jr. Day 1/19/04
Washington's Birthday 2/16/04
Memorial Day 5/31/04
Independence Day 7/04/04
Labor Day 9/6/04
Columbus Day 10/11/04
Election Day 11/02/04
Veteran's Day 11/11/04
Thanksgiving Day 11/25/04
Day after Thanksgiving 11/26/04
Christmas Day 12/25/04

Basically I want the table to look like this:

EMP_NAME HOLIDAY H_DATE H_HRS
Matt NewYrs 1/01/04 8
Jen NewYrs 1/01/04 7
Matt ID4 7/04/04 8
Jen ID4 7/04/04 7

but for all the holidays in the order the holidays occur during the year.

Matt
"Brendan Reynolds" wrote:

If I understand this correctly, you've answered two of the four questions
to
which I would need answers in order to be able to help you. It seems that
you want to update all records in one table. But I still have no answer
to
the other two questions - what values you want to put into what fields?
Your
update query will look something like this ...

UPDATE YourTableName SET YourFieldName(s) = YourValue(s)

.... but given the information provided so far, there is no way for me to
know what "YourFieldName(s)" or "YourValues(s)" should be.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
On the table there is a list of 20 names. Each name get every holiday
and
a
set number of holiday hours for it. I am trying to update the table so
that
when the update query runs, every name get all 12 holidays. On the
table
I
want it to show the name, the holiday and their holiday hours for all
20
names, it should display all 12 holidays for all 20 names.

Matt

"Brendan Reynolds" wrote:

It's not what's on the form that matters, it's what's in the tables.
When
you say 'update 12 holidays for 20 people', what does that mean in
terms
of
which fields you want to set to what values in which records of which
tables?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


"wheelz" wrote in message
...
I am trying to set up an update query to update 12 holidays for 20
people.
But this is my first time doing an update query. The user picks a
holiday,
and based on that choice each person gets the holiday off and their
number
of
holidays given is displayed. The only fields on the form are the
employee's
name, holiday, holiday_hrs, holiday_date. Does anyone have any
suggestions
or hints to help me out.

Matt









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatic Update halchal9 General Discussion 1 September 30th, 2004 09:54 PM
Using MakeTable and UpDate Queries Sandra Grawunder Running & Setting Up Queries 4 September 22nd, 2004 07:49 AM
MS Jet --> MS Access Data Source: How to obtain Update lock André Hartmann Running & Setting Up Queries 1 July 16th, 2004 12:21 PM
update queries angie Running & Setting Up Queries 2 June 8th, 2004 03:11 PM
update query so slow VC Running & Setting Up Queries 4 June 7th, 2004 10:51 PM


All times are GMT +1. The time now is 06:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 OfficeFrustration.
The comments are property of their posters.