Query only works if a joint flag is set

[Gasman]

Hi all,

I have a query which is meant to produce data for letter mailmerge. the code is below.The query appends the record to a table to be used by Albert Kallal's word merge code.
It works as long as I have a joint flag set. the joint flag allows me to fill in extra fields in the client table, but the client might well be single or just want the cover for themself.

How can I get it to work in both scenarios please.?

TIA

PARAMETERS LetterID Long;
INSERT INTO tblLetters ( ID, ForeName, MiddleNames, Surname, Address1, Address2, Address3, Address4, Address5, PostCode, DearNames, CSumAssured, CoverTerm, BenefitAge, CPremium, tAddFeatures, CDisposable, SupportPeriod, IllnessPeriod, cLoanAmount, LoanTerm, cLumpSum, LumpSumPeriod, OldCover, OldProvider, Event, SurvivePeriod, pRPIIncrease, pRPIMax, CoverNames, tJointSurvive, ProviderName, CoverType )
SELECT tblClientProduct.ID, tblClient.ForeName, tblClient.MiddleNames, tblClient.Surname, tblClient.Address1, tblClient.Address2, tblClient.Address3, tblClient.Address4, tblClient.Address5, tblClient.PostCode, IIf([JointCover],Trim([tblLookup].[LookupValue]) & " " & Trim([Surname]) & " & " & Trim([tblLookup_1].[LookupValue]) & " " & Trim([JointSurname]),Trim([tblLookup].[LookupValue]) & " " & Trim([Surname])) AS DearNames, Format([SumAssured],"£#,###") AS CSumAssured, tblClientProduct.CoverTerm, tblClientProduct.BenefitAge, Format([Premium],"Currency") AS CPremium, IIf(Len([AddFeatures])>0,[AddFeatures],"N/A") AS tAddFeatures, Format([Disposable],"Currency") AS CDisposable, tblClientProduct.SupportPeriod, tblClientProduct.IllnessPeriod, Format([LoanAmount],"£#,###") AS cLoanAmount, tblClientProduct.LoanTerm, Format([LumpSum],"£#,###") AS cLumpSum, tblClientProduct.LumpSumPeriod, tblClientProduct.OldCover, tblClientProduct.OldProvider, tblClientProduct.Event, tblClientProduct.SurvivePeriod, Format([RPIIncrease],"Percent") AS pRPIIncrease, Format([RPIMax],"Percent") AS pRPIMax, IIf([JointCover],Trim([tblLookup].[LookupValue]) & " " & Trim([KnownAs]) & " " & Trim([Surname]) & " & " & Trim([tblLookup_1].[LookupValue]) & " " & Trim([JointKnownAs]) & " " & Trim([JointSurname]),Trim([KnownAS]) & " " & Trim([Surname])) AS CoverNames, IIf([JointCover],"you both","you") AS tJointSurvive, tblProvider.ProviderName, tblProduct.ProductName AS CoverType
FROM tblProvider INNER JOIN (tblProduct INNER JOIN (((tblClient INNER JOIN tblLookup ON tblClient.Title = tblLookup.ID) INNER JOIN tblLookup AS tblLookup_1 ON tblClient.JointTitle = tblLookup_1.ID) INNER JOIN (tblClientProduct INNER JOIN tblProductProvider ON tblClientProduct.ProductProviderID = tblProductProvider.ID) ON tblClient.ClientID = tblClientProduct.ClientID) ON tblProduct.ID = tblProductProvider.ProductID) ON tblProvider.ID = tblProductProvider.ProviderID
WHERE (((tblClientProduct.ID)=[LetterID]));

 

[sneuberg]

I don't see the joint flag in this and don't understand what you mean by

the client might well be single or just want the cover for themself.

Please give us more details about what you want to do.

 

[Gasman]

I don't see the joint flag in this and don't understand what you mean by



Please give us more details about what you want to do.

Steve,

The flag was just a generic term. the field is actually called JointCover

IIf([JointCover]

If that is set then I need to append data from the joint fields to the first client name so I will get

Mr Fred Bloggs if sole cover and Mr Fred Bloggs & Ms Julie Smith for example if joint cover is selected. All those fields are on the client record at present, but I lookup the Title(s) in a table called tblLookup.

For some reason if that JointCover flag is not set, then the query does not produce any data, and I cannot figure out why, so hence my asking for help?

TIA

 

[sneuberg]

That's strange.

IIf([Joint Cover],"you both","you")

will produce an output even if [JointCover] is null is which case the output would be "you". I don't see how this would cause the query not to have an output.

Could you upload your database?

 

[Gasman]

I test for joint cover several times, but as far as I can see, output should be produced each time?


Of course,

DB zipped and attached. You do not have the letters though, but if the query gets to create the data, the rest will work.

Thank you for looking at it.

 

[sneuberg]

Some of these don't show up because they don't have an entry for the ProductProviderID. Could the [JointCover] field just be a red herring? I suggest filling these fields in and seeing if your problem changes.

 

[Gasman]

Thank you Steve,
I'll try that tomorrow. My system only ever prints out one letter at a time depending on the record selected in ClientProduct, and the ones I have tried all have values in that table, but will complete the table just in case.

I realised that I would have to create a junction table and amend my form accordingly.

Thanks again for your time with this.

 

[Gasman]

Steve,

I added relevant values into tblClientProduct, but it still does not produce data if that jointCover flag is not set? If set, the query works on records 1 and 2.

The query is qryALetters and the [LetterID] parameter is the ID of the tblClientProduct record.
The form to use to set the Joint Cover flag is frmClient and then use the Products tab to select record 1 or 2

TIA

 

[sneuberg]

This is going to take some time to figure out. I don't know if you noticed but if you uncheck Joint Clients on the ClientID 1, Edwin Paul, the record the qryALetters displayed disappears FOR GOOD. Rechecking it and even restarting the application doesn't bring it back.

 

[sneuberg]

Ok I see that you need to add something in the Joint Details fields to get it back.

 

[sneuberg]

It seem to have something to do the the Joint Details fields being null. If you comment out the part the of code that runs when you click the check box, i.e,

' Also clear fields as well
'If Not Me.JointCover Then
'    Me.JointTitle = Null
'    Me.JointForename = Null
'    Me.JointMiddleNames = Null
'    Me.JointSurname = Null
'    Me.JointKnownAs = Null
'End If

You can uncheck the Joint Clients and still see the record, however, what I said in post 6 still applies.

I suspect that one of the expressions that that uses this flag is cause an error. Access won't output a record with an expression that's shown an error. I'm going to convert the query to a select query and see what that tells us.

 

[Gasman]

Steve,

Please stop what you are doing for now.

I have realised that the joint flag will need to be on the ClientProduct record and have now placed it there with an ID for the Joint client.

Whilst I will still have to worked out how to get the joint details, I will need to recreate the query from scratch.

I believe I only need to get the joint details when the jointcover flag is set, so there should always be a value for that. This time it will just be the ID of the other client, not the actual fields.
Still need to get that data though, so will have to think on how to do that.

It seem to have something to do the the Joint Details fields being null. If you comment out the part the of code that runs when you click the check box, i.e,

' Also clear fields as well
'If Not Me.JointCover Then
'    Me.JointTitle = Null
'    Me.JointForename = Null
'    Me.JointMiddleNames = Null
'    Me.JointSurname = Null
'    Me.JointKnownAs = Null
'End If

You can uncheck the Joint Clients and still see the record, however, what I said in post 6 still applies.

I suspect that one of the expressions that that uses this flag is cause an error. Access won't output a record with an expression that's shown an error. I'm going to convert the query to a select query and see what that tells us.

 

[sneuberg]

Ok holding off.

If you haven't already considered it I suggest considering whether it's a good idea to wipe out this data when you uncheck the box. I'd suggest just leaving it there in case someone changes their minds. I'd just use the flag to make it operate like the data isn't there.

Also I notice that your relationships were different from the joins you had in the qryALetter. I suggest bringing them in sync and also considering whether tblProductProvider should be in the design. The ProductID and ProviderID are in this table and in the tblClientProduct.

 

[Gasman]

This is going to take some time to figure out. I don't know if you noticed but if you uncheck Joint Clients on the ClientID 1, Edwin Paul, the record the qryALetters displayed disappears FOR GOOD. Rechecking it and even restarting the application doesn't bring it back.

No, I had not seen that behaviour, as I was checking unchecking that record in my attempts to test what went wrong. Each time I checked the flag I could produce the record. However all the fields have data in that record as I used that for the test mailmerge.

I have since completed fields for record ID 2 with no change, until I checked Joint again.

However as I mentioned in another reply, the data will now have to come from another table, so I will recreate the query on Monday and see if I still cannot get it to work.

I am going to have to give some thought on how to retrieve the data if the flag is set as that comes from the client table again.

I use the Lookup table to get the ID for Mr, Mrs etc. Would you advise hard coding the values in the client table instead?

Sorry to have wasted some of your time.:banghead:

 

[Gasman]

Ok holding off.

If you haven't already considered it I suggest considering whether it's a good idea to wipe out this data when you uncheck the box. I'd suggest just leaving it there in case someone changes their minds. I'd just use the flag to make it operate like the data isn't there.

I can certainly do that. It was what I started with, then thought it better to clear the fields in case I got caught out later down the line?

Also I notice that your relationships were different from the joins you had in the qryALetter. I suggest bringing them in sync and also considering whether tblProductProvider should be in the design. The ProductID and ProviderID are in this table and in the tblClientProduct.

Initially I had just the ProductID and ProviderID in the tblClientProduct but realised some Providers might not provide every Product and so created the ProductProvider junction table, so when you selected a product only the providers that supplied that product could be displayed.

Due to my inexperience I could not see how to show the categories of each product and provider when already selected using the ProductProviderID. Selecting them and obtaining the correct ProductProviderID I can do, it is the reverse part I am unsure of and so elected to store as well just for ease of use.

 

[sneuberg]

Initially I had just the ProductID and ProviderID in the tblClientProduct but realised some Providers might not provide every Product and so created the ProductProvider junction table, so when you selected a product only the providers that supplied that product could be displayed.

In that case the ProductProvider junction table makes sense.

Due to my inexperience I could not see how to show the categories of each product and provider when already selected using the ProductProviderID. Selecting them and obtaining the correct ProductProviderID I can do, it is the reverse part I am unsure of and so elected to store as well just for ease of use.

To be in normal form you would get rid of the ProductID and ProviderID in the tblClientProduct. When you need them to appear along with the tlbClientProduct data just join to the ProductProvider junction table as you did in the qryALetter query.

 

[Gasman]

Steve,
The productID and providerID are still in the form (and the table) merely due to the ease of setting/selecting these items.

If I was to make them unbound, how would I retrieve the values when I move records in the tblClientProduct table?

I understand that the code would be in the form's current event, but a simple dlookup is not going to work. Would I have to use two dlookups for each item for each record?

One for the ProductID, using the ProductproviderID, then one for the Product Name using the ProductID and the same logic for the ProviderID, or is there a better way?

TIA