How to design update query to add CurrentRecord of child 1

[kimmba]

I have a parent table named tblParent with one child table named tblChild. The structure is:

tblParent
ParentID
[other fields]

tblChild
ChildID
lngParentID
[other fields]

Background: A form (frmParent) with a record source of tblParent has a subform of tblChild. Two unbound textboxes are included on the subform. The control source of the first is =[CurrentRecord]. The second is =DCount("ChildID","tblChild","lngParentID=" & Forms!frmParent.ParentID). The result after adding a label is, for example, Record 1 of 2 meaning this parent record has two child records and the view is on the first record. So far so good.

My question is how to do this in a query of all parents with each child record identified as record 1, 2, 3, etc. I have a temp table built and am trying to run an update query to populate, but do not know how to query the child records as they relate to the parent and then assign the correct record number.

Ultimately I will run a crosstab on the child record of 1, 2, 3, etc.

Thank you very much for any suggestions or for pointing me to a previous post.

 

[dhookom]

Does the resulting query need to be editable? If you want to number the child records for each parent, do you have a unique field in tblChild that identifies the number order?

Duane
Hook'D on Access
MS Access MVP

 

[kimmba]

The resulting query does not need to be editable.

The unique field to order by is the child date field (ASC) to determine child record order.

A query would pull the first four columns to which I would need the ChildRecOrder (5th column, either an update to a temp table or as a calculated field):

ParentID ChildID Date Category ChildRecOrder
Parent1 Child1 08/01/11 A 1
Parent1 Child2 08/02/11 B 2
Parent2 Child1 07/15/11 D 1
Parent3 Child1 06/01/11 C 1
Parent3 Child2 06/10/11 A 2
Parent3 Child3 06/20/11 B 3

The ChildRecOrder number would then be used as a column in the crosstab to produce results looking something like this:

1 2 3
Parent1 A B
Parent2 D
Parent3 C A B

The purpose of this is to show the change of category over time for each parent record. What do you think? Is this doable? Or maybe I need to take a different approach?

 

[Andrzejek]

Could you use last character from ChildID to create ChildRecOrder?
[tt]
ParentID ChildID Date Category ChildRecOrder
Parent1 Child[blue]1[/blue] 08/01/11 A [blue]1[/blue]
Parent1 Child[blue]2[/blue] 08/02/11 B [blue]2[/blue]
Parent2 Child[blue]1[/blue] 07/15/11 D [blue]1[/blue]
Parent3 Child[blue]1[/blue] 06/01/11 C [blue]1[/blue]
Parent3 Child[blue]2[/blue] 06/10/11 A [blue]2[/blue]
Parent3 Child[blue]3[/blue] 06/20/11 B [blue]3[/blue][/tt]

in something like:[tt]
SELECT ParentID, ChildID, Date, Category, [blue]Right(ChildID, 1) As ChildRecOrder[/blue]
FROM...[/tt]

Have fun.

---- Andy

 

[kimmba]

Unfortunately not as all IDs are autonumbers. The Child1, Child2, etc. represents the number of child records linked to each parent record. The real child table data might look like this:

ChildID lngParentID datEvent
1379 915 08/01/11
2426 915 08/02/11
8 7 07/15/11
356 1058 06/01/11
3476 1058 06/10/11
1598 1058 06/20/11

Appreciate your input. Thank you!

 

[dhookom]

Assuming the Orders table in the sample Northwind database with fields OrderID, CustomerID, EmployeeID, and OrderDate. You want to number the orders by customerID from 1 being the earliest to the highest/most recent. The query would use two copies of the Orders table joined on the CustomerID fields. The SQL would would be:

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Count(Orders_1.OrderID) AS CountOfOrderID
FROM Orders AS Orders_1 INNER JOIN Orders ON Orders_1.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)>=[Orders_1].[OrderDate]))
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate
ORDER BY Orders.CustomerID, Orders.OrderDate;

Resulting in something like:
[tt]
Order ID Customer Employee Order Date CountOfOrderID
10529 Maison Dewey Buchanan, Steven 07-May-1997 1
10649 Maison Dewey Buchanan, Steven 28-Aug-1997 2
10760 Maison Dewey Peacock, Margaret 01-Dec-1997 3
10892 Maison Dewey Peacock, Margaret 17-Feb-1998 4
10896 Maison Dewey King, Robert 19-Feb-1998 5
10978 Maison Dewey Dodsworth, Anne 26-Mar-1998 6
11004 Maison Dewey Leverling, Janet 07-Apr-1998 7
10517 North/South Leverling, Janet 24-Apr-1997 1
10752 North/South Fuller, Andrew 24-Nov-1997 2
11057 North/South Leverling, Janet 29-Apr-1998 3
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[kimmba]

Yes! This is perfect. Interesting how you use two instances of the same table, one to group and the other to count. My SQL/query skills are moderate at best, so this was an eye opener. Terrific query lesson. Thank you so very much.