Appending new records to a table

[Guest]

Hi,

I have a Customer table that has fields such as CustomerID, CustomerName,
ActiveClient, DateModified, Address, CustomerType etc. I also have field
called CustomerLicenses that has CustomerID, CustomerName, ActiveClient and
then some other field that are not in the customer table. Every day we add a
few new customers to the database or we may change the status of a customer
from Active to inactive. The ActiveCient field is a yes/no field. What I
want to do is to run an append query that will add new customers to the
CustomerLicenses table. Also, for existing clients whose status has changed
from Active to Inactive (-1 to O), I want to have the ActiveClient field
changed in my CustomerLicenses field. Can someone help me with this append
query?

Thanks,

 

[RBear3]

Why are you duplicating data in two tables? That defeats the purpose of a
RELATIONAL database. Instead, build a relationship between the two tables
and only store data in one table.

To create a record in both tables when you add a new customer, simply
include the "CustomerID field in your form. Include an "after update" event
that copies the number to an invisible field in the same form that is linked
to the Customer ID field in the related license table (assuming you will
always have a one-to-one relationship. Or, better yet, simply include the
license field in your main table and cut it down to one table.

If this is not a one-to-one relationship, then you would include a subform
in your main customer form. In the subform, you could record one or more
licenses for the customer being viewed.

Post back if you need more help. In short, you need to get your data
normalized.

 

[Guest]

Just to give you some background, I use a program called Access Books which
takes data out of Quickbooks and places it into an access database. The main
purpose of this access database is to provide sales reports to managers. I
run a scheduled task every night that refreshed the tables in a batch mode so
there is no imput form for adding the customers. I cannot add fields to the
original tables that access books creates including the Customer table.
However, I can add additional tables which I often do for people who work in
our customer service department. I want to create a form that lists all
customers in the database and then has some check boxes. We want to know if
our customers have various licenses. I can copy the customer table which has
the CustomerNumber and their Active status and create a new one called
CustomerLicenses. I am not sure how to add new customers or look for the
status change (i.e. we sometimes make a customer inactive which changes the
value of the CustomerActive field from -1 to 0). I was thinking there would
be some sort of append query that I could create.

Thanks,