Actually, my customer data is stored in my customer table and nowhere else.
I only want to retrieve that information for each order, and my limited
understanding of Access was that I did that through a form. After I print
out an invoice for the customer and a copy for my file I don't care any more
if the association of customer to item ordered is saved - the reports that I
will want to generate will be for types of books that are and are not selling
and when; my customer base will be large and although reorders from the same
person are nice, they're not expected, because my business is web based.
I've tried doing a sales form with a subform to select a customer, but that
doesn't work because the relationship is backwards - 1 customer could have
many orders, but each order will have only 1 customer. I tried doing a
customer form and then adding in a sales subform so that I could select the
items, price, date sold, etc., but I couldn't get that to work, either. I've
been at this for more than a week now, and I've run through every book on
Access I have (7), gone through every self-paced course Microsoft offers,
have looked at MS's sample databases and at Northwinds, and I'm just about to
the point of going back to my spreadsheets even though it will mean
duplicating information in several places. What I have is 1 customer could
place 1 or more orders for 1 or more items, but I want to create an invoice
for each order showing each item ordered (1 - ???), date, price paid,
shipping charges, and then have a command button that will print out 2 copies
of the invoice - 1 will ship with the order, 1 will stay with my files. (I
know - I should be getting away from paper filing systems, but I don't trust
computers - not even backups!)
I can get the customer's name or ID to show up on a sales form with the
items, but I can't figure out how to get all the customer's information
without manually typing it in, and then there's the problem of typos.
Allen and Tom - I haven't been able to fully digest your replies yet, so
maybe when I'm fresh tomorrow this will make more sense. If so, I'll let you
know. If not, I'll just keep digging.
Thanks for your help!
Tina
Allen Browne said:
Oaky, that's great. You don't need to store the address in the orders at
all.
Now, presumably you want to print out the order and show the home address if
ShipToResidence is checked, or the mailing address if the box is not
checked. On your report, you will have text boxes that have this kind of
thing in their Control Source property:
=IIf([ShipToResidence], [ResidenceAddress], [MailingAddress])
and then on the next line another text box:
=IIf([ShipToResidence], [ResidenceCity], [MailingCity])
The actual content will depend on your field names.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Tom Lake said:
What I do is have both a residence address and mailing address in the
Customer table then on each transaction I have a yes/no field asking Ship
to residence? If the flag is Yes, I use the residence address from the
Customer table, if no, I use the mailing address. Then I don't have to
store address data in the transactions (orders) table.