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 :" RequirementQuery_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 + "RequirementQuery.ProductTag = '" + (Me.ProductList.Column(1, var)) + "' "
 
                If iCounter < Me.ProductList.ItemsSelected.Count 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.ItemsSelected
            strf = strf + "RequirementQuery.StatusTag= '" + (Me.StatusList.Column(1, status)) + "'"
 
                If iCounter < Me.StatusList.ItemsSelected.Count 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 + "RequirementQuery.ReleaseVersion= '" + (Me.ReleaseList.Column(1, Release)) + " ' "
 
                   If iCounter < Me.ReleaseList.ItemsSelected.Count Then
                         strf = strf + " OR "
                   End If
                    iCounter = iCounter + 1
           Next Release
 
           strf = strf + " )"
 
        DoCmd.OpenReport "RequirementQuery_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

[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 :" RequirementQuery_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 + "RequirementQuery.ProductTag = '" + (Me.ProductList.Column(1, var)) + "' "

If iCounter < Me.ProductList.ItemsSelected.Count 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.ItemsSelected
strf = strf + "RequirementQuery.StatusTag= '" + (Me.StatusList.Column(1, status)) + "'"

If iCounter < Me.StatusList.ItemsSelected.Count 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 + "RequirementQuery.ReleaseVersion= '" + (Me.ReleaseList.Column(1, Release)) + " ' "

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

strf = strf + " )"

DoCmd.OpenReport "RequirementQuery_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 ProductRequirements 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 ObectRequirements. 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 ProductRequirements 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 ObectRequirements. 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

SELECT Requirements.RequirementId, Components.ComponentTag, Products.ProductTag, Releases.ReleaseVersion, Statusses.StatusTag, RequirementCategories.RequirementCategorySynopsis, RequirementCategories.RequirementCategoryL1, RequirementCategories.RequirementCategoryL2, RequirementCategories.RequirementCategoryL3, Requirements.RequirementDescription, Objects.Object
FROM (((Releases INNER JOIN ((Products INNER JOIN (((Components INNER JOIN (Requirements INNER JOIN ComponentRequirements ON Requirements.RequirementNumber = ComponentRequirements.RequirementNumber) ON Components.ComponentNumber = ComponentRequirements.ComponentNumber) INNER JOIN Statusses ON Requirements.StatusNumber = Statusses.StatusNumber) INNER JOIN ProductRequirements ON Requirements.RequirementNumber = ProductRequirements.RequirementNumber) ON Products.ProductNumber = ProductRequirements.ProductNumber) INNER JOIN RequirementReleases ON Requirements.RequirementNumber = RequirementReleases.RequirementNumber) ON Releases.ReleaseNumber = RequirementReleases.ReleaseNumber) INNER JOIN RequirementCategories ON Requirements.RequirementCategoryNumber = RequirementCategories.RequirementCategoryNumber) INNER JOIN ObjectRequirements ON Requirements.RequirementNumber = ObjectRequirements.RequirementNumber) INNER JOIN Objects ON ObjectRequirements.ObjectNumber = Objects.ObjectNumber
WHERE (((RequirementReleases.RequirementToBeSupported)=True));
 

so with my 3 listboxes,named ProductList,StatusList 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

SELECT Requirements.RequirementId, Components.ComponentTag, Products.ProductTag, Releases.ReleaseVersion, Statusses.StatusTag, RequirementCategories.RequirementCategorySynopsis, RequirementCategories.RequirementCategoryL1, RequirementCategories.RequirementCategoryL2, RequirementCategories.RequirementCategoryL3, Requirements.RequirementDescription, Objects.Object
FROM (((Releases INNER JOIN ((Products INNER JOIN (((Components INNER JOIN (Requirements INNER JOIN ComponentRequirements ON Requirements.RequirementNumber = ComponentRequirements.RequirementNumber) ON Components.ComponentNumber = ComponentRequirements.ComponentNumber) INNER JOIN Statusses ON Requirements.StatusNumber = Statusses.StatusNumber) INNER JOIN ProductRequirements ON Requirements.RequirementNumber = ProductRequirements.RequirementNumber) ON Products.ProductNumber = ProductRequirements.ProductNumber) INNER JOIN RequirementReleases ON Requirements.RequirementNumber = RequirementReleases.RequirementNumber) ON Releases.ReleaseNumber = RequirementReleases.ReleaseNumber) INNER JOIN RequirementCategories ON Requirements.RequirementCategoryNumber = RequirementCategories.RequirementCategoryNumber) INNER JOIN ObjectRequirements ON Requirements.RequirementNumber = ObjectRequirements.RequirementNumber) INNER JOIN Objects ON ObjectRequirements.ObjectNumber = Objects.ObjectNumber
WHERE (((RequirementReleases.RequirementToBeSupported)=True));
 

so with my 3 listboxes,named ProductList,StatusList 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