You don't. Rather you should have a Customer table with fields like Customer
ID as the primary key, CustFirstName, CustLastName, Address, City, etc.
Then you join this table with your Credit/Debit table on the Customer ID
field to get the customer's name and other data as needed.
You use of the word "cells" means that you are trying to "commit
spreadsheet". You need to think database and not spreadsheet.
I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
Jerry's right, but just in case you really do want to peform the kind
of update you're asking about...
I have a table that in some cases does not include all of the information in
cells that I need. There are multiple lines (one for debits, and one for
credits) for the same customer, but the customer name is not included in the
"credit" line, however, a customer ID number does appear in both lines. I
want to create an update query that will update that value in the "credit"
line to be the same as the "debit" line.
Here is an example:
Debit/Credit, Amount, Customer Name, Customer ID
Debit $15.00 Bill Smith 123456
Credit $15.00 123456
How can I update the "credit" line to include the customer name? Thanks!
First, I'm going to assume that you *don't* have a Customer table
where you can look up the name. So you need to get the name from a
corresponding Debit line.
First you need a query that returns just one Name from each Debit
record. A totals query Grouped Gy Customer ID, with First for the
Customer Name and a Where Debit/Credit = Debit should do the trick.
Call it qtotCustomerNameLookup.
Now create another query. Join qtotCustomerNameLookup with your
transaction table on the CustomerID field. Specify a criteria of
Credit for the Debit/Credit field. Now you have a way to lookup those
missing names.
Make a backup of your database.
Now change the query to an Update query. Update the Customer Name
field to the Customer Name from your lookup query. Run the query and
check to make sure all your transactions now have names.
If your update query says it's not updateable, then you'll need a
slightly different approach. Sometimes Access can't run an update
query when a totals query is joined in. Create a MakeTable query from
your lookup table and create a separate lookup table, then follow my
update directions using that table instead. When you're all done you
can delete the lookup table.
Hope this gets you started,
Armen Stein
Microsoft Access MVP
www.JStreetTech.com