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
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