Not show result if it doesn't exist query?

[vbahelp07]

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.

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

 

[r937]

SELECT B.Bill, B.Component
  FROM CurrentBill AS B
LEFT OUTER
  JOIN CurrentBill AS C 
    ON C.Bill = B.Component
 WHERE C.Bill IS NULL
ORDER 
    BY B.Bill, B.Component

r937.com | rudy.ca

 

[vbahelp07]

Hi there,
thank you BUT ...
it didn't work or I don't have something right.

When I look at the data in the Bill table (B)
let's look at Bill number V3

it shows Component 87-3300

Bill	Component
V3	87-33000

the result of the code provide still shows me:

Bill	Component
V3	87-33000

87-3300 can't exist in the Bill field from the Bill table.

when you look up 87-3300 in the Bill field it is not there.

so i need the query to not show the Bills where the Component does not exist as a Bill in the same table.

HTH??

 

[vbahelp07]

i have this also

SELECT CurrentBill.Bill, Trim([currentBill].[Component]) AS Component, CurrentBill.QtyPerBill AS QtyComp
FROM CurrentBill LEFT JOIN CurrentBill AS CurrentBill_1 ON CurrentBill.Component = CurrentBill_1.Bill
WHERE (((CurrentBill_1.Bill) Is Null))
GROUP BY CurrentBill.Bill, Trim([currentBill].[Component]), CurrentBill.QtyPerBill
HAVING (((CurrentBill.Bill)="V3"))
ORDER BY CurrentBill.Bill, Trim([currentBill].[Component]);

i need it to show all Bill and Component BUT not where the Component exists as a Bill from the same table CurrentBill.

 

[r937]

okay, please have a look at these two statements:

1. give me the a list of Bills and Components where the Component does not exist as a Bill from the same table.

2. need the query to not show the Bills where the Component does not exist as a Bill in the same table.

these statements are contradictory

which one do you really want?


r937.com | rudy.ca

 

[vbahelp07]

oops,

list of Bills and Components where the Component does not exist as a Bill from the same table (CurrentBill)

 

[r937]

okay, then, please try my query again

r937.com | rudy.ca

 

[vbahelp07]

i did, it didn't work.

87-3300 still shows.

this is a component that does not exist in the Bill field in the same table.

 

[r937]

this is a component that [red]does not exist[/red] in the Bill field in the same table"

really? maybe that is why the query shows it!!!

you said list of Bills and Components where the Component [red]does not exist[/red] as a Bill from the same table

r937.com | rudy.ca

 

[vbahelp07]

right. so 87-3300 is a component where it does not exist as a Bill.

it should not show in my result. why is it showing?

what am I saying wrong? not sure how to say it. Sorry!!

 

[CaptainD]

I'm confused as well.

Are you saying that some of the records have a value in the "Bill" field that is the same as the "Components" field.

Bill Components
85-2200 85-2200
V-3 87-33000

and your results you want

Bill Components
85-2200 85-2200

 

[vbahelp07]

Yes, there are Component items that also are Bill items!

so a parent Bill V3 has components, say 85-2200

85-2200 is a Component bill and therefore exists as a Bill item.

87-33000 is a Component of V3 as you can see BUT is not a Bill and does not exist as a Bill.


Bill Component
V3 85-2200
85-2200 ##-#### (sub assembly or 2nd level component)

there are indented levels for some Bill Of Materials
1st level is just called the components (child) of the Bill (parent)
2nd level is called the sub-assembly of the components.


Hope this helps better. I am so sorry I am not sure how to explain it another way. I will do my best to answer the questions.

 

[r937]

could you please show us several sample rows of data from the table

make sure that the sample rows include enough bills and components that will illustrate your scenario

then show what results you want the query to produce from the given sample of rows

r937.com | rudy.ca

 

[vbahelp07]

ok,

1st level with the parent

Bill	Component
DCM2000	[b]80-02000[/b]
DCM2000	99-01500
DCM2000	99-01501
DCM2000	99-91509
V3	[b]80-33101[/b]
V3	87-33000
V3	99-32000C
V3	99-50212

2nd level with the sub assembly of the component
only the bold items from above shows as a Bill along with their sub-assemblies.

Bill	        Component
80-02000	71-24500
80-02000	77-10005
80-33101	71-22012
80-33101	77-10005

the none bolded Components from the 1st level, they do not exist as a Bill.

i hope this is a good sampling


THANK YOU for working with me on this! didn't realize it was going to be so many threads. i simply didn't know how to ask the proper question or more is involved.

 

[CaptainD]

If I understand you correctly

You have [Bill] = V-3 with [components] = 85-2200 and if you have "sub components" you place the Components number under [Bill] and give the sub component a number under [Components]

If so, then I question your database design.

I would create a Bill table and a Components table and possibly a sub-components table

Example
Table tBill
iBillID, sBillNumber
Table tComponents
iComponentsID, fkBillID, sComponents, sDescription

Then
SELECT b.sBillNumber, c.sComponents, sDescription
FROM tBill b INNER JOIN tComponents c
ON b.iBillID = c.fkBillID
ORDER BY b.sBillNumber

Something like that.

 

[PHV]

What is the expected result with the sample data posted 12 Jan 08 14:22 ?

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

 

[CaptainD]

Looking at your tables, I assume a bill can have multiple components and each component can have multiple sub-components.

If so, I would break mine down like this

Table tBill
iBillID, sBillNumber

Table tComponents
iComponentsID, fkBillID, sComponents, sDescription

Table tSubComponents
iSubComponentsID, fkComponentsID, sSubComponent, sDescription

Then
SELECT b.sBillNumber, c.sComponents, c.sDescription, sc.sSubComponents, sc.sDescription
FROM tBill b
INNER JOIN tComponents c
ON b.iBillID = c.fkBillID
LEFT JOIN tSubComponents sc
ON c.iComponentsID = fkComponentsID
ORDER BY b.sBillNumber

This should give you only the bills with components and if the components have sub components, it will give you those.

 

[r937]

thank you PHV

dear vbahelp07, you said "the none bolded Components from the 1st level, they do not exist as a Bill" but would you mind terribly to help us understand by please showing the results that you expect from the sample data as posted



r937.com | rudy.ca

 

[CaptainD]

On your table, does this work

SELECT a.Bill, a.Components, b.Components
FROM tBills a, (SELECT Components, bill from tBills) b
WHERE a.components = b.bill
ORDER BY a.bill;

 

[CaptainD]

Just a note, when I close the query, MS Access makes a change to it and it then will not run. If I re-build it as typed, it works as expected.

I'll play with it to see if I can over come Microsofts "help".