Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Query - another stumper

Status
Not open for further replies.

Mich2too

Technical User
Jan 27, 2001
103
US
Using Access 2000. Am familiar with using update queries, but not sure how to enter the information to be updated into the query.

Fields to be worked with:
Member table - Membership#, Dues Next Due date
Dues Payment table - Date dues payment recorded
Dependant table - Next Due

Need to update the Dependant table Next due field with the data entered in Member Dues Next Due field for the day that dues were recorded from Dues Payment Table.

Have the criteria portion down pat, but need to know how to enter the Update To portion into the Dependant Next Due field from the Member table field.

Am unfamiliar with VB or SQL.

 
First, I have to ask "why?" If the Next Due Date value already exists in one table, why do you need to duplicate it in another?

Ok, ok... If you really must:

Using the QBE grid (design view for a query) -
1. Add the Dependent table to the grid.
2. Add the Master table to the grid.
3. Change the query type to "Update Query"
4. Place the Dependent table's Next Due Date field in the grid
5. Update to MasterTable.NextDueDate or whatever the field is named.
(note that the tables must be related, maybe through the Membership # field)

or in SQL view -
Code:
UPDATE DependentTable 
INNER JOIN MasterTable
ON DependentTable.ID = MasterTable.ID 
SET DependentTable.NextDue = MasterTable.NextDue;

(Replace DependentTable, MasterTable, etc... with your actual table / field names)

Hope this is helpful :)
 
Why update???? Well...how about because our members are IDIOTS and won't read anything sent with their membership cards before they flip out and report me to my bosses for lack of competance because the dependant's membership cards don't have expiration dates on them when they are printed?? I've already had one dummy return a membership card through my boss and have a write up with it "demanding" that this be fixed and that we have to eat the cost of the card!

When orders are received for dependant membership cards, they are entered into the dependant table. This is the table that is used to print the membership cards. Know it would be easier to update this field from the primary member's record from the primary table then to individually sift through and update them by hand. If the field is empty the card will print blank, and if the field is inaccurate, my bosses will know about it before the ink is dry on the cards because of the member screaming at the top of their lungs.

Ain't customer service GREAT???? NOT!!! LOL

Thank you...it has helped!! Any suggestions for good SQL reference books? My advanced access class definately left off where I need to learn more.
 
recommend SQL Queries for Mere Mortals although it seems to be a bit lacking on update (and other action) queries, it is very good for overall SQL syntax and JOIN explanations...

2nd choice: Access itself (Yep, make a query in the QBE grid and check out the SQL Access churns out)
 
Thanks for the recommenations. Will do some hunting at bookstores for it VERY soon!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top