Button to create a new table?

[vidus]

Hello,

I am wondering if there is anyone who can help with some vb for a command button, that when pressed will create a new table with a few pre-set columns?

Also, can the new table be named after the [Job] field (4 number name) from the record in view?

Please I hope someone can help! I have researched for hours but I cant find anything that seems to work.

 

[boblarson]

Okay, I'm going to have to be a bad guy here, but why are you needing to do this? Normally questions like this lead us to the conclusion that your database is NOT properly designed nor properly normalized.

 

[ghudson]

I advise that you do not create a table based on a job name in a record. It appears you are going against the theory of database normalization.

 

[vidus]

Okay, I'm going to have to be a bad guy here, but why are you needing to do this? Normally questions like this lead us to the conclusion that your database is NOT properly designed nor properly normalized.

Simple because when our workers start a job from our Job Board, we have a whole new set of information that we want to keep track of, such as inventory used, certificates, shipping info etc..

Open to suggestions though, but this seems like a good way of doing it no?

 

[vidus]

I advise that you do not create a table based on a job name in a record. It appears you are going against the theory of database normalization.

What would you suggest I do then?

 

[boblarson]

Simple because when our workers start a job from our Job Board, we have a whole new set of information that we want to keep track of, such as inventory used, certificates, shipping info etc..

Open to suggestions though, but this seems like a good way of doing it no?

Nope, not a good idea. You don't add tables for data. You create a good, solid table structure which will accomodate adding a new job by adding RECORDS not tables.

I think we need to start from the basics and get your table structure in order first. Also, you should read up on normalization. This is what will keep your data integrity as well as not store redundant data. And a further benefit is that getting meaningful data OUT (which is why I assume you want to put it in) is WAY easier.

 

[vidus]

Nope, not a good idea. You don't add tables for data. You create a good, solid table structure which will accomodate adding a new job by adding RECORDS not tables.

I think we need to start from the basics and get your table structure in order first. Also, you should read up on normalization. This is what will keep your data integrity as well as not store redundant data. And a further benefit is that getting meaningful data OUT (which is why I assume you want to put it in) is WAY easier.

Thanks for the heads up. Can you give me any advice of what to do? We have 10 columns on a job board, each job has a row with some basic info.

When a job is started, we need to keep track of a new set of info which also needs columns to be organized... what are my options? How can I do this in the same table?

 

[boblarson]

Thanks for the heads up. Can you give me any advice of what to do? We have 10 columns on a job board, each job has a row with some basic info.

When a job is started, we need to keep track of a new set of info which also needs columns to be organized... what are my options? How can I do this in the same table?

Okay, so can you upload a blank database with a copy (empty) of your current table structure? Then, we can go through some questions after we see what it is you currently have.

 

[vidus]

Okay, so can you upload a blank database with a copy (empty) of your current table structure? Then, we can go through some questions after we see what it is you currently have.

I dont have access to it right now unfortunately. But it's very simple, columns include JobNumber, Customer, Description, DueDate etc... And there is also a split form that we use with some basic functions and command buttons with a few queries.

 

[boblarson]

So, when you do have access to it, it would be very helpful to get a copy so we can work out the best solution for you. And don't feel bad - it happens a lot here like this. But we are not about giving out "band-aid" solutions. We want to give you the best information we can, as well as helping you learn best practices, and therefore giving you a result which will last for a long time without you, or anyone else, getting mad at the database when you come across another fix you need to make because it wasn't designed properly.

 

[vidus]

So, when you do have access to it, it would be very helpful to get a copy so we can work out the best solution for you. And don't feel bad - it happens a lot here like this. But we are not about giving out "band-aid" solutions. We want to give you the best information we can, as well as helping you learn best practices, and therefore giving you a result which will last for a long time without you, or anyone else, getting mad at the database when you come across another fix you need to make because it wasn't designed properly.

Perhaps tomorrow I can clear a copy out for you. Ill see what I can do!

 

[boblarson]

Perhaps tomorrow I can clear a copy out for you. Ill see what I can do!

Oh, and if you want to clear out a copy really quickly and easily, you can download and use my free Database reset tool, available here.

 

[vidus]

Oh, and if you want to clear out a copy really quickly and easily, you can download and use my free Database reset tool, available here.

I will give that a try. Will you be around tomorrow?

 

[boblarson]

I will give that a try. Will you be around tomorrow?

There's a good chance I will (barring any unforeseen things popping up).

 

[vidus]

There's a good chance I will (barring any unforeseen things popping up).

Hey boblarson. Here is our blank. So basically what we do is quote people on jobs in 2010 Form. If a quote is accepted, we hit the button Job Accepted and it moves to the Job Board form and is viewable to the office as an active job.

I do realize there is repeated field names between these two tables but this is only because there is a ton of jobs we quote that never get accepted.

So we need a way to track more info for these specific jobs. My idea was to create a small table for each active job, and have it deleted when the job is invoiced and finished...

Thanks for looking... appreciate it.

 

[ghudson]

I would add Yes/No fields in your table to track if a job is accepted, added, invoiced, etc. Usually you do not delete records but it always depends on the situation.

 

[boblarson]

Hey boblarson. Here is our blank. So basically what we do is quote people on jobs in 2010 Form. If a quote is accepted, we hit the button Job Accepted and it moves to the Job Board form and is viewable to the office as an active job.

I do realize there is repeated field names between these two tables but this is only because there is a ton of jobs we quote that never get accepted.

So we need a way to track more info for these specific jobs. My idea was to create a small table for each active job, and have it deleted when the job is invoiced and finished...

Thanks for looking... appreciate it.

What's the difference between Description and Job in the 2010 table?

 

[vidus]

What's the difference between Description and Job in the 2010 table?

No difference. I know there is repeated fields. Its just organized better this way... I might change it in the future. Everyone just like the quotes and active jobs separated.

 

[boblarson]

No difference. I know there is repeated fields. Its just organized better this way... I might change it in the future. Everyone just like the quotes and active jobs separated.

That's why I asked, because it should NOT be that way. "Everyone" needs to learn how to use a database and we're attempting to help you out here. Keeping quotes and active jobs separated is NOT an appropriate way to handle this. As ghudson mentioned, you mark them active or not. You do not give users access to the tables so they should never need worry about what goes on under the covers. You can provide them QUERIES with Forms to give them what they need.

Does that make sense?

So, anyway, I was asking as I'm looking trying to understand what we need to keep and what we don't. Job Description would be what I would use and will use in my example, if they are the same thing.

 

[boblarson]

So next question -

You have Shipping Colour in the Job Board table. What does that mean?

(so still analyzing)