count records

[evr72]

Hello,

Is there a way to number each record for example

aaa	1
aaa	2
aaa	3
bbb	1
bbb	2
bbb	3
bbb	4
ccc	1
ccc	2
ddd	1

 

[PHV]

Probably yes.
But nobody here is a mind reader ...

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

 

[evr72]

so sorry about that. I really did explain anything with that. A bit embarrassed for posting something with any explanations or even mention what I have tried.

Here is what I have tried first

SELECT mytbl.Item,Count(mytbl.Item) AS CountOfItem
FROM mytbl
GROUP BY mytbl.Item

but obviously this groups my Item and gives me the count of all of the same. Not sure where to go from there

Thanks and again sorry for the lousy intro.

 

[PHV]

You still explained nothing.
Do a seaech for ranking query.

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

 

[Golom]

Count(Field) just tells you how many non-NULL values there are in the group.

Attempting to assign sequential numbers to different records and restarting the numbering when "Item" changes when all we know is that you have sets of identical values in the Item field is near-impossible with SQL alone. You could drop the table into a recordset and iterate through it assigning such numbers. A ranking query will give you something like

[pre]
Item Number
aaa 1
aaa 1
aaa 1
bbb 4
bbb 4
bbb 4
bbb 4
ccc 8
ccc 8
ddd 10
[/pre]
Probably not very helpful.

 

[PHV]

A ranking query will give you something like
Sorry to disagree.
The OP should give us more info about the scheme of mytbl, ie the PK.

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

 

[Golom]

PHV

You're probably right. I didn't actually run one. In any event, based on the info he did provide, that's what you would get. I know ... I know ... A ranking query requires unique fields to work properly.

You are correct of course ... as you usually are. Without better definition, this is a hopeless endeavor.

 

[evr72]

after PHV suggestion I was looking at ranking queries and Golum you are correct that would not be very helpful,

the engineering team has a table where they enter all their data the table is called tbl1
in this "tbl1" they have the following fields
item
material
insert
operation
qty
tool
tool_description

sometimes the operation could be different for each item.

what I am looking to do is convert the tbl1 into a query where it shows each item, material, insert and operation in a horizontal form instead of a vertical form
so for example if I have this in my "tbl1"

item  material   insert  operation qty tool  tool_description
aaa   ABS         none     tap      1   8-32  H7 4FL
aaa   PHN         steel    drill    1   6-32  Carbide tip
aaa   ABS         none     retap    1   #28   H3 4FL
bbb   ABS         none     retap    1   #28   H3 4FL 
bbb   PHN         steel    drill    1   6-32  Carbide tip
ccc   PHN         steel    drill    1   6-32  Carbide tip

my query would have operation1, operation2, operation3, qty1, qty2,qty3, tool1, tool2, tool3, tool_description1, tool_description2 and tool_description3 for
part "aaa" and so on for the rest of the parts.
So what I was thinking is if I can number the parts in my table then I could do a query with just the parts and say if item = 1 then qty1 or something like that.

Hence the item numbering. not sure if I am in the right path with this any advice is much appreciated.

Thanks!!

​

 

[Golom]

I guess my first reaction is "spread sheet". With combinations of VBA and some arcane SQL you can do something like that but the results are usually less than satisfactory.

I'm still not sure how you order the records. There doesn't seem to be any field that tells you that (for example) the "aaa" records should appear in the order "tap, drill, retap" for the operation field. Relational databases do not guarantee any particular ordering of records.