Criteria Question for a query 1

[greekpatriott]

Hello,
Can anyone help me find the correct criteria for a query?

If I have three tables say tableA tableB and tableC and a form FormA.

TableA has X and Y values in a field called [PolicyType]
TableB has A, B, C, D, E, and F values in a field called [InsuredItems] and the corresp[onding X and Y values.
TableC is a combination of TableA and TableB for every contract name [ContractNo] which is formed from FormA.

What Criteria should I choose in a query so that when I choose from a form the Policy Type from TableA (comboBox), say X which has the corresponding A and B values of TableB, it will show only A & B instead of all the A, B, C, D, E, and F values????

Can anyone help out? Cheers

 

[greekpatriott]

This is my SQL. Can anyone correct the where criteria to make it work?? Cheers
SELECT T_PolicyInsuredItems.PolicyInsuredItems, T_PolicyAmounts.PolicySection
FROM T_ListofPolicySections INNER JOIN (T_PolicyInsuredItems INNER JOIN T_PolicyAmounts ON (T_PolicyInsuredItems.PolicyInsuredItems = T_PolicyAmounts.PolicyInsuredItems) AND (T_PolicyInsuredItems.PolicySection = T_PolicyAmounts.PolicySection) AND (T_PolicyInsuredItems.PolicySection = T_PolicyAmounts.PolicySection)) ON (T_ListofPolicySections.PolicySections = T_PolicyInsuredItems.PolicySection) AND (T_ListofPolicySections.PolicySections = T_PolicyInsuredItems.PolicySection) AND (T_ListofPolicySections.PolicySections = T_PolicyInsuredItems.PolicySection) AND (T_ListofPolicySections.PolicySections = T_PolicyAmounts.PolicySection)
WHERE (((T_PolicyAmounts.PolicySection)=[T_PolicyAmounts].[PolicySection]));

 

[GingerR]

This is difficult to follow.
How is your query not working? Maybe if you supply some sample data it will help. Also, the 'bad' result you are getting, and the result you are hoping for.

Thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Lot of redundancy in your SQL code.
For me its the same as:
SELECT I.PolicyInsuredItems, A.PolicySection
FROM T_ListofPolicySections L
INNER JOIN (T_PolicyInsuredItems I
INNER JOIN T_PolicyAmounts A
ON (I.PolicyInsuredItems = A.PolicyInsuredItems)
AND (I.PolicySection = A.PolicySection)
) ON (L.PolicySections = I.PolicySection)

Can you rephrase your issue with the real table and field names ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

ok this is the fake database I hace created can you help me on this?? Cheers
SELECT TableB.InsuredItems, TableB.PolicyType, TableC.PolicyType
FROM TableC INNER JOIN TableB ON TableC.PolicyType = TableB.PolicyType;

 

[lespaul]

again, it would be extremely helpful if you would take the time to do something similar to this:

I have tableB.  it has this structure:

Field1 - PK
Field2 
Field3

There is also another TableA, this table has:

Field1 - PK
FIeld2- FK to tableB
Field3
Field4

The data is like this:

TableB
Field1         Field2             Field3
data           data               data
data           data               data
data           data               data
data           data               data

TableA

Field1         Field2             Field3        Field4
data           data               data          data
data           data               data          data
data           data               data          data
data           data               data          data

I want to have the information presented like this:

TableA.Field1    TableB.FIeld2   TableAFIeld3

where these conditions are met (list conditions here).

So, with the above data, I would expect something like this:

TableA.Field1    TableB.FIeld2   TableA.FIeld3
data                data             data
data                data             data
data                data             data

Leslie

 

[greekpatriott]

TableA:
Field1: PolicyType
X
Y

TableB
Field1: InsuredItems      Field2: PolicyType
A                                     X
B                                     X
C                                     Y
D                                     Y
E                                     Y

TableC
Field1: ContractNo Field2: PolicyType  Field3: InsuredItems
1                                
2                              
3
4
5


Basicly the input of TableC is done through a form.
FormA
textbox: ContractNo
ComboBox1: PolicyType
ComboBox2: InsuredItems

comboBox1 has a query:
that reads from tableA

I need to have a query in comboBox2 when I insert values in FormA say:
textbox: 1
ComboBox1:X (from the drop down menu)
ComboBox2: will show only the values A and B (in the drop down menu)

textbox: 2
comboBox1: Y
ComboBox2: will show the values C, D, E to chooce from.

Cheers

 

[PHV]

strSQL = "SELECT InsuredItems FROM TableB WHERE PolicyType='" & comboBox1 & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

PHV, hi

SELECT TableB.InsuredItems
FROM TableB
WHERE (((TableB.PolicyType)='& insuredItems &'));

Something is not working. Can you correct the code in the wbere .....

Thanks

 

[lespaul]

it's your ' and " that are missing:

SELECT TableB.InsuredItems
FROM TableB
WHERE (((TableB.PolicyType)='"& insuredItems &"'"));

Leslie

 

[greekpatriott]

& PolicyType &" not insuredItems I think but still something is not working with the question marks.

 

[PHV]

In the AfterUpdate event procedure of comboBox1:
strSQL = "SELECT InsuredItems FROM TableB WHERE PolicyType='" & comboBox1 & "'"
comboBox2.RowSource = strSql

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

PHV this is excellent. I dont understand the code but it works. This is excellent work. Cheers. I appreciate it.