Create New Project and duplicate entire table with new name????? Access 2007-2016

[Blackkimba851]

I have a table that has (Project Name),(Room),(Task),(Status). There are about 250 Tasks per Project that are the same for each Project. Just the name and the status will change in the table. How do I add a new Project and create a new table or add to a table with all the rooms and tasks the same but with a new Project name and the status set to (By Default) Incomplete. All Projects will have the same Data in them (Room,Task). I would like to do this with a "Create New Project" Button Macro, Enter name an then have it create the new table or add to a table.


I hope I explained it the right way
Any ideas

[pbaldy]

You haven't mentioned your structure. You almost certainly don't want another table. Perhaps:

http://allenbrowne.com/ser-57.html

[June7]

Do you have a Tasks table that has these 250 tasks? If not, then pick a project to copy. Consider:

CurrentDb.Execute "INSERT INTO ProjectTasks([Project Name], Room, Task, Status) SELECT '" & Me.textboxname & "', Room, Task, 'Incomplete' FROM ProjectTasks WHERE [Project Name]='some project to copy'"

Have you already committed master record in Projects table first?

[Blackkimba851]

Powers	Utility Room	Ductwork Repaired	Not Done
Powers	Utility Room	Electrical Covers	Not Done
Powers	Utility Room	Electrical GFCI	Not Done
Powers	Utility Room	Electrical Lights	Not Done
Powers	Utility Room	Electrical Outlets	Not Done
Powers	Utility Room	Electrical Panel Repaired	Not Done
Powers	Utility Room	Electrical Repaired/Installed	Not Done
Powers	Utility Room	Electrical Switches	Not Done
Powers	Utility Room	Furnace Repaired	Not Done
Powers	Utility Room	Gas Lines Repaired	Not Done
Powers	Utility Room	Pluming Drain Repaired	Not Done

Project, Room, Task, Status

This is what my table looks like. I do not have a Task table. all data is in the same table.

[Blackkimba851]

Powers	Utility Room	Ductwork Repaired	Not Done
Powers	Utility Room	Electrical Covers	Not Done
Powers	Utility Room	Electrical GFCI	Not Done
Powers	Utility Room	Electrical Lights	Not Done
Powers	Utility Room	Electrical Outlets	Not Done
Powers	Utility Room	Electrical Panel Repaired	Not Done
Powers	Utility Room	Electrical Repaired/Installed	Not Done
Powers	Utility Room	Electrical Switches	Not Done
Powers	Utility Room	Furnace Repaired	Not Done
Powers	Utility Room	Gas Lines Repaired	Not Done
Powers	Utility Room	Pluming Drain Repaired	Not Done

Project, Room, Task, Status

This is what my table looks like. I do not have a Task table. all data is in the same table.

[June7]

Okay, did you try suggested code?

[Blackkimba851]

Yes i did. but it only created one record. I need it to do all 250 records. should i create a table with just the rooms and tasks to use as a template?

[June7]

It should have created multiple records. Post your code or provide db for analysis. Follow instructions at bottom of my post.

[Blackkimba851]

Here is the db

[davegri]

Black-davegri-v01.zip
Have a look at this. Creates new table with project name.

[June7]

I agree with Paul that creating multiple project tables is not desirable design.

The Project field in AllInOne table is empty so of course the INSERT could not copy data. If there were data in Project field, would substitute "some project to copy" with an actual project reference.

A tasks template table is an option. Then the WHERE clause will not be needed.

[Blackkimba851]

That will work. Then I can just delete the table when i'm done with it. thx for your help

[davegri]

That will work. Then I can just delete the table when i'm done with it. thx for your help

U Bet. Good luck with the rest of the project.

[Blackkimba851]

Is there a way to set the format of the newly created table with wider columns ( not the default widths ) and also with the "status" column into a combo box so it can be edited right from the table. Right now I have to edit the table for each new Project that I create.

Project	Room	Task	Status
powers	Utility Room	Pluming Furn	Incomplete

Powers /Utility /Plumbing Furnace /Incomplete <to look more like this>

[June7]

Why are column widths in tables important? Should use forms bound to table/query.

I never set lookups in table.

Already advised should not create new table for each project. Should be 1 table.