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?
- Local time
- Today, 06:47
- Joined
- Feb 19, 2002
- Messages
- 47,029
When you want to combine several sets of data, you would use a Union Query. The critical point in a union is that ALL the Select clauses in the Union MUST have the select columns in the same sequence.
Select FName, LName, Address From qryFirst
Union Select LName, FName, Address From qrySecond
This query will run but the first/last names will be scrambled and the recordset will be useless. The columns need to be in the same order and the selections for each column must be of the same data type. so if the third column in the first query is a date, then the third column in the second query must also be a date.
Access will complain if the data types are a mismatch but it has no way of knowing you did something stupid if the data types are the same as in my first description of an error.
I get that if you don't know VBA, it's hard to just jump in but for something simple like this, you would either bind the union quey to the report if we've guessed right and that's what you need. Or if we've guessed wrong and for some bizarre reason, you actually need to create a temp table, you would just use two lines of code in the click event of a button. The first line would run a make table query to create/recreate the temp table and the second line would run an append query.
Select FName, LName, Address From qryFirst
Union Select LName, FName, Address From qrySecond
This query will run but the first/last names will be scrambled and the recordset will be useless. The columns need to be in the same order and the selections for each column must be of the same data type. so if the third column in the first query is a date, then the third column in the second query must also be a date.
Access will complain if the data types are a mismatch but it has no way of knowing you did something stupid if the data types are the same as in my first description of an error.
I get that if you don't know VBA, it's hard to just jump in but for something simple like this, you would either bind the union quey to the report if we've guessed right and that's what you need. Or if we've guessed wrong and for some bizarre reason, you actually need to create a temp table, you would just use two lines of code in the click event of a button. The first line would run a make table query to create/recreate the temp table and the second line would run an append query.
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.
- Local time
- Today, 06:47
- Joined
- Feb 19, 2002
- Messages
- 47,029
You appear to have autonumbers in some tables which are not being used as the primary key. The ONLY purpose of an autonumber is to be used as the primary key. If you are using other fields as the single or compound PK, then get rid of the autonumbers before they cause a problem.
That said, you will find building an interface with Access forms, much easier if you use autonumbers as the PK for your tables, ESPECIALLY if you make use of combo and listboxes. Both of these controls REQUIRE a SINGLE unquiet identifier in order for them to work correctly.
If you have a multi-field unique index required to enforce uniqueness of a combination of data fields, that is fine but you will need to build the multi-column index using the Indexes dialog. Although you can build a multi-column PK using the table interface, you cannot create a multi-column unique index that way. Let the autonumber be your PK. Use it for ALL joins. Let the data fields be data and create a compound unique index as necessary to ensure that when you need to enforce uniqueness on a set of fields you can.
Given the way you are using the field, I'm going to assume that employee_id is a unique identifier assigned by a different application. In your access application, this is just a reference field. Taking just the first two tables, you CANNOT build a combo to use to select an employee_record from because there is no 1 column unique identifier in the employee table.
Please stop worrying about creating a macro. You have urgent design changes to make before you go any further with this application. You are just digging yourself a deeper hole if you persist in duplicating data by creating these append queries.
I see that you've marked this thread as "solved" but your problem is far from solved. If you would like help fixing the schema, please start a new thread and upload a copy of the database. If you have actual data in the tables, obfuscate anything sensitive but leave as much data as possible to help us work out how the data relates.
That said, you will find building an interface with Access forms, much easier if you use autonumbers as the PK for your tables, ESPECIALLY if you make use of combo and listboxes. Both of these controls REQUIRE a SINGLE unquiet identifier in order for them to work correctly.
If you have a multi-field unique index required to enforce uniqueness of a combination of data fields, that is fine but you will need to build the multi-column index using the Indexes dialog. Although you can build a multi-column PK using the table interface, you cannot create a multi-column unique index that way. Let the autonumber be your PK. Use it for ALL joins. Let the data fields be data and create a compound unique index as necessary to ensure that when you need to enforce uniqueness on a set of fields you can.
Given the way you are using the field, I'm going to assume that employee_id is a unique identifier assigned by a different application. In your access application, this is just a reference field. Taking just the first two tables, you CANNOT build a combo to use to select an employee_record from because there is no 1 column unique identifier in the employee table.
Please stop worrying about creating a macro. You have urgent design changes to make before you go any further with this application. You are just digging yourself a deeper hole if you persist in duplicating data by creating these append queries.
I see that you've marked this thread as "solved" but your problem is far from solved. If you would like help fixing the schema, please start a new thread and upload a copy of the database. If you have actual data in the tables, obfuscate anything sensitive but leave as much data as possible to help us work out how the data relates.
