UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Claimhistory Qry, Access 2007    
 
   
johan
post Aug 21 2019, 07:15 AM
Post#1



Posts: 1,023
Joined: 24-April 08
From: Riebeek Kasteel, Western Cape, South Africa


I am at lost here. I have a main form [PersonalClaim] and a subform [PPropertyLossDetailsClaim]. I need to query how many claims a client had and the query will based on the [Policy_nr] as one policy can have more then one claim.

PERSONALCLAIM
Claim_nr (ID)
Policy_nr (Number)

PPROPERTYLOSSDETAILSCLAIM
Claim_nr (ID)
Policy_nr (Number)

As all the details, I need for the query, is on the subform [PPropertyLossDetailsClaim], I notice that the Policy_nr control on the subform is empty and do not pull the Policy_nr throw from form PersonalClaim
The controlSource on my subform is
CODE
SELECT PPropertyLossDetailsClaim.Claim_nr, PPropertyLossDetailsClaim.ClientNR, PPropertyLossDetailsClaim.Policy_nr, PPropertyLossDetailsClaim.Section, PPropertyLossDetailsClaim.Item, PPropertyLossDetailsClaim.ODate, PPropertyLossDetailsClaim.OTime, PPropertyLossDetailsClaim.DDate, PPropertyLossDetailsClaim.OAddress, PPropertyLossDetailsClaim.SAPS, PPropertyLossDetailsClaim.SAPSRef, PPropertyLossDetailsClaim.Description, PPropertyLossDetailsClaim.AAlarm, PPropertyLossDetailsClaim.ClaimAmount, PPropertyLossDetailsClaim.Excess, PPropertyLossDetailsClaim.Note FROM PPropertyLossDetailsClaim;

It does not work either when I add
CODE
=[Forms]![PersonalClaim]![Policy_nr]

in the [Policy_nr] control on the subform [PPropertyLossDetailsClaim]

When a button on the form is clicked a message box must appear where I put the [Policy_nr] in. The only information I need for the query is.
Policy_nr
ODate
Description

--------------------
Johan
Winners don't do different things, they do things different.
Go to the top of the page
 
Jeff B.
post Aug 21 2019, 08:04 AM
Post#2


UtterAccess VIP
Posts: 10,276
Joined: 30-April 10
From: Pacific NorthWet


Can we assume your db also has policy numbers associated with (i.e., connected to) clients?

Just off the top of my head, it sounds like you could use GROUP BY [PolicyNumber] to find all claims associated with each given policy...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
projecttoday
post Aug 21 2019, 08:12 AM
Post#3


UtterAccess VIP
Posts: 10,966
Joined: 10-February 04
From: South Charleston, WV


If this is a table:

PPROPERTYLOSSDETAILSCLAIM
Claim_nr (ID)
Policy_nr (Number)

Then Policy_nr will appear in the result of this:

SELECT Claim_nr, Policy_nr FROM PPROPERTYLOSSDETAILSCLAIM

But you don't need to repeat the policy number in the claims details if it's in the claims table. And you don't even need the policy number if the policies table has an ID (autonumber) field. You can use that instead.

--------------------
Robert Crouser
Go to the top of the page
 
orange999
post Aug 21 2019, 08:23 AM
Post#4



Posts: 1,947
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


What links your form and subform? (linking fields)

Typically you would have a query with a Count and Group By
eg:(from my database)
CODE
SELECT ClaimInfo.PolicyNO, Count(ClaimInfo.ClaimRef) AS CountOfClaimRef
FROM ClaimInfo
GROUP BY ClaimInfo.PolicyNO;


with a result such as:
CODE
PolicyNO    CountOfClaimRef
12    4
14    1
16    1
17    1
34    2
39    1


Perhaps your structure is not matching your requirement/rules???
This post has been edited by orange999: Aug 21 2019, 08:24 AM

--------------------
Good luck with your project!
Go to the top of the page
 
johan
post Aug 23 2019, 01:55 AM
Post#5



Posts: 1,023
Joined: 24-April 08
From: Riebeek Kasteel, Western Cape, South Africa


Claim_nr is the linking fields
The [Policy_nr] does not show in subform [PPropertyLossDetailsClaim] all though it has a field [Policy_nr], it does not pull throw from the form [PersonalClaim]
Attached File(s)
Attached File  Screenshot.pdf ( 133K )Number of downloads: 5
Attached File  QryClaimHistory.pdf ( 108.12K )Number of downloads: 4
 

--------------------
Johan
Winners don't do different things, they do things different.
Go to the top of the page
 
johan
post Aug 30 2019, 08:12 AM
Post#6



Posts: 1,023
Joined: 24-April 08
From: Riebeek Kasteel, Western Cape, South Africa


I know it is possible to have an unbound field in a query, but I am struggling with it. In the screenshot attach the unbound field on my form is Item but no data is displayed. The code in Text65 (label Item) on the form is
CODE
=Trim(DLookUp("[Item]","PPropertyLossDetailsClaim","[Claim_Nr] =" & [Forms]![PersonalClaim]![Claim_Nr]) & "" & DLookUp("[VYear]","tblVehicleClaimDetails","[Claim_Nr] =" & [Forms]![PersonalClaim]![Claim_Nr]) & " " & DLookUp("[VMake]","tblVehicleClaimDetails","[Claim_Nr] =" & [Forms]![PersonalClaim]![Claim_Nr]) & " " & DLookUp("[VRegNr]","tblVehicleClaimDetails","[Claim_Nr] =" & [Forms]![PersonalClaim]![Claim_Nr]) & "" & DLookUp("[VH]","tblWindscreenClaimDetails","[Claim_Nr] =" & [Forms]![PersonalClaim]![Claim_Nr]) & " " & DLookUp("[VHReg]","tblWindscreenClaimDetails","[Claim_Nr] =" & [Forms]![PersonalClaim]![Claim_Nr]))

Attached File(s)
Attached File  ScreenShot.pdf ( 134.78K )Number of downloads: 4
 

--------------------
Johan
Winners don't do different things, they do things different.
Go to the top of the page
 
projecttoday
post Aug 30 2019, 10:07 AM
Post#7


UtterAccess VIP
Posts: 10,966
Joined: 10-February 04
From: South Charleston, WV


i see. The thing is, you're calling these fields ID and we're used to seeing a separate autonumber field as ID. So, with that in mind, the advice you've been given may be off a little.

An autonumber ID is a convenient and reliable way to identify records. I highly recommend that you add an autonumber ID to each of your tables and do your linking on those.

--------------------
Robert Crouser
Go to the top of the page
 
johan
post Sep 4 2019, 08:47 AM
Post#8



Posts: 1,023
Joined: 24-April 08
From: Riebeek Kasteel, Western Cape, South Africa


Thanks Projecttoday will try, just one question is it possible to query an unbound field, say [Text345] on a form which have code in it as the data is not physical type in or do you query [Forms]![MainFormName]![Unboundfield]
This post has been edited by johan: Sep 4 2019, 08:51 AM

--------------------
Johan
Winners don't do different things, they do things different.
Go to the top of the page
 
projecttoday
post Sep 4 2019, 10:38 AM
Post#9


UtterAccess VIP
Posts: 10,966
Joined: 10-February 04
From: South Charleston, WV


Yes, I think you've got it. If you want the value in a control on a form (generally when we say query we mean tables) you can reference it with either Me or as you suggest according to the format (it's at The Access Web).

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 03:59 AM