Request help

[Khalil Islamzada]

Dear all;

I just searched a lot in internet but couldn't find what I want, so I want to share it with you and hope you can help me.

I am developing an access database as follow;

1. I have table called "customer info"
2. another table called "customer type"
3. A table for billing


Now, in the first table "customer info" i have a field called customer type that gets its data from "customer type" table.

I have a query for billing that up to here everything is normal. now my problem is that some of the customers are sometimes change in term of their type for example from household to commercial, so If i change the customer type from customer info table all the history of billing will change while I want the history remain as it was.

I have uploaded the DB for more info.

Please, waiting for your ideas.:banghead::banghead::banghead::banghead::banghead::banghead:

 

[Ranman256]

The customer type MUST get saved with the billing because of this.
The tBilling table would have
CustID, BillType, Date,OrderID

To preserve the Cust type AT THAT TIME.

 

[Khalil Islamzada]

Many thank for your reply Ranman256:

Can you please guide me how to do it?


Thanks
Khalil

 

[arnelgp]

you add a customer type field in your billing table.
this has to be same datatype as in your customer info table.

now update this field using update query:

update [billing table] as t1 inner join [customer table] as t2 on t1.[customer id]=t2.[customer id] set t1.[customer type] = t2.[customer type];

you'll also have to change your form (billing), so it will save the customer type to the billing table.

 

[Khalil Islamzada]

Hi Arnelgp:

Thanks, but can you do it in my attached database and upload it, because I did but couldn't get result,

Thanks for your help.

 

[arnelgp]

here, i added CustType to billing table. also made a form Billing.