Moving between tables

[tostgulen]

Hi



I'm new to Access so please bear with me on this. I'm creating a database for my finance's business. Shes starting up in a couple of months and have acquired a long list of potential customers. My plan was to have two separate customers tables. One for potential customers and one for existing.


The list of potential customers have been imported in one table and I have made a form she can use to view the info and comment on them wile talking to them. If some of the P.C.'s wants to buy her product I would like to have a button on the form that moves this P.C. to the table of existing customers and at the same time deletes them form the P.C. table.

Is this possible? If it is, could someone please help me on the right track?

[JoeM]

I think a better way of going about would be to have a single table, and just have a flag for existing/potential customer. Then there is no reason to move any data, just update the flag value.

[tostgulen]

Ok. I will see if I can make that work.

But I still wonder if there might be a possibility to move entities from one table to another. I can probably get VBA to do the job, but that requires a bit of research since I haven't used VBA.

[JoeM]

Oh, you can do it. But it seems to be totally unnecesary and isn't really good database design.
Why you would want to do it that way? If we understand what the logic is, we may be able to make some suggestions.

[tostgulen]

Great.

The list of P.C.'s is bought form the governments records of companies in a specific geographical area. With well over 4000 entities and a plan to expand the list as time goes it can be a challenge to keep track of existing and potential customers. With a flag as you mentioned I will be able to keep track of who I have made contact with an order table I will be able to keep track of previous customers. I would like to have all active and existing customers in one table to have control over the support aspect and follow up of customers. The firm sells first aid courses and life support equipment. It is important to keep always know when customers last had a course. Some professions are required by law (in Norway) to have CPR courses one a year. To keep a good record of the last time they had a course, it will be far easier to remind customers that they need to refresh they CPR knowledge. It's all about the sales

Again, I started learning access yesterday, so I don't know all the tricks

[JoeM]

Again, I started learning access yesterday, so I don't know all the tricks

Ah, yes. This can be a bit tricky. Access is not nearly as intuitive as Excel, and if you try to design a database in Access without understanding the concepts of relational databases and normalization, you could be in for a world of heartache. It is best to learn as much as you can about these things and Access before diving in and trying to create databases. Otherwise, you could end up programming yourself into a corner (been there, done that). Here's a good link to get you started: http://www.deeptraining.com/litwin/d...aseDesign.aspx

Just a note on your issue at hand. Many "newbies" are under the mistaken impression that they would need two tables in your circumstance to separate the two. That is not the case. You seldom ever want to create too nearly identical tables and try to move data back and forth between the two. Just create a single table, like I mentioned, and then use queries to separate it into two lists. Most queries are updateable (if designed correctly) and can be used for almost anything that you use tables for (source of Forms, Reports, exports, etc). You simply would create two queries, one that has criteria where you flag is set to "Yes", and the other where it is set to
"No". Then, by changing the value of the flag, the record will then automatically show up in the other query.

[tostgulen]

Ok. I will look try that.

Thank you so much for the help. Happy new year to you!