How to reteive data of a primary key with duplicate value

[Joelle]

Hi all,i will strongly appreciate if someone could help me,to figure out how i can handel my problem.
I have this report :" RequirementQuer y_Report" and i have a command button that opens this report according certain conditions.
My report hodls all information regarding the requirements in my database such as the producs,status, and release version( this last three are listed in a listbox each)
so whenever i select a certain product,with a certain status and a certain release value i will get the report.
But my problem is, some requirements have 2 products and whenever i select one of those products i do not want to have that requirements because it has an other product.but whenever i select both product then i wanna see the requirement.
Here below is the code i wrote,it works only when i select one of the product that a requirement has.
could some one help me figure out how to handle that.
i think it should be a sql command with a where clause but i do not know what to writte in there.

[CODE=vb]Private Sub Generate_Report _Click()

Dim var As Variant ' Product Selected
Dim status As Variant ' Status selected
Dim Release As Variant 'Release version selected
Dim strf As String ' holds Report filter
Dim iCounter As Integer 'a counter

iCounter = 1 'Initialize it to 1 to be able to compare it with the last selected item

If Not IsNull(var) Then
If Not IsNull(status) Then
If Not IsNull(Release) Then

strf = " ( "
For Each var In Me.ProductList. ItemsSelected
strf = strf + "RequirementQue ry.ProductTag = '" + (Me.ProductList .Column(1, var)) + "' "

If iCounter < Me.ProductList. ItemsSelected.C ount Then ' As long as icounter<the last Prod selected; add OR
strf = strf + "OR "
End If
iCounter = iCounter + 1
Next var

strf = strf + " ) AND ( "

iCounter = 1 'Initialize icounter again otherwise it will still have the previous value

For Each status In Me.StatusList.I temsSelected
strf = strf + "RequirementQue ry.StatusTag= '" + (Me.StatusList. Column(1, status)) + "'"

If iCounter < Me.StatusList.I temsSelected.Co unt Then ' As long as iCouter< the last status selected add OR
strf = strf + " OR "
End If
iCounter = iCounter + 1
Next status
strf = strf + " )"

strf = strf + " AND ( "
iCounter = 1
For Each Release In Me.ReleaseList. ItemsSelected
strf = strf + "RequirementQue ry.ReleaseVersi on= '" + (Me.ReleaseList .Column(1, Release)) + " ' "

If iCounter < Me.ReleaseList. ItemsSelected.C ount Then
strf = strf + " OR "
End If
iCounter = iCounter + 1
Next Release

strf = strf + " )"

DoCmd.OpenRepor t "RequirementQue ry_Report", acViewPreview, , "( " + strf + " )"

Else
MsgBox "Provide a Release version"
Exit Sub
End If
Else

Exit Sub

End If
End If

End sub[/CODE]

Please help.
Best Regards,
Joelle

[PianoMan64]

Hi all,i will strongly appreciate if someone could help me,to figure out how i can handel my problem.
I have this report :" RequirementQuer y_Report" and i have a command button that opens this report according certain conditions.
My report hodls all information regarding the requirements in my database such as the producs,status, and release version( this last three are listed in a listbox each)
so whenever i select a certain product,with a certain status and a certain release value i will get the report.
But my problem is, some requirements have 2 products and whenever i select one of those products i do not want to have that requirements because it has an other product.but whenever i select both product then i wanna see the requirement.
Here below is the code i wrote,it works only when i select one of the product that a requirement has.
could some one help me figure out how to handle that.
i think it should be a sql command with a where clause but i do not know what to writte in there.

Private Sub Generate_Report _Click()

Dim var As Variant ' Product Selected
Dim status As Variant ' Status selected
Dim Release As Variant 'Release version selected
Dim strf As String ' holds Report filter
Dim iCounter As Integer 'a counter

iCounter = 1 'Initialize it to 1 to be able to compare it with the last selected item

If Not IsNull(var) Then
If Not IsNull(status) Then
If Not IsNull(Release) Then

strf = " ( "
For Each var In Me.ProductList. ItemsSelected
strf = strf + "RequirementQue ry.ProductTag = '" + (Me.ProductList .Column(1, var)) + "' "

If iCounter < Me.ProductList. ItemsSelected.C ount Then ' As long as icounter<the last Prod selected; add OR
strf = strf + "OR "
End If
iCounter = iCounter + 1
Next var

strf = strf + " ) AND ( "

iCounter = 1 'Initialize icounter again otherwise it will still have the previous value

For Each status In Me.StatusList.I temsSelected
strf = strf + "RequirementQue ry.StatusTag= '" + (Me.StatusList. Column(1, status)) + "'"

If iCounter < Me.StatusList.I temsSelected.Co unt Then ' As long as iCouter< the last status selected add OR
strf = strf + " OR "
End If
iCounter = iCounter + 1
Next status
strf = strf + " )"

strf = strf + " AND ( "
iCounter = 1
For Each Release In Me.ReleaseList. ItemsSelected
strf = strf + "RequirementQue ry.ReleaseVersi on= '" + (Me.ReleaseList .Column(1, Release)) + " ' "

If iCounter < Me.ReleaseList. ItemsSelected.C ount Then
strf = strf + " OR "
End If
iCounter = iCounter + 1
Next Release

strf = strf + " )"

DoCmd.OpenRepor t "RequirementQue ry_Report", acViewPreview, , "( " + strf + " )"

Else
MsgBox "Provide a Release version"
Exit Sub
End If
Else

Exit Sub

End If
End If

End sub

Please help.
Best Regards,
Joelle

Simply Joelle,

You need to put conditional statement for the second half of your SQL Statement, so that if you don't select anything on the second Listbox, then don't include the "AND (" and simply replace it with the ")" to close it up. then you'll get a valid SQL Statement.

Hope that helps,

Joe P.

[Joelle]

Simply Joelle,

You need to put conditional statement for the second half of your SQL Statement, so that if you don't select anything on the second Listbox, then don't include the "AND (" and simply replace it with the ")" to close it up. then you'll get a valid SQL Statement.

Hope that helps,

Joe P.

Hi PainoMan64,
Thank you for your reply but my problem was not if i do not select something from the second or third listbox.
May be i did not explain my problem well,In my database i have a tables named Requirements, Products.there is a many-to-many relation between those two which means that i have an intermediate table named ProductRequirem ents that show which requirement belongs to which product.i do also have a table named Object which holds pictures(this ones have OLE as datatype) the Object table has also a many-to-many relationship with the Requirements table,so i have also an intermediate table named ObectRequiremen ts. I do have other table too but they have a one to many relationship with requirements table.So i made a query based on those tables and i generated a report for that.
What i want now is when ever i select "Product A "i do want to have the report with all requirements related to Product A if and only if those requirements are not related to any other product.
Thank again
Joelle

[PianoMan64]

Hi PainoMan64,
Thank you for your reply but my problem was not if i do not select something from the second or third listbox.
May be i did not explain my problem well,In my database i have a tables named Requirements, Products.there is a many-to-many relation between those two which means that i have an intermediate table named ProductRequirem ents that show which requirement belongs to which product.i do also have a table named Object which holds pictures(this ones have OLE as datatype) the Object table has also a many-to-many relationship with the Requirements table,so i have also an intermediate table named ObectRequiremen ts. I do have other table too but they have a one to many relationship with requirements table.So i made a query based on those tables and i generated a report for that.
What i want now is when ever i select "Product A "i do want to have the report with all requirements related to Product A if and only if those requirements are not related to any other product.
Thank again
Joelle

Then to answer your question,

You're going to need to establish somehow, a list that would have a one to many relationship so as to be able to select the records that you want based on the conditions that you're wanting.

The easiest way to do that is search in the requirments tables that you've meantioned and see if any of those items are required and exclude them from the returned SELECT table results.

this way you can have all the items but only the ones that don't have requirements from the other.

Hope that helps,

If you want more detailed answer, if you include the table and query structure, I would be able to give you a more detailed answer.

Joe P.

[Joelle]

Hi ,
thanks for getting back to me.Here is the structure of my query from which i have generated a report

[CODE=sql]SELECT Requirements.Re quirementId, Components.Comp onentTag, Products.Produc tTag, Releases.Releas eVersion, Statusses.Statu sTag, RequirementCate gories.Requirem entCategorySyno psis, RequirementCate gories.Requirem entCategoryL1, RequirementCate gories.Requirem entCategoryL2, RequirementCate gories.Requirem entCategoryL3, Requirements.Re quirementDescri ption, Objects.Object
FROM (((Releases INNER JOIN ((Products INNER JOIN (((Components INNER JOIN (Requirements INNER JOIN ComponentRequir ements ON Requirements.Re quirementNumber = ComponentRequir ements.Requirem entNumber) ON Components.Comp onentNumber = ComponentRequir ements.Componen tNumber) INNER JOIN Statusses ON Requirements.St atusNumber = Statusses.Statu sNumber) INNER JOIN ProductRequirem ents ON Requirements.Re quirementNumber = ProductRequirem ents.Requiremen tNumber) ON Products.Produc tNumber = ProductRequirem ents.ProductNum ber) INNER JOIN RequirementRele ases ON Requirements.Re quirementNumber = RequirementRele ases.Requiremen tNumber) ON Releases.Releas eNumber = RequirementRele ases.ReleaseNum ber) INNER JOIN RequirementCate gories ON Requirements.Re quirementCatego ryNumber = RequirementCate gories.Requirem entCategoryNumb er) INNER JOIN ObjectRequireme nts ON Requirements.Re quirementNumber = ObjectRequireme nts.Requirement Number) INNER JOIN Objects ON ObjectRequireme nts.ObjectNumbe r = Objects.ObjectN umber
WHERE (((RequirementR eleases.Require mentToBeSupport ed)=True));
[/CODE]

so with my 3 listboxes,named ProductList,Sta tusList and ReleaseList.
for this last 2 listboxes there is no problem because a requirement has only one status or release but the problem is on the product list because one requirement can have 2 products.
As an example Requirement1 is related to Product A and B.
if i select product A i will get Requirement1 and the same goes to Product B .with the codes i am using now(see first post)
which is not what i want. What i want is: to see in my report Requirement1 only if i select Product A and Product B otherwise not!

Thanks for helping!
Regards,
Joelle

[Scott Price]

Just a request, Joelle! When posting code, especially the long, involved queries and code sections that you have here, please use the [CODE] tags provided! They are simple to use: select your code text in the reply window, then click the # icon on the top of the reply window. This makes your code MUCH easier to read and debug.

As an added bonus, you can manually edit the first code tag to look like this: [CODE=vb] for VBA code, and [CODE=sql] for SQL statements.

Thanks!

Scott

[PianoMan64]

Hi ,
thanks for getting back to me.Here is the structure of my query from which i have generated a report

[CODE=sql]SELECT Requirements.Re quirementId, Components.Comp onentTag, Products.Produc tTag, Releases.Releas eVersion, Statusses.Statu sTag, RequirementCate gories.Requirem entCategorySyno psis, RequirementCate gories.Requirem entCategoryL1, RequirementCate gories.Requirem entCategoryL2, RequirementCate gories.Requirem entCategoryL3, Requirements.Re quirementDescri ption, Objects.Object
FROM (((Releases INNER JOIN ((Products INNER JOIN (((Components INNER JOIN (Requirements INNER JOIN ComponentRequir ements ON Requirements.Re quirementNumber = ComponentRequir ements.Requirem entNumber) ON Components.Comp onentNumber = ComponentRequir ements.Componen tNumber) INNER JOIN Statusses ON Requirements.St atusNumber = Statusses.Statu sNumber) INNER JOIN ProductRequirem ents ON Requirements.Re quirementNumber = ProductRequirem ents.Requiremen tNumber) ON Products.Produc tNumber = ProductRequirem ents.ProductNum ber) INNER JOIN RequirementRele ases ON Requirements.Re quirementNumber = RequirementRele ases.Requiremen tNumber) ON Releases.Releas eNumber = RequirementRele ases.ReleaseNum ber) INNER JOIN RequirementCate gories ON Requirements.Re quirementCatego ryNumber = RequirementCate gories.Requirem entCategoryNumb er) INNER JOIN ObjectRequireme nts ON Requirements.Re quirementNumber = ObjectRequireme nts.Requirement Number) INNER JOIN Objects ON ObjectRequireme nts.ObjectNumbe r = Objects.ObjectN umber
WHERE (((RequirementR eleases.Require mentToBeSupport ed)=True));
[/CODE]

so with my 3 listboxes,named ProductList,Sta tusList and ReleaseList.
for this last 2 listboxes there is no problem because a requirement has only one status or release but the problem is on the product list because one requirement can have 2 products.
As an example Requirement1 is related to Product A and B.
if i select product A i will get Requirement1 and the same goes to Product B .with the codes i am using now(see first post)
which is not what i want. What i want is: to see in my report Requirement1 only if i select Product A and Product B otherwise not!

Thanks for helping!
Regards,
Joelle

Joelle, Like I've explained before, you're going to need to set one of the relationship between the tables to be a LEFT JOIN in order words it has to be the primary table that is going to have the main overall structure to how the other tables are related. It sounds like you're going to need to re-structure your tables so that somewhere along the line, that you have some difinitive relationships that aren't so abmiguous.

I'm also assuming that this query runs very slow, if there is a lot of data?

If you can send me data examples so I can re-create the senerio, then that would help in giving you possibly an answer to your question.

Hope that helps,

Joe P.

[Joelle]

Joelle, Like I've explained before, you're going to need to set one of the relationship between the tables to be a LEFT JOIN in order words it has to be the primary table that is going to have the main overall structure to how the other tables are related. It sounds like you're going to need to re-structure your tables so that somewhere along the line, that you have some difinitive relationships that aren't so abmiguous.

I'm also assuming that this query runs very slow, if there is a lot of data?

If you can send me data examples so I can re-create the senerio, then that would help in giving you possibly an answer to your question.

Hope that helps,

Joe P.

Hi Piano,
Sorry for getting back to uyou so late,i was out of town for a while.
Thx again for your response ,i checked my tables and restructured my table.Thx for your help
Joelle