adding item count to query 1

[robojeff]

I have a query which pulls a parent part number from a database table and then
pulls a Bill of materials of all the items under that part number and displays these
parts with the parent part number.

I create a table with this data but I would also like to create a line number field so
that each line containing a part number under the parent part number is listed wth
a line item. For example, I would like to set this up to create a table as shown below:

Parent item Line
xxxxx 1234 1
xxxxx 1322 2
xxxxx 1455 3
xxxxx 1133 4
yyyyy 5432 1
yyyyy 5665 2
zzzzz 1122 1
zzzzz 2111 2

How can I create these line counts and add them to my table?

Thanks again

 

[dhookom]

Try something like:

SELECT Parent, Item, (SELECT Count(*) FROM [yourquery] y WHERE y.Item <= [yourquery].Item AND y.Parent = [Yourquery].parent) as Line
FROM [Yourquery];

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

CODE
SELECT Parent, Item, (SELECT Count(*) FROM [yourquery] y WHERE y.Item <= [yourquery].Item AND y.Parent = [Yourquery].parent) as Line
FROM [Yourquery];

Should the "YourQuery" actually be "mytable"? I get an error stating that an action query can not be used as a row source when I specify myQuery...

I added an additional field which I am using to sort this in ascending order but when I use "myTable" I get the lines but they are not in sequential order.

my SQL is as follows:

SELECT Mytable.parent, Mytable.Item, (SELECT Count(*) FROM [Mytable] y WHERE y.Item <= [Mytable].Item AND y.Parent = [Mytable].parent) AS Line, Mytable.order
FROM Mytable
ORDER BY Mytable.parent, Mytable.order;

regarding the data results, I get the following:

parent Item Line order
xxxxx 1234 2 1
xxxxx 1322 3 2
xxxxx 1455 4 3
xxxxx 1133 1 4
yyyyy 5432 1 1
yyyyy 5665 2 2
zzzzz 1122 1 1
zzzzz 2111 2 2

This is only the test data, when I plug my real table most all of the groupings of items under the parent part
number are displayed in parent and then order number order but the line value is not sequencial and I am
noticing that none of these are starting with "1"

What am I doing wrong?


thanks

 

[dhookom]

Since you didn't provide a query or table name, I just made one up.

Do you have more duplicate Item values within a parent? Where did the Order field come from and isn't it the value you were hoping to create?

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

The parent field can be the same for many records in this table as each record shows the parent number and then an item listed under it...


My mistake about the order field as this is actually a range and I should have listed it as follows:
(sorry, I set up my test data wrong in my table- what shows below is what Iget with this same query)

parent Item Line order
xxxxx 1234 2 10
xxxxx 1322 3 10
xxxxx 1234 2 10
xxxxx 1455 4 20
xxxxx 1133 1 30
yyyyy 5665 2 20
yyyyy 5432 1 20
zzzzz 1122 1 30
zzzzz 2111 2 40

thanks again

 

[dhookom]

So apparently you do have duplicate Item values within a single Parent. When there are dups, how do you want them numbered?

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

Yes there is an occasional duplication of an Item number within the parent number grouping...

There is another field in this table which contains a 3 digit number which is in the correct order per the parent number called "bubble" but I need to add the Line field that counts from 1 - whatever the last record is and append it to the table...)

In other words, I would like the table to be grouped by parent number and then sorted by and numbered (1, 2, 3, ...) in the same order as the bubble number...

maybe, if I can sort off that field, this might come out right...


thank you for your help and patience Duane..

 

[dhookom]

So now there is another field that is significant? Please define your question thoroughly and provide us with everything we need to help with an answer. Providing some sample records and desired output is key.

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Ok, I know what I want to do but I am having a hard time explaining it...

The other field is not significant, but is does have the correct order...

I have a table with the following fields:

parent Item order bubble
xxxxx 1234 10 100
xxxxx 1322 10 105
xxxxx 2211 10 124
xxxxx 1455 20 200
xxxxx 1133 30 300
xxxxx 5432 60 610
yyyyy 5432 20 202
yyyyy 5665 20 220
yyyyy 1133 40 436
zzzzz 1122 30 305
zzzzz 2111 40 410

The bubble number does show a sequence and by using the following query:

SELECT Mytable.parent, (SELECT Count(*) FROM [Mytable] y WHERE y.Item <= [Mytable].Item AND y.Parent = [Mytable].parent) AS Line, Mytable.Item, Mytable.order, Mytable.bubble
FROM Mytable
ORDER BY Mytable.parent, Mytable.bubble;

I get the following results:

parent Line Item order bubble
xxxxx 2 1234 10 100
xxxxx 3 1322 10 105
xxxxx 5 2211 10 124
xxxxx 4 1455 20 200
xxxxx 1 1133 30 300
xxxxx 6 5432 60 610
yyyyy 2 5432 20 202
yyyyy 3 5665 20 220
yyyyy 1 1133 40 436
zzzzz 1 1122 30 305
zzzzz 2 2111 40 410

How do I get this to display these lines in order such as:

parent Line Item order bubble
xxxxx 1 1234 10 100
xxxxx 2 1322 10 105
xxxxx 3 2211 10 124
xxxxx 4 1455 20 200
xxxxx 5 1133 30 300
xxxxx 6 5432 60 610
yyyyy 1 5432 20 202
yyyyy 2 5665 20 220
yyyyy 3 1133 40 436
zzzzz 1 1122 30 305
zzzzz 2 2111 40 410

thank you!

 

[PHV]

SELECT Mytable.parent, (SELECT Count(*) FROM [Mytable] y WHERE [!]y.bubble <= [Mytable].bubble[/!] AND y.Parent = [Mytable].parent) AS Line, Mytable.Item, Mytable.order, Mytable.bubble
FROM Mytable
ORDER BY Mytable.parent, Mytable.bubble

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

 

[robojeff]

SELECT Mytable.parent, (SELECT Count(*) FROM [Mytable] y WHERE y.bubble <= [Mytable].bubble AND y.Parent = [Mytable].parent) AS Line, Mytable.Item, Mytable.order, Mytable.bubble
FROM Mytable
ORDER BY Mytable.parent, Mytable.bubble

That's it...

thank you PHV & Duane