By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Problem with Query

P: 38
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
Aug 20 '08 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,660
I'm afraid you will need to explain this better Kelly. I barely have a clue what you're talking about :(
Aug 20 '08 #2

P: 38
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:
Expand|Select|Wrap|Line Numbers
  1. Comb1a       Comb2a      Comb3a     
  2. CA UHC       MI UHC      Aetna 42
  3. 0Aetna 41    BCBSMI      BCBSWI
The other query looks like this:
Expand|Select|Wrap|Line Numbers
  1. Carrier   Single   Double   Single wChild     
  2. CA UHC     $300     $400       $350
  3. Aetna41    $350     $600       $250
  4. MI UHC     $500     $800       $650
  5. 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
Aug 20 '08 #3

NeoPa
Expert Mod 15k+
P: 31,660
Am I right in thinking that you want an output something like? :
Expand|Select|Wrap|Line Numbers
  1. Comb1a   Sngl1 Dbl1 SwC1  Comb2a  Sngl2 Dbl2 SwC2  Comb3a    ...
  2. CA UHC    $300 $400 $350  MI UHC   $500 $800 $650  Aetna 42  ...
  3. 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?
Aug 20 '08 #4

P: 38
Am I right in thinking that you want an output something like? :
Expand|Select|Wrap|Line Numbers
  1. Comb1a   Sngl1 Dbl1 SwC1  Comb2a  Sngl2 Dbl2 SwC2  Comb3a    ...
  2. CA UHC    $300 $400 $350  MI UHC   $500 $800 $650  Aetna 42  ...
  3. 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.
Aug 21 '08 #5

P: 38
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT qGI.[BRET Number],
  2.        qPI.[Plan #],
  3.        qPI.[comparable plan 1a] &
  4.        [adp tier compare 1a] &
  5.        [rating area 1a] AS CarrierADP1a,
  6.        qPI.[comparable plan 1b] &
  7.        [adp tier compare 1b] &
  8.        [rating area 1b] AS CarrierADP1b,
  9.        qPI.[comparable plan 1c] &
  10.        [adp tier compare 1c] &
  11.        [rating area 1c] AS CarrierADP1c,
  12.        qPI.[comparable plan 1d] &
  13.        [adp tier compare 1d] &
  14.        [rating area 1d] AS CarrierADP1d,
  15.        qPI.[comparable plan 1a] &
  16.        [bret tier] &
  17.        [rating area 1a] AS CarrierB1A,
  18.        qPI.[comparable plan 1b] &
  19.        [bret tier] &
  20.        [rating area 1b] AS CarrierB1b,
  21.        qPI.[comparable plan 1c] &
  22.        [bret tier] &
  23.        [rating area 1c] AS CarrierB1c,
  24.        qPI.[comparable plan 1d] &
  25.        [bret tier] &
  26.        [rating area 1d] AS CarrierB1d,
  27.        qPI.[comparable plan 1e] &
  28.        [bret tier] &
  29.        [rating area 1e] AS CarrierB1e,
  30.        qPI.[comparable plan 1a] &
  31.        [Final Tier Carrier 1] &
  32.        [rating area 1a] AS CarrierF1A,
  33.        qPI.[comparable plan 1b] &
  34.        [Final Tier Carrier 1] &
  35.        [rating area 1b] AS CarrierF1B,
  36.        qPI.[comparable plan 1c] &
  37.        [Final Tier Carrier 1] &
  38.        [rating area 1c] AS CarrierF1C,
  39.        qPI.[comparable plan 1d] &
  40.        [Final Tier Carrier 1] &
  41.        [rating area 1d] AS CarrierF1D,
  42.        qPI.[comparable plan 1e] &
  43.        [Final Tier Carrier 1] &
  44.        [rating area 1e] AS CarrierF1e
  45.  
  46. FROM (qryGrpInfo AS qGI INNER JOIN qryPlanInfo AS qPI
  47.   ON  qryGrpInfo.[BRET Number]=qryPlanInfo.[BRET Number]) INNER JOIN
  48.       tblLSGInputs AS tLI
  49.   ON  qryGrpInfo.[BRET Number]=tblLSGInputs.[Bret Number]
Aug 21 '08 #6

NeoPa
Expert Mod 15k+
P: 31,660
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)?
Aug 21 '08 #7

P: 38
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, "qryplaninfo" 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
Aug 21 '08 #8

NeoPa
Expert Mod 15k+
P: 31,660
...
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 :
Expand|Select|Wrap|Line Numbers
  1. [adp tier compare ??]
  2. [rating area ??]
  3. [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?
Aug 21 '08 #9

P: 38
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
Aug 22 '08 #10

NeoPa
Expert Mod 15k+
P: 31,660
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]
Expand|Select|Wrap|Line Numbers
  1. Field   Type       IndexInfo
  2. Branch  String(2)  CPK
  3. OrdNo   String(5)  CPK
  4. CustNo  String(5)
  5. Source  Numeric
Table Name=[tblOrdDtl]
Expand|Select|Wrap|Line Numbers
  1. Field      Type        IndexInfo
  2. FullOrdNo  String(10)  CPK & FK
  3. LineNo     Numeric     CPK
  4. Qty        Numeric
  5. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrdDtl.FullOrdNo
  2.        tblOrdDtl.LineNo
  3.        subHdr.CustNo
  4.        subHdr.Source
  5.        tblOrdDtl.Qty
  6.        tblOrdDtl.Value
  7.  
  8. FROM (SELECT [Branch] & ' / ' [OrdNo] AS FullOrdNo,
  9.              CustNo,
  10.              Source
  11.  
  12.       FROM tblOrdHdr
  13.  
  14.       ORDER BY [Branch],
  15.                [OrdNo]) AS subHdr INNER JOIN tblOrdDtl
  16.   ON subHdr.FullOrdNo=tblOrdDtl.FullOrdNo
  17.  
  18. ORDER BY tblOrdDtl.FullOrdNo
  19.          tblOrdDtl.LineNo
PS. This is not normally how you would design tables to handle orders of course. This is simply used for illustrative purposes.
Aug 23 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.