Keeping order when running an Update Query 2

[Lokoono]

Is there a way in Access to control what order an update query writes data from the first table into a a second table (based on the values in the first table)?

I'm trying to merge multiple records from the first table into one record on the second table. The common field for both tables would be the EmployeeID number. In the first table, there could be 3 records with the same EmployeeID number or there could be 10 (which is the maximum, so I 've setup my second table to only allow for 10 fields of data).

Its crucial that I keep the same order though when updating the data.

I am bringing the information in from Excel where I already have it sorted in the order that I want. It stays in order (via an OrderNum field that I created when I was in Excel).

I just need to tell the Update Query to look at the record with OrderNum being 1 from the first table and then go to the next record and so forth until it gets to the end.

What I've found happening is that the updates happen in what appears to be random order.

And in case someone's asking, I did try a continuous form. It doesn't work in this case. VBA code doesn't like continous forms (at least my knowledge of it doesn't). Whenever I the code would find something wrong with the first record, all the other records on the continuous form for that particular employee show as having the same errors (even though they truly don't).

I'd appreciate any help anyone can give. Its okay if what I'm asking for can't be done, but at least I'll know.

Thanks.

 

[danvlas]

You will have to use 2 Recordsets to control the order and the updates.

AFAIK, it's not possible through Update queries (unless the query updates a single record at a time)

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dhookom]

I can't imagine what your records look like either before or after your updates. Are you trying to combine values from multiple records into a single record? Can you provide some sample data?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I 've setup my second table to only allow for 10 fields of data
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lokoono]

danvlas,

Whenever I think I have Access figured out, I run into a roadblock (which I'm usually able to tear down). This one has me stumped though for the better part of a month. If its not possible to automate my updates in order, then I do have a backup plan (see the second to last paragraph of this reply for the plan's details)...


dhookom,

Hope this better explains what I'm trying to do....

Table1
My fields
1. IDNumber (this is the primary key field of the table)

2. EmployeeID (there can be multiple records with the same EmployeeID)

3. Date_of_Action (could be any date in any year between 1955 and now since we've had employees with the company since that time)

4. Type_of_Action (Workers Comp injury, Hiring, Firing, Relocation, Job Abandonment, etc.). There are currently 22 different types of actions that an employee could have for any given date. There could be multiple actions on one date, which would go in as a separate record.

That's about the meat of it (there are other fields that I am not really worrying about now until I can figure this current dilemma out).

All of the data needs to be displayed in form view from earliest date to latest. My bosses are only wanting the last 10 actions right now, so the first table only has 10 actions for each associate. And I have to be able to display command buttons on the form if a particular action meets certain criteria. The reason this database is being made is because we need to find trends in employment actions.


Table2 (that's being written to with update queries)
My Fields
EmployeeID (which is the unique field now)
Date_of_Action1
Type_of_Action1
Date_of_Action2
Type_of_Action


Updating the first field goes fine (as I'm able to always apply the . I'm having problems making the second field in the record keep that order though and it all goes downhill from there with the 8 other field "sets".

What makes this even more complicated is that there could is a potential of about 15,000 dates and one of 22 different actions.

I have no problem designing the form or the tables. Its the updating that's the problem.

If my plight is hopeless, then I did think of a workaround today that would help. It'll take creating a 3rd table and about a hundred queries or so. I can create an Update Query to find out the Order number of the second field when it compares its date of action to third date of action field, fourth date of action field, and so forth. I actually figured out a way to use formulas in Excel to make this all go a little easier (if spending 3 to 5 minutes on the design of each query is easy).


PHV,

I'll look at relational design to see if it will work with what I'm trying to do. Thanks.

 

[dhookom]

Why update an un-normalized/spreadsheet-type table? If you really need to see the information you could create a multi-column report.

If you really need a form, I would probably use a little vba code to maintain table2.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Lokoono]

Well it would need to be a form since its not just seeing information. The idea that's been proposed to me is that when a command button shows up as visible (say for example as an alert because someone had multiple Workers' Comp injuries), they want the command button to be able to lead to a separate table that would be tied to the associate's EmployeeID number.

Its daunting, but an interesting concept really.

I'll give the vba code a shot (since I'm more familiar with maintaining code than maintaining queries).

Thanks for the idea and the attention to my problem.

 

[danvlas]

In my opinion, it's a situation where you need a 'temp table', which concept does not exist in Access. However, you could use a table for temporary reporting purposes.

You might structure it like:

EmployeeID
RecordCounter (which would increment from 1 to n for each employee)
ActionDate
ActionType


Then you would populate the table through VBA, using recordsets (take each employee, get Top 10 records from Table2 for that employee, order by date Desc, sort the records in Ascending order by date, then write those records to the temp table, one by one and - important - incrementing the RecordCounter by 1 for each record.

After that, you can create 2 crosstab queries, pivoting on
RecordCounter to preserve the order and the field name. One query would take as value Max(Date_Of_Action), the other Max(Type_Of_Action)

After that, join the 2 crosstab queries into one by EmployeeID and you have what you need, no matter which date or which type is found in first, last or any of the 10 columns you need.

Actually, the problem stays in getting the dates (regardless of their values) into the same 'column' positions as their record index within the 'actions by employee' result set.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dhookom]

I don't have your table but lets assume you want to do something similar with the Orders table from Northwind. Let's display the CustomerID rather than the employee and then the most recent 5 order dates with their ShipVia Company.

Create a two record table with one field:
[tt][blue]
tblCrosstabColumns
==================
FieldName (the actual field name)
Date (1st record)
Type (2nd record)
[/blue][/tt]

Then create a crosstab query with this SQL view:

TRANSFORM First(IIf([FieldName]="Date",[OrderDate],[CompanyName])) AS Expr1
SELECT Orders.CustomerID
FROM tblCrosstabColumns, Shippers INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia
GROUP BY Orders.CustomerID
ORDER BY Orders.CustomerID
PIVOT [FieldName] & Format(DCount("*","Orders","CustomerID=""" & [CustomerID] & """ AND OrderDate>=#" & [OrderDate] & "#"),"00") In ("Date01","Type01","Date02","Type02","Date03","Type03","Date04","Type04","Date05","Type05");

This will show 5 pairs of columns with the shipping company and order date for each CustomerID. You would need to add more column headings.

This crosstab could be used to append or make your temporary table or simply bind it to a form.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Lokoono]

danvlas, your post helped me figure it out, although what I ended up doing was vastly different than what you mentioned, I did use VBA.

I'm not yet familiar with Crosstab queries, so I didn't go that route.

I guess a brief explanation of what I did would be as follows:

#1. I created OrderNumber, CurrentRecord, and TotalRecord fields in my original table. I'll explain the two latter fields below.

#2. Created a query based on the order that the original table SHOULD be sorted (by Employee and then by date)

#3. Created my 2nd table to just allow Unique EmployeeID #s and then appended them to the table from the original

#4. Created a form that's based on the 2nd table with the unique Employee ID #s.

#5. Inserted a subform into the previous form. The subform is based on the original table's query (where its sorted, although it wouldn't really matter about the sort since I can do that through the subform anyway). The form and subform are linked by EmployeeID

#6. Here's where I start with VBA. I use DAO to open the recordset of the 2nd table

#7. I use VBA code to determine recordcounts and currentrecords for the table and assign values to them.

#8. I then use "Do Until...Loop" and type up code to switch between the form and subform in-between, and then assigning order numbers based on the currentrecord value. Once the last record in the subform is reached, the code then goes back to the main form and goes to the next record and then back to the subform for the next employee.

I know all of this seems unecessarily messy, but it really isn't that bad to look at and it only takes about a minute to cycle through 300 records (there are actually 900 employee records, but I only have 300 in the database right now).

#9. Forgot to mention I use DCount to determine the total records). I use Do Until rs.EOF, but I don't think it actually stops the loop because I get an error message when it reaches the end of my recordset. No big deal though since I used a workaround to end the loop when the main form's currentrecord matches the total record #.

#10. I then wrote update queries to assign the records from the original table to a 3rd table where I have date1, date2, date3, and etc (the query writes to the appropriate field depending on the OrderNumber).

I can finally put this chapter of the database to rest for now. When I learn crosstab queries, I will go back and see if I can streamline the whole process to run quicker (for now, 2 to 4 minutes for managing 900 records is definitley quick enough for this process).

Thanks again danvlas and dhookom for the help and ideas.