Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Not show result if it doesn't exist query?

Status
Not open for further replies.
vbahelp07

vbahelp07

Technical User
Oct 16, 2007
115
US
I'm not sure how to write this query ...

i have a table named CurrentBill with:
Bill
Component

I want to write a query that will give me the a list of Bills and Components where the Component does not exist as a Bill from the same table.

Code: 
SELECT CurrentBill.Bill, CurrentBill.Component
FROM CurrentBill LEFT JOIN CurrentBill AS CurrentBill_1 ON CurrentBill.Component = CurrentBill_1.Bill
WHERE (((CurrentBill_1.Bill) Is Null))
GROUP BY CurrentBill.Bill, currentBill.Component)
ORDER BY CurrentBill.Bill, currentBill.Component);
 
Sort by date Sort by votes
This works

Code: 
SELECT tBills.Bill, tBills.Components, b.Components
FROM tBills, [SELECT Components, bill from tBills]. AS b
WHERE tBills.components = b.bill
ORDER BY tBills.bill;
 
Upvote 0 Downvote
Hello everyone,

i will try to address each post since my last.

Table structure cannot be changed. I am using MS Access to link via ODBC connection to a back office software system that it is as it is.

it has BM1_BillHeader (Bill and Component, etc ...)
BM2_BillDetails (Bill and Components with subassemblies, if that even makes sense!)

These data files has all kinds of other information like comments, etc ... that is not needed.

so i have extracted the data from each.
table names in Access that i am working with are:
CurrentBill and CurrentComp (for the sub assembly info)

this is the modified of CaptainD's query using the actual names I have.
Code: 
SELECT CurrentBill.Bill, CurrentBill.Component, b.Component
FROM CurrentBill, [SELECT Component, bill from CurrentBill]. AS b
WHERE (((CurrentBill.Component)=[b].[bill]))
ORDER BY CurrentBill.Bill;

This only gives me the 80-33101 of V3 and no other components but am getting the sub-assemblies for 80-33101
example (last record for the V3 query)
Code: 
Bill	CurrentBill.Component	b.Component
V3	80-33101	        77-10005

there is actually 181 items that make up the Bill for V3. of which 67 are 1st level and the rest are the 2nd level.

There are some Bills that are used as a Component, etc ...

this could be the confusion and why a Bill would exist as a Component, etc ...

because the record source has so much data, i think that i may get lost if I try to copy and paste all of it.
 
Upvote 0 Downvote
excuse me, but would you please show the results that you want for the sample data that you posted earlier

r937.com | rudy.ca
 
Upvote 0 Downvote
Here is the results from just the CurrentBill against the CurrentBill table

Code: 
Bill	Component	QtyComp
V3	02-18250	7
V3	03-18200	3
V3	03-52221	2
V3	03-77705	1
V3	05-01603	1
V3	05-48012	1
V3	05-48020	3
V3	05-48510	1
V3	05-48517	1
V3	05-48522	2
V3	05-60420	1
V3	05-60435	1
V3	05-64410	2
V3	05-64420	3
V3	05-68415	3
V3	05-68440	1
V3	05-85611	1
V3	06-10060	8
V3	06-10061	4
V3	06-10090	4
V3	06-10133	2
V3	06-10160	4
V3	06-10174	6
V3	06-11903	23
V3	06-11905	0
V3	06-11906	0
V3	06-20010	8
V3	06-30010	8
V3	06-30025	10
V3	06-30030	4
V3	06-30035	6
V3	06-31123	5
V3	06-40070	3
V3	06-45001	1
V3	06-45002	1
V3	06-45003	2
V3	06-50032	3
V3	06-50035	2
V3	06-50051	1
V3	06-50054	6
V3	06-50090	4
V3	06-51123	5
V3	06-57705	1
V3	07-11410	2
V3	07-77710	23
V3	10-00034	4
V3	10-33101D	1
V3	10-33105C	1
V3	10-33108A	1
V3	15-02116	1
V3	15-10714B	1
V3	25-31352	2
V3	65-00034-1	4
V3	65-00127-1	3
V3	65-01127C-1	2
V3	70-28106	1
V3	76-00001	1
V3	76-33100A	1
V3	77-00002	1
V3	77-00051	1
V3	77-99001	1
V3	80-33101	1
[b]V3	87-33000	1
V3 99-32000C 1
V3 99-50212 2
V3 99-92317 1
V3 99-94670 0
[/code]

the bolded row
V3 87-33000 1
should be the only one not showing in this list of the result because 87-33000 does not exist in the Bill field.
 
Upvote 0 Downvote
SELECT CurrentBill.Bill, Trim([currentBill].[Component]) AS Component, CurrentBill.QtyPerBill AS QtyComp
FROM CurrentBill
WHERE (((CurrentBill.Bill)="V3")
ORDER BY CurrentBill.Bill, Trim([currentBill].[Component]);

i put this under WHERE
AND ((Trim([currentBill].[Component]))<>[Bill])) and i get the same result.


 
Upvote 0 Downvote
What about this ?
Code: 
SELECT B.Bill, Trim(B.Component) AS Component, B.QtyPerBill AS QtyComp
FROM CurrentBill AS B INNER JOIN CurrentBill AS C ON Trim(B.Component) = Trim(C.Bill)
WHERE B.Bill = 'V3'
ORDER BY 1,2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hi PHV,

you know what, yours and CaptainD gives me the same result. and by design it truly is only showing the Bill and Component where the Component exists as a Bill.

Code: 
Bill	Component	2ndLevel
V3	02-18250	
V3	03-18200	
V3	03-52221	
V3	03-77705	
V3	05-01603	
V3	05-48012	
V3	05-48020	
V3	05-48510	
V3	05-48517	
V3	05-48522	
V3	05-60420	
V3	05-60435	
V3	05-64410	
V3	05-64420	
V3	05-68415	
V3	05-68440	
V3	05-85611	
V3	06-10060	
V3	06-10061	
V3	06-10090	
V3	06-10133	
V3	06-10160	
V3	06-10174	
V3	06-11903	
V3	06-11905	
V3	06-11906	
V3	06-20010	
V3	06-30010	
V3	06-30025	
V3	06-30030	
V3	06-30035	
V3	06-31123	
V3	06-40070	
V3	06-45001	
V3	06-45002	
V3	06-45003	
V3	06-50032	
V3	06-50035	
V3	06-50051	
V3	06-50054	
V3	06-50090	
V3	06-51123	
V3	06-57705	
V3	07-11410	
V3	07-77710	
V3	10-00034	
V3	10-33101D	
V3	10-33105C	
V3	10-33108A	
V3	15-02116	
V3	15-10714B	
V3	25-31352	
V3	65-00034-1	
V3	65-00127-1	
V3	65-01127C-1	
V3	70-28106	
V3	76-00001	
V3	76-33100A	
V3	77-00002	
V3	77-00051	
V3	77-99001	
V3	80-33101	03-18230
V3	80-33101	03-50135
V3	80-33101	03-63250
V3	80-33101	05-22102
V3	80-33101	06-10050
V3	80-33101	06-30010
V3	80-33101	06-40050
V3	80-33101	06-40060
V3	80-33101	12-57462
V3	80-33101	15-27430
V3	80-33101	21-01804
V3	80-33101	21-31100
V3	80-33101	21-51346
V3	80-33101	21-52546
V3	80-33101	21-70505
V3	80-33101	23-08605
V3	80-33101	23-08609
V3	80-33101	23-11004
V3	80-33101	23-11008
V3	80-33101	23-11010
V3	80-33101	23-12004
V3	80-33101	23-12008
V3	80-33101	23-26401
V3	80-33101	23-91632
V3	80-33101	25-01061
V3	80-33101	25-01123
V3	80-33101	25-01202
V3	80-33101	25-01203
V3	80-33101	25-62656
V3	80-33101	25-76286
V3	80-33101	30-33101G
V3	80-33101	41-10342
V3	80-33101	41-22362
V3	80-33101	41-47242
V3	80-33101	41-47342
V3	80-33101	41-47362
V3	80-33101	42-22045
V3	80-33101	42-82052
V3	80-33101	44-13520
V3	80-33101	45-10052
V3	80-33101	45-12152
V3	80-33101	45-18152
V3	80-33101	45-25152
V3	80-33101	45-27052
V3	80-33101	45-33113
V3	80-33101	45-56152
V3	80-33101	46-10242
V3	80-33101	46-10312
V3	80-33101	46-10561
V3	80-33101	46-22212
V3	80-33101	46-22242
V3	80-33101	46-22312
V3	80-33101	46-22461
V3	80-33101	46-33212
V3	80-33101	46-47212
V3	80-33101	47-10012
V3	80-33101	47-22225
V3	80-33101	47-22260
V3	80-33101	47-47061
V3	80-33101	49-10451
V3	80-33101	49-15052
V3	80-33101	50-10021
V3	80-33101	50-10045
V3	80-33101	50-10055
V3	80-33101	50-10065
V3	80-33101	50-15035
V3	80-33101	50-15055
V3	80-33101	50-22035
V3	80-33101	50-22045
V3	80-33101	50-22055
V3	80-33101	50-27025
V3	80-33101	50-30055
V3	80-33101	50-33035
V3	80-33101	50-47035
V3	80-33101	50-47045
V3	80-33101	50-47055
V3	80-33101	50-47065
V3	80-33101	50-56025
V3	80-33101	52-22055
V3	80-33101	53-47035
V3	80-33101	53-47045
V3	80-33101	56-35010
V3	80-33101	56-35025
V3	80-33101	58-00035
V3	80-33101	58-10035
V3	80-33101	58-10045
V3	80-33101	58-10055
V3	80-33101	58-22025
V3	80-33101	58-22035
V3	80-33101	58-27025
V3	80-33101	58-47025
V3	80-33101	58-47035
V3	80-33101	60-50200
V3	80-33101	60-55500
V3	80-33101	60-61380
V3	80-33101	60-75320
V3	80-33101	60-75330
V3	80-33101	60-75340
V3	80-33101	60-75350
V3	80-33101	60-75535
V3	80-33101	60-78050
V3	80-33101	61-10000
V3	80-33101	62-12000
V3	80-33101	62-19140
V3	80-33101	62-25010
V3	80-33101	62-55500
V3	80-33101	62-74014
V3	80-33101	62-74595
V3	80-33101	62-74597
V3	80-33101	62-87764
V3	80-33101	70-05305
V3	80-33101	71-09221
V3	80-33101	71-14050
V3	80-33101	71-14060
V3	80-33101	71-14070
V3	80-33101	71-22012
V3	80-33101	77-10005
V3	87-33000	
V3	99-32000C	
V3	99-50212	
V3	99-92317	
V3	99-94670

Now I am not sure, but when you do a Production Entry all of these items show EXCEPT the 87-33000

it seems I am not on the right track in figuring out WHY this 87-33000 does not show during a manual production entry.

End scope, i am trying to help so they don't have to do a manual production entry. They are just scanning the parent Bill. in this case the V3 then with an import job it should import in all the components that it takes to build the V3. So comparing to the system's program of the components it generates vs what I mustered up using Access, they do not match.

For only V3 thus far, 87-33000 is something I have to figure out how to NOT show in the file that is used to import into the system's Production Entry file so it matches what the Production Entry file creates when done manually.


The system Production Entry only generated:
Code: 
02-18250            
03-18200            
03-18230            
03-50135            
03-52221            
03-63250            
03-77705            
05-01603            
05-22102            
05-48012            
05-48020            
05-48510            
05-48517            
05-48522            
05-60420            
05-60435            
05-64410            
05-64420            
05-68415            
05-68440            
05-85611            
06-10050            
06-10060            
06-10061            
06-10090            
06-10133            
06-10160            
06-10174            
06-11903            
06-11905            
06-11906            
06-20010            
06-30010            
06-30025            
06-30030            
06-30035            
06-31123            
06-40050            
06-40060            
06-40070            
06-45001            
06-45002            
06-45003            
06-50032            
06-50035            
06-50051            
06-50054            
06-50090            
06-51123            
06-57705            
07-11410            
07-77710            
10-00034            
10-33101D           
10-33105C           
10-33108A           
12-57462            
15-02116            
15-10714B           
15-27430            
21-01804            
21-31100            
21-51346            
21-52546            
21-70505            
23-08605            
23-08609            
23-11004            
23-11008            
23-11010            
23-12004            
23-12008            
23-26401            
23-91632            
25-01061            
25-01123            
25-01202            
25-01203            
25-31352            
25-62656            
25-76286            
30-33101G           
41-10342            
41-22362            
41-47242            
41-47342            
41-47362            
42-22045            
42-82052            
44-13520            
45-10052            
45-12152            
45-18152            
45-25152            
45-27052            
45-33113            
45-56152            
46-10242            
46-10312            
46-10561            
46-22212            
46-22242            
46-22312            
46-22461            
46-33212            
46-47212            
47-10012            
47-22225            
47-22260            
47-47061            
49-10451            
49-15052            
50-10021            
50-10045            
50-10055            
50-10065            
50-15035            
50-15055            
50-22035            
50-22045            
50-22055            
50-27025            
50-30055            
50-33035            
50-47035            
50-47045            
50-47055            
50-47065            
50-56025            
52-22055            
53-47035            
53-47045            
56-35010            
56-35025            
58-00035            
58-10035            
58-10045            
58-10055            
58-22025            
58-22035            
58-27025            
58-47025            
58-47035            
60-50200            
60-55500            
60-61380            
60-75320            
60-75330            
60-75340            
60-75350            
60-75535            
60-78050            
61-10000            
62-12000            
62-19140            
62-25010            
62-55500            
62-74014            
62-74595            
62-74597            
62-87764            
65-00034-1          
65-00127-1          
65-01127C-1         
70-05305            
70-28106            
71-09221            
71-14050            
71-14060            
71-14070            
71-22012            
76-00001            
76-33100A           
77-00002            
77-00051            
77-10005            
77-99001            
99-32000C           
99-50212            
99-92317            
99-94670

my query generated:
Code: 
02-18250
03-18200
03-18230
03-50135
03-52221
03-63250
03-77705
05-01603
05-22102
05-48012
05-48020
05-48510
05-48517
05-48522
05-60420
05-60435
05-64410
05-64420
05-68415
05-68440
05-85611
06-10050
06-10060
06-10061
06-10090
06-10133
06-10160
06-10174
06-11903
06-11905
06-11906
06-20010
06-30010
06-30025
06-30030
06-30035
06-31123
06-40050
06-40060
06-40070
06-45001
06-45002
06-45003
06-50032
06-50035
06-50051
06-50054
06-50090
06-51123
06-57705
07-11410
07-77710
10-00034
10-33101D
10-33105C
10-33108A
12-57462
15-02116
15-10714B
15-27430
21-01804
21-31100
21-51346
21-52546
21-70505
23-08605
23-08609
23-11004
23-11008
23-11010
23-12004
23-12008
23-26401
23-91632
25-01061
25-01123
25-01202
25-01203
25-31352
25-62656
25-76286
30-33101G
41-10342
41-22362
41-47242
41-47342
41-47362
42-22045
42-82052
44-13520
45-10052
45-12152
45-18152
45-25152
45-27052
45-33113
45-56152
46-10242
46-10312
46-10561
46-22212
46-22242
46-22312
46-22461
46-33212
46-47212
47-10012
47-22225
47-22260
47-47061
49-10451
49-15052
50-10021
50-10045
50-10055
50-10065
50-15035
50-15055
50-22035
50-22045
50-22055
50-27025
50-30055
50-33035
50-47035
50-47045
50-47055
50-47065
50-56025
52-22055
53-47035
53-47045
56-35010
56-35025
58-00035
58-10035
58-10045
58-10055
58-22025
58-22035
58-27025
58-47025
58-47035
60-50200
60-55500
60-61380
60-75320
60-75330
60-75340
60-75350
60-75535
60-78050
61-10000
62-12000
62-19140
62-25010
62-55500
62-74014
62-74595
62-74597
62-87764
65-00034-1
65-00127-1
65-01127C-1
70-05305
70-28106
71-09221
71-14050
71-14060
71-14070
71-22012
76-00001
76-33100A
77-00002
77-00051
77-10005
77-99001
[b]87-33000[/b]
99-32000C
99-50212
99-92317
99-94670

the 87-33000 is the discrepancy.
 
Upvote 0 Downvote
End scope, i am trying to help so they don't have to do a manual production entry. They are just scanning the parent Bill. in this case the V3 then with an import job it should import in all the components that it takes to build the V3. So comparing to the system's program of the components it generates vs what I mustered up using Access, they do not match.
Click to expand...
i'm sorry, i am not going to be able to help you at all


i have no clue what you are talking about, and you have now ignored my question a couple of times

all i can say is good luck and goodbye

r937.com | rudy.ca
 
Upvote 0 Downvote
You are making this difficult in that you add fields (or excluded them) such as the quantity field.

<Here is the results from just the CurrentBill against the CurrentBill table> Which now has a quantity. Other then that, the results of the two queries (Mine and PHV's)show what I think you asked for.

Then you show a single column results that we have no clue what it is from or what it means.

What query gives that result?

If you are having problems with a record showing up that you do not want, you need to show all the values that are tied to it.

So, if you have one table such as "CurrentBill" then we need to see all if the fields from that table and all the records with 87-33000.

(I don't like using "SELECT * but we need everything)

Run this and post back the results. If you have more then one table that you are using, post that as well

Code: 
SELECT * FROM CurrentBill WHERE TRIM([Bill]) = '87-33000' OR TRIM(Component) = '87-33000';

Is there only the one table or do you have others?
 
Upvote 0 Downvote
r937,
thank you for trying. i am sorry for the confusion and don't EXPECT anyone to truly understand but what i could do to try and get what's going on understood so i can get the help looks to be my 1st step in getting help.


CaptainD,
thank you for your patience and attempt to help me with the add'l questions to better help you understand the situation.

i thought by only showing the actual data regardless of the other fields would be enough as I didn't want to confuse the matter by having too much information. but now it seems it's the other way around. leaving info out has caused more confusion.

ok, i have 2 tables I am working with as there are 2 tables for the backoffice system that stores all the data for a Bill Of Material (BOM)

the 2 tables in Access that I extracted the data from the backoffice systems are named CurrentBill and CurrentComp from BM1_Header and BM2_Details (backoffice table names).

Now, not sure if how I extract the data from the backoffice tables matters as the data is the same. It's just faster to work with running queries, especially during business hours when others are in the system. It's really slow.
However, i did link the BM1 to BM2 on the Bill (Parent) as the BM1 only hold the Bill items.

Basically the BillNumber and Revision fields are really the only fields I used from BM1. Then linked on BillNumber to the BM2 I get the Component items and QtyPerBill.

QtyPerBill stores how many component items each (Child) it needs to make the Bill item.

SO with the 1st query linking BM1 to BM2, i extract BillNumber, CurrentRevision, Component, QtyPerBill into the table CurrentBill.
Code: 
SELECT
    BM1_BillMaterialsHeader."BillNumber", BM1_BillMaterialsHeader."CurrentRevision", BM2_BillMaterialsDetail."Component", BM2_BillMaterialsDetail."QtyPerBill", 
FROM
    "BM2_BillMaterialsDetail" BM2_BillMaterialsDetail,
    "BM1_BillMaterialsHeader" BM1_BillMaterialsHeader,
WHERE
    BM2_BillMaterialsDetail."BillNumber" = BM1_BillMaterialsHeader."BillNumber" AND
    BM2_BillMaterialsDetail."Revision" = BM1_BillMaterialsHeader."Revision" AND
    BM2_BillMaterialsDetail."Revision" = BM1_BillMaterialsHeader."CurrentRevision"

My next query i extract the data into CurrentComponent table.
Code: 
SELECT
    BM2_BillMaterialsDetail."BillNumber", BM2_BillMaterialsDetail."Revision", BM2_BillMaterialsDetail."Component", BM2_BillMaterialsDetail."QtyPerBill
FROM
    "BM2_BillMaterialsDetail" BM2_BillMaterialsDetail,


Here's the result from the query you are asking about:
Code: 
Bill	CurrentRevision	Component	QtyPerBill
V3	B  	        87-33000	1
V3-E	B  	        87-33000	1
X-100B	001	        87-33000	1
This shows that the component 87-33000 are used in the Bill V3, V3-E, and X-100B.

THANK YOU!
 
Upvote 0 Downvote
CaptainD,
also, here are the queries that I linked to the Production scan file. This and another query creates a table which is then exported as a .csv that is used to import into the backoffice system and into the Production Entry program.
Code: 
INSERT INTO ProductionEntryDetails ( Bill, CurrentRevision, Component, QtyComp )
SELECT CurrentBill.Bill, CurrentBill.CurrentRevision, currentBill.Component, CurrentBill.QtyPerBill AS QtyComp
FROM (ProductionScanSummary INNER JOIN CurrentBill ON ProductionScanSummary.BillNo = CurrentBill.Bill) LEFT JOIN CurrentBill AS CurrentBill_1 ON CurrentBill.Component = CurrentBill_1.Bill
WHERE (((CurrentBill_1.Bill) Is Null))
GROUP BY CurrentBill.Bill, CurrentBill.CurrentRevision, currentBill.Component), CurrentBill.QtyPerBill
ORDER BY CurrentBill.Bill, Trim([currentBill].[Component]);

then to get the other part of components (sub-assemblies if you will)
Code: 
INSERT INTO ProductionEntryDetails ( Bill, CurrentRevision, Component, QtyComp )
SELECT CurrentBill.Bill, CurrentBill.CurrentRevision, CurrentComp.Component AS 2ndLevel, CurrentComp.QtyPerBill AS 2ndCompQty
FROM (ProductionScanSummary INNER JOIN CurrentBill ON ProductionScanSummary.BillNo = CurrentBill.Bill) INNER JOIN CurrentComp ON CurrentBill.Component = CurrentComp.Bill
GROUP BY CurrentBill.Bill, CurrentBill.CurrentRevision, CurrentComp.Component, CurrentComp.QtyPerBill
ORDER BY CurrentBill.Bill, CurrentComp.Component;

After running these queries I get:
Code: 
Bill	CurrentRevision	Component	QtyComp
V3	B  	02-18250	7
V3	B  	03-18200	3
V3	B  	03-52221	2
V3	B  	03-77705	1
V3	B  	05-01603	1
V3	B  	05-48012	1
V3	B  	05-48020	3
V3	B  	05-48510	1
V3	B  	05-48517	1
V3	B  	05-48522	2
V3	B  	05-60420	1
V3	B  	05-60435	1
V3	B  	05-64410	2
V3	B  	05-64420	3
V3	B  	05-68415	3
V3	B  	05-68440	1
V3	B  	05-85611	1
V3	B  	06-10060	8
V3	B  	06-10061	4
V3	B  	06-10090	4
V3	B  	06-10133	2
V3	B  	06-10160	4
V3	B  	06-10174	6
V3	B  	06-11903	23
V3	B  	06-11905	0
V3	B  	06-11906	0
V3	B  	06-20010	8
V3	B  	06-30010	8
V3	B  	06-30025	10
V3	B  	06-30030	4
V3	B  	06-30035	6
V3	B  	06-31123	5
V3	B  	06-40070	3
V3	B  	06-45001	1
V3	B  	06-45002	1
V3	B  	06-45003	2
V3	B  	06-50032	3
V3	B  	06-50035	2
V3	B  	06-50051	1
V3	B  	06-50054	6
V3	B  	06-50090	4
V3	B  	06-51123	5
V3	B  	06-57705	1
V3	B  	07-11410	2
V3	B  	07-77710	23
V3	B  	10-00034	4
V3	B  	10-33101D	1
V3	B  	10-33105C	1
V3	B  	10-33108A	1
V3	B  	15-02116	1
V3	B  	15-10714B	1
V3	B  	25-31352	2
V3	B  	65-00034-1	4
V3	B  	65-00127-1	3
V3	B  	65-01127C-1	2
V3	B  	70-28106	1
V3	B  	76-00001	1
V3	B  	76-33100A	1
V3	B  	77-00002	1
V3	B  	77-00051	1
V3	B  	77-99001	1
[b]V3	B  	87-33000	1[/b]
V3	B  	99-32000C	1
V3	B  	99-50212	2
V3	B  	99-92317	1
V3	B  	99-94670	0
V3	B  	03-18230	2
V3	B  	03-50135	1
V3	B  	03-63250	8
V3	B  	05-22102	12
V3	B  	06-10050	8
V3	B  	06-30010	8
V3	B  	06-40050	8
V3	B  	06-40060	8
V3	B  	12-57462	2
V3	B  	15-27430	1
V3	B  	21-01804	1
V3	B  	21-31100	1
V3	B  	21-51346	5
V3	B  	21-52546	3
V3	B  	21-70505	3
V3	B  	23-08605	2
V3	B  	23-08609	1
V3	B  	23-11004	7
V3	B  	23-11008	8
V3	B  	23-11010	4
V3	B  	23-12004	1
V3	B  	23-12008	1
V3	B  	23-26401	4
V3	B  	23-91632	5
V3	B  	25-01061	3
V3	B  	25-01123	5
V3	B  	25-01202	3
V3	B  	25-01203	3
V3	B  	25-62656	2
V3	B  	25-76286	1
V3	B  	30-33101G	1
V3	B  	41-10342	1
V3	B  	41-22362	1
V3	B  	41-47242	3
V3	B  	41-47342	7
V3	B  	41-47362	4
V3	B  	42-22045	3
V3	B  	42-82052	2
V3	B  	44-13520	6
V3	B  	45-10052	3
V3	B  	45-12152	2
V3	B  	45-18152	1
V3	B  	45-25152	5
V3	B  	45-27052	1
V3	B  	45-33113	2
V3	B  	45-56152	9
V3	B  	46-10242	11
V3	B  	46-10312	5
V3	B  	46-10561	1
V3	B  	46-22212	12
V3	B  	46-22242	1
V3	B  	46-22312	2
V3	B  	46-22461	5
V3	B  	46-33212	2
V3	B  	46-47212	7
V3	B  	47-10012	2
V3	B  	47-22225	1
V3	B  	47-22260	3
V3	B  	47-47061	2
V3	B  	49-10451	2
V3	B  	49-15052	2
V3	B  	50-10021	4
V3	B  	50-10045	4
V3	B  	50-10055	19
V3	B  	50-10065	5
V3	B  	50-15035	4
V3	B  	50-15055	2
V3	B  	50-22035	4
V3	B  	50-22045	6
V3	B  	50-22055	11
V3	B  	50-27025	3
V3	B  	50-30055	6
V3	B  	50-33035	1
V3	B  	50-47035	7
V3	B  	50-47045	9
V3	B  	50-47055	13
V3	B  	50-47065	2
V3	B  	50-56025	1
V3	B  	52-22055	5
V3	B  	53-47035	2
V3	B  	53-47045	1
V3	B  	56-35010	1
V3	B  	56-35025	4
V3	B  	58-00035	1
V3	B  	58-10035	1
V3	B  	58-10045	10
V3	B  	58-10055	1
V3	B  	58-22025	1
V3	B  	58-22035	5
V3	B  	58-27025	3
V3	B  	58-47025	7
V3	B  	58-47035	3
V3	B  	60-50200	4
V3	B  	60-55500	5
V3	B  	60-61380	1
V3	B  	60-75320	1
V3	B  	60-75330	1
V3	B  	60-75340	1
V3	B  	60-75350	3
V3	B  	60-75535	1
V3	B  	60-78050	2
V3	B  	61-10000	7
V3	B  	62-12000	1
V3	B  	62-19140	6
V3	B  	62-25010	1
V3	B  	62-55500	3
V3	B  	62-74014	1
V3	B  	62-74595	1
V3	B  	62-74597	1
V3	B  	62-87764	1
V3	B  	70-05305	6
V3	B  	71-09221	2
V3	B  	71-14050	2
V3	B  	71-14060	10
V3	B  	71-14070	11
V3	B  	71-22012	1
V3	B  	77-10005	1

the bolded section for 87-33000 i need to exclude for whatever reason because this does not exist in the system's Production Entry when exploding the V3 within the system. hope that made sense. and seriously, i cannot see what is special about this particular item for V3 that should not explode. Because when I run a system Bill report i see 87-33000.

I have asked the Production Manager if they might know but most likely I will not get an answer until next Wednesday when she is back.
 
Upvote 0 Downvote
Let see, you have two Insert queries that place data into a table called "ProductionEntryDetails"

INSERT INTO ProductionEntryDetails ( Bill, CurrentRevision, Component, QtyComp )
Click to expand...

And then you are just running a simple select statement to get the last results you posted.

Is that correct?

If so:

Have you tried running the "SELECT" queries that do the insert to see what they are "Inserting"?

My guess is, one of those queries is inserting into the table:

V3 B 87-33000 1
 
Upvote 0 Downvote
yes, correct.

it's the 1st one that is inserting the following:
V3 B 87-33000 1
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
695
strongm
strongm
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
4K
combo
combo
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
1K
TheresAlwaysAWay
TheresAlwaysAWay
Moss100
  • Locked
  • Question
Append query dropping leading zero 2
Replies
4
Views
890
Andrzejek
Andrzejek
JimStrand
  • Locked
  • Question
Crosstab query - returning 1 record per week instead of 1 record
Replies
1
Views
714
JimStrand
JimStrand

Part and Inventory Search

Sponsor

Back
Top