Problem with Query

[LSGKelly]

Hi Byte community! I'm having a problem with a query and I need your expert advice.

Okay, I have two queries; the first one has this type of information in columns:

CombinedPlan1
CombinedPlan2
CombinedPlan3

The results of each of these columns need to be looked up in another query called "Rates". I need to return 4 (Single rates, Family, etc.) columns of information based on CombinedPlan 1-3. This is driving me crazy. How can I pull this information from the query?

Thanks, let me know if you need more information. I hope it's not too confusing.

Kelly

[NeoPa]

I'm afraid you will need to explain this better Kelly. I barely have a clue what you're talking about :(

[LSGKelly]

Okay. It is kind of hard to explain, but I will try to do better. :)

I have two queries. The first query has columns of information like this:

Comb1a       Comb2a      Comb3a     
CA UHC       MI UHC      Aetna 42
0Aetna 41    BCBSMI      BCBSWI

The other query looks like this:

Carrier   Single   Double   Single wChild     
CA UHC     $300     $400       $350
Aetna41    $350     $600       $250
MI UHC     $500     $800       $650
BCBSMI     $250     $900       $500

What I need is for the query to pull the rates for Single, Double and Single w/Child into the query that has the comb1a, comb2a, comb3a fields.

Would a Dlookup work? I can't pull based on carrier, because each combination column is different. Maybe I need to set it up differently?

I hope that clears it up a bit for you.

Kelly

[NeoPa]

Am I right in thinking that you want an output something like? :

Comb1a   Sngl1 Dbl1 SwC1  Comb2a  Sngl2 Dbl2 SwC2  Comb3a    ...
CA UHC    $300 $400 $350  MI UHC   $500 $800 $650  Aetna 42  ...
Aetna 41  $350 $600 $250  BCBSMI   $250 $900 $500  BCBSWI    ...

What are the names of the two queries?

What is the SQL for the first query?

[LSGKelly]

Am I right in thinking that you want an output something like? :

Comb1a   Sngl1 Dbl1 SwC1  Comb2a  Sngl2 Dbl2 SwC2  Comb3a    ...
CA UHC    $300 $400 $350  MI UHC   $500 $800 $650  Aetna 42  ...
Aetna 41  $350 $600 $250  BCBSMI   $250 $900 $500  BCBSWI    ...

What are the names of the two queries?

What is the SQL for the first query?

That is EXACTLY what I am looking for.

The rate query is called: "qryRates" and the combination query is called qryComb1C1.

[LSGKelly]

That is EXACTLY what I am looking for.

The rate query is called: "qryRates" and the combination query is called qryComb1C1.

Here is the sql...sorry I forgot to send it the first time:

SELECT qGI.[BRET Number],
       qPI.[Plan #],
       qPI.[comparable plan 1a] &
       [adp tier compare 1a] &
       [rating area 1a] AS CarrierADP1a,
       qPI.[comparable plan 1b] &
       [adp tier compare 1b] &
       [rating area 1b] AS CarrierADP1b,
       qPI.[comparable plan 1c] &
       [adp tier compare 1c] &
       [rating area 1c] AS CarrierADP1c,
       qPI.[comparable plan 1d] &
       [adp tier compare 1d] &
       [rating area 1d] AS CarrierADP1d,
       qPI.[comparable plan 1a] &
       [bret tier] &
       [rating area 1a] AS CarrierB1A,
       qPI.[comparable plan 1b] &
       [bret tier] &
       [rating area 1b] AS CarrierB1b,
       qPI.[comparable plan 1c] &
       [bret tier] &
       [rating area 1c] AS CarrierB1c,
       qPI.[comparable plan 1d] &
       [bret tier] &
       [rating area 1d] AS CarrierB1d,
       qPI.[comparable plan 1e] &
       [bret tier] &
       [rating area 1e] AS CarrierB1e,
       qPI.[comparable plan 1a] &
       [Final Tier Carrier 1] &
       [rating area 1a] AS CarrierF1A,
       qPI.[comparable plan 1b] &
       [Final Tier Carrier 1] &
       [rating area 1b] AS CarrierF1B,
       qPI.[comparable plan 1c] &
       [Final Tier Carrier 1] &
       [rating area 1c] AS CarrierF1C,
       qPI.[comparable plan 1d] &
       [Final Tier Carrier 1] &
       [rating area 1d] AS CarrierF1D,
       qPI.[comparable plan 1e] &
       [Final Tier Carrier 1] &
       [rating area 1e] AS CarrierF1e

FROM (qryGrpInfo AS qGI INNER JOIN qryPlanInfo AS qPI
  ON  qryGrpInfo.[BRET Number]=qryPlanInfo.[BRET Number]) INNER JOIN
      tblLSGInputs AS tLI
  ON  qryGrpInfo.[BRET Number]=tblLSGInputs.[Bret Number]

[NeoPa]

I've added the (mandatory) [ CODE ] tags, and tidied it up a little so it's understandable, but I will still need a while to digest.

It refers to various items without references (Where does [Comparable Plan 1a] come from for instance), so it's still a little unclear.

Do I understand from this that there are actually more than the three columns you mentioned (5 in fact)?

[LSGKelly]

I've added the (mandatory) [ CODE ] tags, and tidied it up a little so it's understandable, but I will still need a while to digest.

It refers to various items without references (Where does [Comparable Plan 1a] come from for instance), so it's still a little unclear.

Do I understand from this that there are actually more than the three columns you mentioned (5 in fact)?

There are three queries that it's pulling information from. qryGrpInfo is the first query, "qryplaninf o" is the second and qryRates is the third. The "comparable " plans are coming from the qryplaninfo query. This is where they decide which plans they are going to compare. The qrygrpinfo is the first query. They put in all the relevant information in the first form based on this query.

Yes, there were more than the three columns. There are actually going to be three separate queries based on the combined information. There are three carriers for each with five comparisons per carrier (craziness!). I'm planning on having a comparison query with rates for each of the carriers.

I'm really not new to Access, but this database is going to make me bald! lol I certainly appreciate anything you can do to help!

Kelly

[NeoPa]

...
I'm really not new to Access, but this database is going to make me bald! lol I certainly appreciate anything you can do to help!

Kelly

Kelly, unless this is simply your surname, that's not a fate I would wish upon any girl ;)

I have updated the SQL post to better reflect your recent information, but is still missing the source indicator for the groups of fields :

[adp tier compare ??]
[rating area ??]
[bret tier]

With this information I could tidy up the SQL, but nevertheless doing what you require will prove quite complex.

The only thought that I had that might help is to get the input query (might be qryGrpInfo but it's hard to follow so it may be one of the other ones) that produces the [Comp1a]; etc data you want expanded and expand it BEFORE [qryComb1C1] gets it.

That would mean (assuming the query we are needing to work on is [qryGrpInfo]) creating a new query [qryGrpInfoEx] based on linking [qryGrpInfo] to the [qryRates] query which would return the extra rate information as well as the original [qryGrpInfo] info.

Next you redo your [qryComb1C1] query to use [qryGrpInfoEx] instead of [qryGrpInfo]. Clearly if the query is [qryPlanInfo] then just do the same but with that one as the basis.

Does that help at all?

[LSGKelly]

The only thought that I had that might help is to get the input query (might be qryGrpInfo but it's hard to follow so it may be one of the other ones) that produces the [Comp1a]; etc data you want expanded and expand it BEFORE [qryComb1C1] gets it.

That would mean (assuming the query we are needing to work on is [qryGrpInfo]) creating a new query [qryGrpInfoEx] based on linking [qryGrpInfo] to the [qryRates] query which would return the extra rate information as well as the original [qryGrpInfo] info.

Next you redo your [qryComb1C1] query to use [qryGrpInfoEx] instead of [qryGrpInfo]. Clearly if the query is [qryPlanInfo] then just do the same but with that one as the basis.

Does that help at all?

It really did turn into a complex mess. The problem I'm having is that I have separate fields for each of the comparisons. The query above concatenates three fields together for a lookup in the rate table. There is no good way to pull the rates because what I'm looking up is in one column in the rate table and I can't compare many fields to one column (if that makes sense at all). It would be really convenient if there was a way that I could do a lookup in the query and pull those numbers that way.

It's so frustrating!

And at this point, going bald would be the least of my worries! :)

Kelly

[NeoPa]

I thought I'd replied to this yesterday. I'm sure I prepared a response so I must have restarted my PC without submitting it Grrr!

As I said before, this whole situation is getting a little complex to deal with as is (remotely). What I can do though, is suggest a way of dealing with the particular issue you've just raised. This is about linking data between two record sources where the result of combining multiple fields is actually what you need to join the two record sources together.

See if the following sheds any light, and can be used in your situation to get over this specific hurdle.

Assume two tables which should be joined together but are of the following formats (CPK indicates Composite Primary Key; FK indicates Foreign Key) :

Table Name=[tblOrdHdr]

[I]Field   Type       IndexInfo[/I]
Branch  String(2)  CPK
OrdNo   String(5)  CPK
CustNo  String(5)
Source  Numeric

Table Name=[tblOrdDtl]

[I]
Field      Type        IndexInfo[/I]
FullOrdNo  String(10)  CPK & FK
LineNo     Numeric     CPK
Qty        Numeric
Value      Numeric

The format of the [FullOrdNo] is [Branch] & " / " & [OrdNo]. IE. If [Branch]="LN" and OrdNo="W2245" then [FullOrdNo]="LN / W2245". To get these tables to link together to show all details of all order lines you would use a subquery (Check out Subqueries in SQL for more on subqueries) in the following way :

SELECT tblOrdDtl.FullOrdNo
       tblOrdDtl.LineNo
       subHdr.CustNo
       subHdr.Source
       tblOrdDtl.Qty
       tblOrdDtl.Value

FROM (SELECT [Branch] & ' / ' [OrdNo] AS FullOrdNo,
             CustNo,
             Source

      FROM tblOrdHdr

      ORDER BY [Branch],
               [OrdNo]) AS subHdr INNER JOIN tblOrdDtl
  ON subHdr.FullOrdNo=tblOrdDtl.FullOrdNo

ORDER BY tblOrdDtl.FullOrdNo
         tblOrdDtl.LineNo

PS. This is not normally how you would design tables to handle orders of course. This is simply used for illustrative purposes.