Solved macro that creates a table with two append queries
- Thread starter distinky
- Start date
Yes, but it's not something any of us would ever do. That methodology screams hacky solution.
Give us the big picture of what you are actually trying to accomplish and we can help you achieve a more proper solution to the ultimate goal. Assume you've created that table you want, then what? What will you use that table for?
Give us the big picture of what you are actually trying to accomplish and we can help you achieve a more proper solution to the ultimate goal. Assume you've created that table you want, then what? What will you use that table for?
my mistake for not explaining in depth what im trying to do. at the moment im currently trying to create a work flow type of thing. im quite new to all of this with SQL, VBA etc.
what ive done so far is a registry for employees, so we put in their name and job title etc, then we assign tasks to the job title. such as deploying software for certain job titles. then ive created a report that displays as a lists of tasks for a specific user and their job title. it also displays what device type the tasks are under. then we follow that as a list.
from that report i want to create a log type of thing. so it pretty much acts as 'report created for ''user'' ''job title'' at this date...' etc.
from that, ive created two tables. one called 'Task_Create_Detail' and one is called 'task_Create_header.'
the 'Task_Create_Header' contains a 'task ID', 'employee id', 'task date','employee start date' and 'job type.'
the 'task_Create_detail' contains 'task id' 'attribute id' 'user_device_type' and 'attribute header.'
the query ive created for the header is an append query and every time a new task is created it adds +1 onto the 'task_ID' so its unique to the employee.
the task create detail query brings over the unique 'task id' and then displays the list of tasks that have been assigned to the employee. its also an append query.
so my next steps, i want to use a macro to either run these two queries then for it to generate a table. it doesnt have to be a macro.
from that table with the information combined from those two queries i want it to then generate a report from that.
i hopefully ive explained this well ;-;
what ive done so far is a registry for employees, so we put in their name and job title etc, then we assign tasks to the job title. such as deploying software for certain job titles. then ive created a report that displays as a lists of tasks for a specific user and their job title. it also displays what device type the tasks are under. then we follow that as a list.
from that report i want to create a log type of thing. so it pretty much acts as 'report created for ''user'' ''job title'' at this date...' etc.
from that, ive created two tables. one called 'Task_Create_Detail' and one is called 'task_Create_header.'
the 'Task_Create_Header' contains a 'task ID', 'employee id', 'task date','employee start date' and 'job type.'
the 'task_Create_detail' contains 'task id' 'attribute id' 'user_device_type' and 'attribute header.'
the query ive created for the header is an append query and every time a new task is created it adds +1 onto the 'task_ID' so its unique to the employee.
the task create detail query brings over the unique 'task id' and then displays the list of tasks that have been assigned to the employee. its also an append query.
so my next steps, i want to use a macro to either run these two queries then for it to generate a table. it doesnt have to be a macro.
from that table with the information combined from those two queries i want it to then generate a report from that.
i hopefully ive explained this well ;-;
The first step for a good and working database solution should be to create a well thought out and functional data model (tables, relationships), just like a house needs a foundation first.
Show your data model => contents of the relationship window
Show your data model => contents of the relationship window
If the data model fits (which I don't believe after a first glance), you only need a selection query for the simple data compilation described.
Writing existing data to a new table only creates redundancies and thus a significant potential problem for an intact database.
1. It sounds like you are over engineering a solution for your report. Although I didn't completely understand it, I don't see why you can't just make a query and base your report on that query. No need for code and moving data to tables and appending. Just use a SELECT and then use that for your report.
2. I don't think your table structure is right. It seems you are storing the same data ll over the place. For example every field in Attribute_header except Attribute_header_id is in employee_record. Despite that, those 2 tables are not directly related to each other, seems very odd and wrong. I see other issues with your tables but that is the most obvious.
My suggesttion is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), read a few tutorials and then apply what you learn to your database. The ultimate solution to the issue you posted isn't going to be through code and action queries, but writing a SELECT query based on a properly structured database.
2. I don't think your table structure is right. It seems you are storing the same data ll over the place. For example every field in Attribute_header except Attribute_header_id is in employee_record. Despite that, those 2 tables are not directly related to each other, seems very odd and wrong. I see other issues with your tables but that is the most obvious.
My suggesttion is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), read a few tutorials and then apply what you learn to your database. The ultimate solution to the issue you posted isn't going to be through code and action queries, but writing a SELECT query based on a properly structured database.
Similar threads
