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 StringDim db As DAO.DatabaseDim rs As DAO.RecordsetDim qryRS As DAO.RecordsetDim aa As IntegerDim i As IntegerDim j As IntegerDim CSiD As IntegerDim ProcessID As IntegerDim PCD As LongDim b As IntegerDim RecordCount As LongDim qryApp As StringSet db = CurrentDbSet rs = db.OpenRecordset("tblCompSeriesProcessDetails") 'Debug.Print rs.RecordCountRecordCount = 1For i = 1 To rs.RecordCount If RecordCount < rs.Fields("CompSeriesProcessID").Value Then RecordCount = rs.Fields("CompSeriesProcessID").Value End If rs.MoveNextNext irs.MoveFirstDoCmd.SetWarnings FalseFor 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 iNext jFor j = 17 To 33qryApp = "qryProcess" & jstrSQL = "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 strSQLDoCmd.RunSQL strSQLRecordCount = RecordCount + 1Next jDoCmd.SetWarnings TrueSet qryRS = NothingSet rs = NothingSet db = NothingEnd 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?
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.