Understanding an Append Query's SQL
Hi all,
This is an extremely vague question, but if I don't ask it I'll never find an answer...
I have many queries that feed into forms. When the forms are populated with data, I want to append data from these queries into another table to try and make my data thin (as this is how Access likes it!) to feed out into another output.
Is my query doing the correct thing?
(prefix tbl is a table, prefix qry is a query, process 16 behaves in a totally different manner to the rest so have created loops from 4-15 and 17-33, 33 processes in total, similarities come from 4-15 and 17-33)
Also, do I need to loop for all the records in my query?
Thanks in advance for a quick response!
Sarith
This is an extremely vague question, but if I don't ask it I'll never find an answer...
I have many queries that feed into forms. When the forms are populated with data, I want to append data from these queries into another table to try and make my data thin (as this is how Access likes it!) to feed out into another output.
Is my query doing the correct thing?
Sub AppendQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qryRS As DAO.Recordset
Dim aa As Integer
Dim i As Integer
Dim j As Integer
Dim CSiD As Integer
Dim ProcessID As Integer
Dim PCD As Long
Dim b As Integer
Dim RecordCount As Long
Dim qryApp As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompSeriesProcessDetails")
'Debug.Print rs.RecordCount
RecordCount = 1
For i = 1 To rs.RecordCount
If RecordCount < rs.Fields("CompSeriesProcessID").Value Then
RecordCount = rs.Fields("CompSeriesProcessID").Value
End If
rs.MoveNext
Next i
rs.MoveFirst
DoCmd.SetWarnings False
For j = 4 To 15
qryApp = "qryProcess" & j
Set qryRS = db.OpenRecordset(qryApp & "_Sub")
For i = 1 To qryRS.RecordCount
strSQL = "INSERT INTO tblCompSeriesProcessDetails ( CompSeriesProcessID, CompSeriesID, ProcessID_FK, PlannedCompletionDate )" _
& "SELECT " & RecordCount + 1 & ", " & qryApp & "_Sub.CompSeriesID, " & qryApp & "_Sub.CurrentProcess, " & qryApp & "_Sub.PCD " _
& "FROM " & qryApp & "_Sub"
Debug.Print strSQL
DoCmd.RunSQL strSQL
RecordCount = RecordCount + 1
qryRS.MoveNext
Next i
Next j
For j = 17 To 33
qryApp = "qryProcess" & j
strSQL = "INSERT INTO tblCompSeriesProcessDetails ( CompSeriesProcessID, CompSeriesID, ProcessID_FK, PlannedCompletionDate )" _
& "SELECT " & RecordCount + 1 & ", " & qryApp & "_Sub.CompSeriesID, " & qryApp & "_Sub.CurrentProcess, " & qryApp & "_Sub.PCD " _
& "FROM " & qryApp & "_Sub"
Debug.Print strSQL
DoCmd.RunSQL strSQL
RecordCount = RecordCount + 1
Next j
DoCmd.SetWarnings True
Set qryRS = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
(prefix tbl is a table, prefix qry is a query, process 16 behaves in a totally different manner to the rest so have created loops from 4-15 and 17-33, 33 processes in total, similarities come from 4-15 and 17-33)
Also, do I need to loop for all the records in my query?
Thanks in advance for a quick response!
Sarith
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sarah, this looks more like you are working with a spreadsheet or other flat file than a relational database. In SQL, a table is an unordered set and so is a query that has no order by clause. That means that there is no contract to return the records in the order in which you expect them. If record order is important, the ONLY way to guarantee it is to sort by a unique identifier.
The first loop looks like you are reading every record of table and assuming that the last record you read has the highest value for CompSeriesProcessID. The problem is that Access doesn't work quite like a "real" RDBMS in how it manages free space so new records are always added to the end of the table space following the last record that was entered. "Real" RDBMS' use a much more sophisticated algorithm for determining where records are physically written on the disk. Also, Access (Jet and ACE) typically return rows in physical order for queries without order by clauses or tables. So unless your table is huge and/or you do a lot of updating, it will look like it is safe to process an unsorted table this way. I can assure you that it is not because of the way updates happen. All text fields in Jet/ACE are variable in length regardless of what length you specify. If you say a column is 20 characters but only 12 are written, only 12 are stored and the first character of the next column immediately follows the 12th character. There is not 8 characters of filler at the end of the short field. So here comes the problem. If you update that record and change the value of that field so that it is now 18 characters long, Jet/ACE may not be able to write it back to disk in its original physical location so it may insert a pointer and write the changed record at the end of the table space. So record ID 45 is no longer between 44 and 46 when you read the table unsorted. Record 45 now comes between records 519 and 520.
Part of the compact process is to copy all tables, sort them and rewrite them in PK sequence so most of you will never actually see this happen but if you care to reproduce the situation, you need to create a table with a few thousand long records where some of the fields are shorter than the designated length. Update a record and see where it appears in an unsorted query.
That was a pretty long and technical explanation of why you can never perform a process that depends on sequence on a recordset based on opening a table directly. You must ALWAYS use a query with an order by clause if record order is important. That said, if all you want is the maximum value for the record ID so you can increment it yourself then use DMax("CompSeriesProcessID"
As to the rest of the process. "bizarre" comes to mind. I don't have a clue what you are trying to do but I can tell you that you have some type of design flaw, perhaps simply in your form. When working with a paper form we might allow room to enter the names of 5 children because 2 and a fraction is the national average so 5 is twice the norm. But how do you handle a family with 6 or 7 or 12? They are rare but two of my aunts each had 7 children so it is certainly no unheard of. In a paper form, people just write smaller or in the margins. In a spreadsheet, you might add additional columns or simply allow multiple names to be entered in a single column. In a relational database, each child would be entered in a separate row and in theory, the number of children is infinite.
There is rarely a reason to impose an artificial limit. It looks like you have somehow imposed artificial limits on the "paper" form rather than using subforms which would allow an infinite number of child records. Occasionally there are business limits that must be imposed but those are enforced via program code. For example, a lab classroom might have only 20 work stations so the number of students for this class must be 20 or less. In this case, before you add a new student, you count the existing class members. If it is less than 20, you add the new student. If it is 20 or more, you prevent the student from being added. Or you might do it by creating a StudentSeqNum where you assign the values 1-20 and the validation rule in the database allows only the values 1-20 and the combination of ClassID and StudentSeqNum must be unique which prevents two student 5's from being added.
I would suggest standing back and reviewing your data entry process and your database schema in light of what I have said and the rules of normalization.
The first loop looks like you are reading every record of table and assuming that the last record you read has the highest value for CompSeriesProcessID. The problem is that Access doesn't work quite like a "real" RDBMS in how it manages free space so new records are always added to the end of the table space following the last record that was entered. "Real" RDBMS' use a much more sophisticated algorithm for determining where records are physically written on the disk. Also, Access (Jet and ACE) typically return rows in physical order for queries without order by clauses or tables. So unless your table is huge and/or you do a lot of updating, it will look like it is safe to process an unsorted table this way. I can assure you that it is not because of the way updates happen. All text fields in Jet/ACE are variable in length regardless of what length you specify. If you say a column is 20 characters but only 12 are written, only 12 are stored and the first character of the next column immediately follows the 12th character. There is not 8 characters of filler at the end of the short field. So here comes the problem. If you update that record and change the value of that field so that it is now 18 characters long, Jet/ACE may not be able to write it back to disk in its original physical location so it may insert a pointer and write the changed record at the end of the table space. So record ID 45 is no longer between 44 and 46 when you read the table unsorted. Record 45 now comes between records 519 and 520.
Part of the compact process is to copy all tables, sort them and rewrite them in PK sequence so most of you will never actually see this happen but if you care to reproduce the situation, you need to create a table with a few thousand long records where some of the fields are shorter than the designated length. Update a record and see where it appears in an unsorted query.
That was a pretty long and technical explanation of why you can never perform a process that depends on sequence on a recordset based on opening a table directly. You must ALWAYS use a query with an order by clause if record order is important. That said, if all you want is the maximum value for the record ID so you can increment it yourself then use DMax("CompSeriesProcessID"
As to the rest of the process. "bizarre" comes to mind. I don't have a clue what you are trying to do but I can tell you that you have some type of design flaw, perhaps simply in your form. When working with a paper form we might allow room to enter the names of 5 children because 2 and a fraction is the national average so 5 is twice the norm. But how do you handle a family with 6 or 7 or 12? They are rare but two of my aunts each had 7 children so it is certainly no unheard of. In a paper form, people just write smaller or in the margins. In a spreadsheet, you might add additional columns or simply allow multiple names to be entered in a single column. In a relational database, each child would be entered in a separate row and in theory, the number of children is infinite.
There is rarely a reason to impose an artificial limit. It looks like you have somehow imposed artificial limits on the "paper" form rather than using subforms which would allow an infinite number of child records. Occasionally there are business limits that must be imposed but those are enforced via program code. For example, a lab classroom might have only 20 work stations so the number of students for this class must be 20 or less. In this case, before you add a new student, you count the existing class members. If it is less than 20, you add the new student. If it is 20 or more, you prevent the student from being added. Or you might do it by creating a StudentSeqNum where you assign the values 1-20 and the validation rule in the database allows only the values 1-20 and the combination of ClassID and StudentSeqNum must be unique which prevents two student 5's from being added.
I would suggest standing back and reviewing your data entry process and your database schema in light of what I have said and the rules of normalization.
Hi PatHartman,
Thank you for this.
Unfortunately, I am an Excel buff that is starting to use Access and have been using it for the last few months. This means I still look at things from a spreadsheet perspective.
The database I'm building is looking at 33 processes, all with a different completion date. These are built into their own tables as I need to capture more data than just that.
From these I've created queries to build forms. Once I've captured the data, I store I'm building a calendar to produce a schedule for individual workloads.
The Append Query was to make the data thin so populate a query for my calendar.
I thought about my original post and I suppose I wanted to find out the simplest way of appending records from one dataset to another.
Would creating a new temporary table be the best solution?
I know it's not the cleanest, but with my limited experience and deadlines looming, I'm trying to deliver something quickly yet efficiently.
All help appreciated, thank you for the response. Will certainly make changes to my way of working :)
Thanks again,
Sarith
Thank you for this.
Unfortunately, I am an Excel buff that is starting to use Access and have been using it for the last few months. This means I still look at things from a spreadsheet perspective.
The database I'm building is looking at 33 processes, all with a different completion date. These are built into their own tables as I need to capture more data than just that.
From these I've created queries to build forms. Once I've captured the data, I store I'm building a calendar to produce a schedule for individual workloads.
The Append Query was to make the data thin so populate a query for my calendar.
I thought about my original post and I suppose I wanted to find out the simplest way of appending records from one dataset to another.
Would creating a new temporary table be the best solution?
I know it's not the cleanest, but with my limited experience and deadlines looming, I'm trying to deliver something quickly yet efficiently.
All help appreciated, thank you for the response. Will certainly make changes to my way of working :)
Thanks again,
Sarith
Without spending hours/days/weeks to analyze the current processes I won't be able to offer an appropriate design plan but based on nearly 50 years of experience in designing and building applications, I'm convinced that you are on the wrong path. It is highly unlikely that you need 33 separate tables. That is your spreadsheet hat falling over your eyes and blinding you to the wonders of a relational database. Try not to fixate on input or output formats. Try to focus on data and the commonalities of the processes.
Also, making temp table is usually the wrong approach. Once everything is properly normalized, the data will be in one table or perhaps in several but the separation won't be by process Number. It will be by data relationships. Once the data has been properly normalized, you'll find that you have common form and report formats rather than the 33 each that you are headed for as well as dozens of queries to go along with this separation. Instead of actually copying data and physically storing it twice, a properly designed app will use queries to select the data it needs. So, when your form is showing process 22, then the query will only select the data for process 22.
If you have to create a method of importing the data from hundreds of existing spreadsheets, that will be completely separate from the replacement application. Importing data from unnormalized spreadsheets will probably be a combination of cut and paste and lots of hard coded references to specific cells but once the data has been transformed, you will be free from the constraints of Excel and can fly.
If the data isn't proprietary or you can obfuscate it, try posting a couple of different sheets and we'll see if we can be a little more specific.
Also, making temp table is usually the wrong approach. Once everything is properly normalized, the data will be in one table or perhaps in several but the separation won't be by process Number. It will be by data relationships. Once the data has been properly normalized, you'll find that you have common form and report formats rather than the 33 each that you are headed for as well as dozens of queries to go along with this separation. Instead of actually copying data and physically storing it twice, a properly designed app will use queries to select the data it needs. So, when your form is showing process 22, then the query will only select the data for process 22.
If you have to create a method of importing the data from hundreds of existing spreadsheets, that will be completely separate from the replacement application. Importing data from unnormalized spreadsheets will probably be a combination of cut and paste and lots of hard coded references to specific cells but once the data has been transformed, you will be free from the constraints of Excel and can fly.
If the data isn't proprietary or you can obfuscate it, try posting a couple of different sheets and we'll see if we can be a little more specific.
One thing you might start with is - create a new, empty Access database to start making your data model. Create a table with several columns:
ProcessNumber
AttributeName
AttributeType (text, number, date, etc)
MaxLength
NumberOfValuesallowed (usually this will be 1 but this will help you identify repeating groups)
Once you do this for all processes, you can sort it and see which attributes are common and which are unique.
ProcessNumber
AttributeName
AttributeType (text, number, date, etc)
MaxLength
NumberOfValuesallowed (usually this will be 1 but this will help you identify repeating groups)
Once you do this for all processes, you can sort it and see which attributes are common and which are unique.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial