Sequencing parts

[Scott]

I have a table of parts and material parts. I have the table sorted by the
portent part then the mtlpart. I need to sequence them like the example
below. The order of the mtlparts is not important as long as all the parents
are together. Right now I am doing this in excel with the following IF
formula.
=IF(B5=B4,D4+10,10) Note: The first Colum starts with a 10
If there is only one P/N to MtlPart than the seq qould be 10. If there are
two LIKE P/N with different MtlParts then the first would be 10 and the
second would be 20.

There would never be two duplicates as I have a Primary key relationship on
the P/N & MtlPart.

ALso there could be up to 30 of the same P/N and different MtlParts. 10,20,

300.

P/N Rev SEQ MtlPart
491H-1B Conv 10 CV4
491V-2 Conv 10 01BV
491V-2B Conv 10 CV404U
291H-1 Conv 10 100SJ34191225
291H-1 Conv 20 KEVLAR
291H-1 Conv 30 PROTEX
291H-1 Conv 40 100SJ
291H-1 Conv 50 01LB1
291H-1 Conv 60 200SJ3
291H-2 Conv 10 KEVLAR
291H-2 Conv 20 PROTEX
291H-2 Conv 30 400DE
291H-2 Conv 40 200J34
291H-2 Conv 50 10SJ41
291H-2 Conv 60 100J3419

 

[Lord Kelvan]

you cannot do that as is you need to add something to act as a
sequence source for the query to do it

i take it your design is

parts ||---0< material_parts >0-----|| material

where the above data apart from the SEQ field comes from.

what you need to do is edit material_parts and insert a new field
which is an auto number

then the below query i have named it autonumberfield

the below query then takes the auto number field and uses it to
generate the sequence numbers

SELECT material_parts.[P/N], material_parts.Rev,
material_parts.MtlPart, (SELECT (Count([p/n]) + 1)*10 FROM
material_parts AS materialpartsnew WHERE
material_parts.autonumberfield > materialpartsnew.autonumberfield and
materialpartsnew.[p/n] = material_parts.[p/n]) AS [sequence num]
FROM material_parts
order by [p/n]

you can then create another query to order by the p/n and then by the
sequence number

something like
SELECT [P/N], Rev, MtlPart, [sequence num]
FROM theabovequery
order by [p/n],[sequence num];

there may be another way to do this without having to create an auto
number but i dont know it

AS A NOTE
database are not spreadsheets though to you, you may see multiple
records on the screen a database can only see 1 and i say again 1!!!
record at a time. in access this is noted by the black arrow pointing
to the record in the datasheet view on the right hand side. There is
no such thing as order in a database like there is in a spreadsheet so
what you did though seems easy in excel is very complicated in a
database.

hope this helps

Regards
Kelvan

 

[Scott]

Thanks for the input. but i think I am missing something. this is my select
code for the Parent part, child part, Number, seq. (note the seq field is
blank, this is what I need).

SELECT [PARTMTL - GOOD].Number01, [PARTMTL - GOOD].PartNum, [PARTMTL -
GOOD].MtlPartNum, [PARTMTL - GOOD].MtlSeq
FROM [PARTMTL - GOOD];


If i have a "parent part" which is made up of two "child parts" i would have
two lines below. I need to create a sequence field and have it say 10 for
the first line and 20 for the second. I could have a 15 lines with the same
"Parent part number" with 17 different "child parts" In this case I would
need to create a sequence of 10,20, 30 ..... 160, 170. I coudl also have on
"Parent part" and one "child part" It's seq would need to be 10.

P/N MtlPart
291H-1 100SJ34191225
291H-1 KEVLAR

I tried to use the code you sent and this is what I came up with. I believe
you are just counting the reocurences of the P/N and * by 10. That makes
sence with the AutoNumber but when I run this I am getting an Syntex Error
from the last "FROM"

SELECT [PARTMTL - GOOD].Number01, [PARTMTL - GOOD].PartNum, [PARTMTL -
GOOD].MtlPartNum, [PARTMTL - GOOD].MtlSeq
FROM [PARTMTL - GOOD], (SELECT (Count([PartNum]) + 1)*10 FROM
[PARTMTL - GOOD] AS PNVar1 WHERE
[PARTMTL - GOOD].Number01 > PNVar1.Number01 and
PNVar1.[PartNum] = [PARTMTL - GOOD].[PartNum]) AS [seq123]
FROM [PARTMTL - GOOD]
order by [PartNum]

FYI, The PartNumb field is sorted asending and the autonumber is Number01.
The auto number was created when the table was populated so they are both in
order (Partnum and Number01).

Thanks.

 

[Lord Kelvan]

it is because you have the from in the wrong place

SELECT [PARTMTL - GOOD].Number01, [PARTMTL - GOOD].PartNum, [PARTMTL -
GOOD].MtlPartNum, [PARTMTL - GOOD].MtlSeq, (SELECT (Count([PartNum]) +
1)*10 FROM
[PARTMTL - GOOD] AS PNVar1 WHERE
[PARTMTL - GOOD].Number01 > PNVar1.Number01 and
PNVar1.[PartNum] = [PARTMTL - GOOD].[PartNum]) AS [seq123]
FROM [PARTMTL - GOOD]
order by [PartNum]

 

[Scott]

This seemed to wrok. I had to fiddle with it in SQL design mode. That's
what pointed out my syntax problem.

SELECT [PARTMTL - GOOD].Number01, [PARTMTL - GOOD].PartNum, [PARTMTL -
GOOD].MtlPartNum, [PARTMTL - GOOD].MtlSeq, (SELECT (Count([PartNum]) +1)*10
FROM
[PARTMTL - GOOD] AS PNVar1 WHERE
[PARTMTL - GOOD].Number01 > PNVar1.Number01 and
PNVar1.[PartNum] = [PARTMTL - GOOD].[PartNum]) AS seq123
FROM [PARTMTL - GOOD]
ORDER BY [PARTMTL - GOOD].[PartNum];

 

[Scott]

I started to use this a bit more to day and it very processer intensive and
takes for ever to run. Any ideas how to make this run a bit faster.

I have done other stuff with VB and For Each loops. Can I read in
[line1].[p/n] to a var and [line2].[p/n] to a var and run an if statment. I
know that Access is not Excell but it woudl be just like the IF statmetn I
was useig in Excell (conceptually).

 

[Michel Walsh]

You may try a join rather than a correlated sub query:

SELECT a.Number01, a.PartNum, a.MtlPartNum, a.MtlSeq, 10*COUNT(*) AS seq
FROM [PARTMTL - GOOD] AS a INNER JOIN [PARTMTL - GOOD] AS b
ON a.PartNum = b.PartNum
WHERE a.Number01 <= b.Number01
GROUP BY a.PartNum, a.Number01, a.MtlPartNum, a.MtlSeq



Note that it may be

WHERE a.MtlPartNum <= b.MtlPartNum

rather than involving Number01 field


That is still a little bit slow (have indexes on PartNum and, as example,
Number01), but is probably faster than using the sub-query.

You can make it a little bit faster (but probably not perceptible), with:

SELECT a.Number01, a.PartNum, LAST(a.MtlPartNum), LAST(a.MtlSeq),
10*COUNT(*) AS seq
FROM [PARTMTL - GOOD] AS a INNER JOIN [PARTMTL - GOOD] AS b
ON a.PartNum = b.PartNum
WHERE a.Number01 <= b.Number01
GROUP BY a.PartNum, a.Number01



If it is still to slow, there is another possibility, appending data into a
temporary table, but that involves more work to 'program' it.


Vanderghast, Access MVP

 

[Scott]

Can't I use VB to run the table if the parrent P/N's are all together? I
don't care about the MTLPartNum. Only SEQuence numbers that I will generate
for the number (or Count) of P/N's.

 

[Lord Kelvan]

not really because of how it has to work as i said databses can only
see 1 record at a time so a sub query allows it to see two within
regards.

how it is processor intensive are you running reports off it or
something.

Regards
Kelvan

 

[Scott]

3,000 records per 2 min

I have a brand new Ltitude D830 core2 4gb ram. w/ vista & office 2007.

 

[Michel Walsh]

You can loop over a recordset, and that may be faster, indeed, in this case.

First, open the recordset with the appropriate ORDER BY sequence:


Dim rst AS DAO.Recordset
Set rst=CurrentDb.OpenRecordset( "SELECT * FROM tableName ORDER BY a,
b", dbOpenDynaset)
Dim previousA AS String
Dim previousB AS String
Dim counter AS long : counter = 10


Loop over the recordset, if field 'a' has the same value than the previous
one, increment the counter and update the sequence field, otherwise,
reinitialize the sequence:

While Not rst.EOF
if rst.Fields("a") = previousA AND rst.Fields("b") =
previousB then
rst.Edit
rst.Fields("Sequence") = counter
counter=counter+10
rst.update
else
previousA=rst.Fields("a")
previousB=rst.Fields("b")
counter =10
end if

rst.MoveNext
Wend



Note that it is extremely important that you include the ORDER BY when you
open the recordset, to have the 'records' properly 're-group-ed'.


Vanderghast, Access MVP

 

[Lord Kelvan]

well your problem is most likly vista and office 2007 =P

cause that query takes no time at all for me even on a table of >50000
records and thats with a computer that has a celeron cpu and 256mb of
ram and i am running windows 2000 and access 97

sorry

Regards
Kelvan