Record Count for each Record

[gpouliot]

Hi,

I have two tables each record line in each table is identical in table 1 and table 2 just with different data.

I would like to create a query to have a count for each records. So I could then create query matching my count# on each table to manipulate my data.

Current Query Data
<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>PARTTRAN</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>part_number</TH><TH bgColor=#c0c0c0 borderColor=#000000>ship_to_code</TH><TH bgColor=#c0c0c0 borderColor=#000000>employee_number</TH><TH bgColor=#c0c0c0 borderColor=#000000>Pcs</TH><TH bgColor=#c0c0c0 borderColor=#000000>transaction_date</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>544P24143</TD><TD borderColor=#d0d7e5>QJ</TD><TD borderColor=#d0d7e5>039796</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>20100804</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>544P24143</TD><TD borderColor=#d0d7e5>QJ</TD><TD borderColor=#d0d7e5>030953</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>20100804</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>544P24185</TD><TD borderColor=#d0d7e5>QJ</TD><TD borderColor=#d0d7e5>050137</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>20100804</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>544P24143</TD><TD borderColor=#d0d7e5>QJ</TD><TD borderColor=#d0d7e5>058236</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>20100804</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Desired Query Data
<TABLE style="WIDTH: 367pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=489><COLGROUP><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 33pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 width=44>Count</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 66pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=88>part_number</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #eeece1; WIDTH: 68pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=90>ship_to_code</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #eeece1; WIDTH: 96pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=128>employee_number</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #eeece1; WIDTH: 20pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=27>Pcs</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #eeece1; WIDTH: 84pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=112>transaction_date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=88>544P24143</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=90>QJ</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=128>039796</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=27>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=112>20100804</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=88>544P24143</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=90>QJ</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=128>030953</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=27>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=112>20100804</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=88>544P24185</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=90>QJ</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=128>050137</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=27>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=112>20100804</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=88>544P24143</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=90>QJ</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=128>058236</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=27>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=112>20100804</TD></TR></TBODY></TABLE>

Any help would be greatly appreciated.

Thanks

 

[gpouliot]

These are the only two tables. Each record in each table matches the other table. If I want to count each records as a new field in my query 1,2,3,4 ect ect is that possible? This would mean I would need to create two queries base on my two tables and then join them base on the count field?

 

[james_lankford]

so you're saying the first row in PARTTRAN corresponds to the first row in SKIDSUM.
How do you know that ? How can you be sure ?

The first 2 rows in PARTTRAN have the same part_number and transaction_date so they could correspond to any of the 7 rows in SKIDSUM with that part_number and transaction_date.
There doesn't seem to be any way to know for exactly 100% sure which row corresponds to which.

Is it possible to do what you want ? Sure.
Just go into table design for both tables and add an autonumber field then write a query that joins on the autonumber.

Is that wrong and might it give incorrect results ? Yes