Report Parameters Being Requested Multiple Times (1 Viewer)

mellis · Nov 17, 2014

Hi all,

I set up what I thought was a simple query in my DB using it to create a report containing a subreport.

I kind of works, however Access is asking me for the parameter value [Extract Date] multiple times, when in theory it should only ask once!

In addition (and likely related) the Access is then repeating the output multiple times.

Any help would be appreciated, thanks!

jdraw · Nov 18, 2014

I've looked at your database. Some comments to consider:

-Don't use table or field names with embedded spaces or special(non alphanumeric) characters. They will cause syntax errors over time.
-Why have multiple IFACTOR DATA tables? Why not 1 table with a type or Location field with AU or NZ as appropriate?
-Your relationships are all 1 to 1???

Here is a revised working version of your qryTrail SQL

Code:

PARAMETERS EnterExtractDate date;
SELECT [I FACTOR DATA - AU].[Extract Date], [CRM DATA - CLIENT]![Company Name]+" (referred by "+[CRM DATA - CLIENT]![Intermediary Referrer]+")" AS [Client Name & Referrer], ([I FACTOR DATA - AU]![Turnover]*[INTERMEDIARY AGREEMENTS]![Commission on Turnover])+(([I FACTOR DATA - AU]![Service Fee Charged]+[I FACTOR DATA - AU]![Minimum Fee Charged])*[INTERMEDIARY AGREEMENTS]![Commission On Service Fee])+(([I FACTOR DATA - AU]![Discount Charged]/([I FACTOR DATA - AU]![Discount Margin]+0.0898))*[INTERMEDIARY AGREEMENTS]![Commission Discount Margin]) AS IntermediaryTrailCommission, [CRM DATA - CLIENT].[Intermediary Company]
FROM ([CRM DATA - INTERMEDIARY] INNER JOIN ([CRM DATA - CLIENT] INNER JOIN [I FACTOR DATA - AU] ON [CRM DATA - CLIENT].[True Client Number] = [I FACTOR DATA - AU].[Client Number]) ON [CRM DATA - INTERMEDIARY].[Company Name] = [CRM DATA - CLIENT].[Intermediary Company]) INNER JOIN [INTERMEDIARY AGREEMENTS] ON [CRM DATA - INTERMEDIARY].[Company Name] = [INTERMEDIARY AGREEMENTS].[Intermediary Company]
WHERE [I FACTOR DATA - AU].[Extract Date]=EnterExtractDate;

Good luck.

mellis · Nov 18, 2014

Thanks for your fast response!

I've tried to copy the revised SQL in to Expression Builder but there appears to be a character limit that prevents it from being entered fully.

Should I be entereing the SQL in through a different method? A bit of a newbie here so sorry if this seems like a daft question.

I'll take a look at revising the field names also. There are multiple IFactor tables as the data is extracted from 2 different programs. Again I will look in to how to manage this once the basics are set up. It would be ideal if on import they dropped in to the same table.

jdraw · Nov 18, 2014

Yes - use the query designer,open your query, go to SQL mode
and paste in the code I gave.

mellis · Nov 18, 2014

Ok, thanks...that makes a lot more sense.

So the query is giving the outputs I want, as is the rptTrailSUB.

The issue is purely with the actual rptTrail i.e. when I open it, I am asked for the date multiple times (say 28/02/2014). Very frustrating and I'm sure I must be missing something simple!

jdraw · Nov 18, 2014

I didn't look at that report, I looked the the qryTrail. I'll take a look.

jdraw · Nov 18, 2014

You are using the same query for the sub report. So, I don't see any issue with the query.
What links the Report and subreport?

mellis · Nov 18, 2014

It links the "Company Name" from "CRM DATA - INTERMEDIARY" & "Intermediary Company" from "CRM DATA - CLIENT".

Explaining this makes me realise they should be called the same thing!

Could it be the way the relationship is set up between the tables? The same record could appear many times in the CRM DATA - CLIENT but only once in CRM DATA -INTERMEDIARY.

jdraw · Nov 18, 2014

You know your situation better than I do. I did highlight earlier that all your tables show a 1 to 1 relationship. However, if
Could it be the way the relationship is set up between the tables? The same record could appear many times in the CRM DATA - CLIENT but only once in CRM DATA -INTERMEDIARY.
then you have a 1 to many and that would affect your set up.

Report Parameters Being Requested Multiple Times (1 Viewer)

mellis

Registered User.

Attachments

jdraw

Super Moderator

mellis

Registered User.

jdraw

Super Moderator

mellis

Registered User.

jdraw

Super Moderator

jdraw

Super Moderator

mellis

Registered User.

jdraw

Super Moderator

Similar threads

Users who are viewing this thread