This is either really easy or really complicated -- possible?

[Access9001]

Background info:
Cust_ID represents the id number of a customer, Branch is the general location (e.g. "Dallas"), Representative is the person assigned to that customer, and Address is the customer's address. SendMail, SendEmails, and SendCalls are boolean values that basically ask "Is it OK to send mail to this customer?", etc.

I basically want to be able to have Representatives be able to pull up all customers by branch (this is easy enough to do) in order to check/uncheck corresponding Send variables. So for instance, say John Doe pulls up customers in Dallas and sees the following:

Cust_ID, Branch, Representative, Address, SendMail, SendEmails, SendCalls
12345, Dallas, John Doe, Sunny Lane, 1, 0, 1

Where the rep can edit SendMail, SendEmails, and SendCalls for that particular Cust_ID.

My questions:

Is it possible to create a process that does the following:

1. Takes a source table, Customers, which has Cust_ID, Branch, Representative, and Branch -- and have a Make Table that also appends blank fields SendMail, SendEmails, and SendCalls as yes/no fields? I have to store the Send data in a table somewhere, obviously.

2. Can I update the result of my Make Table by changing Representatives when they are updated through the Customers table?

3. Can I also append any new results? I say append because re-making this table would wipe out the Send data that the Representatives enter/modify.


Apologies if this is actually a really hard question -- but I have no idea how to do this elegantly.

 

[Galaxiom]

Why do you want to make a separate table? Why not simply edit the records in the main table via a form that has its recordsource selected by branch?

BTW I suggest you normalize the branch value to an integer.

 

[Access9001]

Because the Send fields are new fields that don't currently exist in Customers, and the Customer table is already being used in a ton of other integrations/manners and I'd rather not potentially botch something. Customers itself is a part of a maketable process and I don't know how to put yes/no values in maketables.

 

[Galaxiom]

OK. Your new table will be a one-to-one relationship with the other table.
Join them on CustomerID on your form recordsource query and select by Branch.

However I don't understand why customers is a MakeTable query.

 

[Access9001]

I don't either, but it's beyond my control -- hence my inability to figure out a good way to handle this.

I know that the new table would basically include the relevant data from Customers in addition to new, defaultly-blank Send fields.

The problem is how to properly append/modify this table. I can probably make the initial set manually.

 

[Galaxiom]

The only way I know to include a Boolean in a MakeTable query is to insert the field from an existing table. Add the seed table with the fields and one record having values you want as default in the new table.

 

[boblarson]

As stated already - create a table once and then just clear it out and fill it again. Then it can have whatever you decide. As long as fields don't come and go (which in a properly designed database that would be very rare) you should be fine.