Is this possilbe??

moleary

Senior Member
Local time
Today, 21:29
Joined
Feb 9, 2001
Messages
58
I have a intke table in which I have a intake form that we enter new clients on, but I also have another table which is the "master" table for all clients, now on the intake form there is only about 10 fields that need to be in the "master table" how can I make it so that these fields will populate the "master table at the same time??? Or is this not possilbe?
 
What you are asking is possible, but sounds like poor database structure. Why have the intake table? If the information is in the Master Table (or should be), why not just extract that information from the master table?
 
Well some of the information is in the master table but alot of it isn't and we don't track that information right now so I didn't include it in the table. Is there an easier way to do this or should I just include all of the stuff on the intake form into the master table?
 
Are there records in the master table that are NOT in the intake table? If so, how did they get there?

It sounds to me like all of your information should be in the intake table (which would then become your "master table"). There is never any reason to store the same information in two places.

Why do you want to track more information for some records (i.e. your current "intake table") and not for others (i.e. your current "master table"). Just curious.

It sounds like your solution will be one of the following:

1. (more likely) Combine all of your records into one table and simply filter the records accordingly in forms/reports/etc.

2. (less likely) Keep the information in separate tables permanently and use a union query to join the two tables when needed.

Let me know more about the information you are tracking and why you need two different tables.
 
Okay let me explain our tracking, when we get a new client we fill out a intake form, and we track these for production purposes, then when that client is actually approved to be a client we start tracking a whole bunch of other stuff that is not the intake form, see the intake form just gives us basic information, and this was a paper form filled out by hand now they want it on a form in access that will automatically populate the database....see I either need to figure out how to "move" the client from intake status to the "master table " when they actually become a client or update at the same time what ever is easier??
 
It is possible to keep the information separate, and upon a certain event, move them to the master table and delete them from the intake table. You could do this with append and delete queries or through VBA.

However, I would recommend putting all of the info in the same table and simply having a yes/no field to indicate whether the record (person) has become an official client. Then you could have two forms, one that pulls all of the "intake" people (Client field = false) with just the 10 fields that you want to track for them. And one that pulls all of the "master" people (Client field = true) with all of the fields represented.

Then, upon making someone an official client (checking the check box would be the easiest way, depending security), the client field turns to True and you view them in the second form with the entirety of the fields in the table.

Another option would be to have one form and make the controls for all of the extra fields hidden or visible depending on the Client Yes/No field.

HTH

[This message has been edited by shacket (edited 09-21-2001).]
 
Okay if I wanted to flag it at a certain date how can I move it to the master table upon that date? But I don't really want to delete the information in the intake table but hide it per say??
 
Something in me just needs to express the disclaimer that I don't think this is the best way to manage your information. (*Whew* - now that that's off my chest...)

smile.gif


You would put a field in the intake table called "DateToMove" (or something like that) and place your date there.

You would then run an append query to add all records with "DateToMove" before or equal to today's date to the master table.

Because you want to keep the record in the intake table and not add it more than once, you would need to do either of the following:

1. Cross reference with the master table to add only records from the intake table that do not already exist in the master table

2. Add another field in the intake table (yes/no called "Moved") to indicate whether it has been moved to the master table. After running the append query, run an update query to mark the Moved field in all records where DateToMove is less or equal to today's date as true. (Your append query would then add records whose DateToMove is less than or equal to today's date AND where Moved is false)

The latter may work better for a number of reasons:

1. You will likely have primary key issues to compare the information in both tables.

2. To "hide" the record in the intake table, it would be easiest to have that "Moved" field. Then your intake form could retrieve all records in the intake table where Moved = false to "hide" those that have been moved.

[This message has been edited by shacket (edited 09-21-2001).]
 
I realize that this is not the best way to managage our records but we are slowly trying to straigten this mess out and make it work properly but I really needed a quick fix for now and I really appricaite your help with that!!!
smile.gif
smile.gif
 
Another question for you moleary:
Do you need to keep those entered into the intake table and those transfered to the "master" table separate, or do you keep the info in the intake table even if a potential client doesn't become an official client?

If you hold onto all the information, why not just use a query for the intake data (which would be a subset of themaster data)?
 

Users who are viewing this thread

Back
Top Bottom