Insert Count from Table Not in Details Section

[staciann]

Hi.

I have a report that uses two tables:

tblRequestedInformation
tblNewCustomers

My two tables are linked by a PN number:

tblRequestedInformation.PN
tblNewCustomers.PN

The details section of my report breaks down the [/i]tblRequestedInformation[/i] by tblRequestedInformation.HowHeard and tblRequestedInformation.Specific. I then have an (*) added next to the records from tblRequestedInformation that also appear in tblNewCustomers.

I have a count of records with an (*) at in the report footer but I need to add a count of the total number of records in the tblNewCustomers. I tried to insert a count by tblNewCustomers.PN but it causes my details section to go from 8 pages to over 2,000. I don't want this info added anywhere else in my report - just the count in the footer. Is there a way to add this count without it effecting the rest of my report?

Thank you in advance for your help,
Staci

 

[IanWaterman]

First you need to determine why your reports grows so much.

ie why does including data from tblNewCustomers cause so many duplications.

YOu can then group your data to eliminate duplications by displaying your data in the group footer instead of the detail, suppress detail and you will be back to your 8 pages and have correct count of tblNewCustomers.PN .

Ian

 

[staciann]

Well, what it seems to be doing is causing the detail section to repeat by each tblNewCustomers.PN

Here is a better explanation of what the report is summarizing: The report is designed to summarize Quote Requests that turned into jobs (from tblRequestedInformation) and which medium they come from (tblRequestedInformation.HowHeard and tblRequestedInformation.Specific). Of these, we figure out how many of these jobs were from new customers (which are all listed by PN in tblNewCustomers). Then I need to figure out how many jobs from new customs came in from trackable means compared to the total number (which is what I need the count for).

I can't group by tblNewCustomers.PN because I'm already grouped by:

tblRequestedInformation.HowHeard and tblRequestedInformation.Specific

The information I need a count of (tblNewCustomers.PN) does not apply to or link to any information in the above groups or in my details section. It's a count of total records in tblNewCustomers. But of this total count of records (which is around 300), only 30 or so actually link by PN to the tblRequestedInformation. These are the records that get an * next to them. The number of * and the total number change every day - which is why I need a count of them.

My details section includes:

tblRequestedInformation.PN
tblRequestedInformation.Date
tblRequestedInformation.Price
(and an * if it's a new customer)

 

[lbass]

How did you add the asterisk? Did you create a formula? If so, please show the content of the formula.

-LB

 

[staciann]

if {tblRequestedInformation.NewCustomer} = True
then "*"

My database is in access. I run a query to find fields with the same PN in both tables and then the records the query brings up get checked in tblRequestedInformation.NewCustomer.

 

[lbass]

You're not trying to count that formula, are you, because counts inserted on formulas return only the number of time the formula executes. If you add another formula:

if {tblRequestedInformation.NewCustomer} = True
then 1

You can insert a sum on this at the report level.

Referencing the second table will cause the records to inflate, if there is a one to many relationship, but you could insert a group on the value that is repeating and place the fields in a group section. Or instead, you could format the fields to suppress if duplicated and then format the detail section to "suppress blank section".

-LB

 

[staciann]

That's exactly what I did - I have a separate formula that makes each true value = 1 and then count that to get the count of tracable new customers. (I just have the asterisk in so when people look at the printed page they can tell which records are new customers.) This works fine. I have no problem with that count.

The count I need to get, but can't, is the count of total new customers - which needs to come from counting the total number of records in tblNewCustomers --- not tblRequestedInformation. Everything in the report is based off of tblRequestedInformation. The count I need is from a table I don't want to reference anywhere else in the report.

 

[staciann]

Thanks. I will try that.

 

[lbass]

You have to insert a SUM on the formula, NOT a count. But you will still get the inflation. The only other solution would be to remove the second table and reference it in a subreport placed in the report footer. You would suppress all but the sub report footer.

-LB

 

[staciann]

OK...that's not going to work. I have no way of grouping them by anything in that table because nothing matches up. I thought I could do the second option (supressing duplicates and supressing blank sections) but that won't work either because I need duplicated *, dates and dollar amounts. I just don't need duplicated PNs. If I only supress duplicated PNs it still gives me the corellating dates, $, etc. But if I supress these other fields it won't give me the duplicates I need. Ex. If my info looks like this and I supress the duplicates, it doesn't give the the records with duplicated dates or dollar amounts.

PN Date Amount

12345 10/13/07 $2500.00
33456 10/06/07 $2500.00
45745 10/13/07 $500.00
47465 10/02/07 $500.00

 

[staciann]

Sorry, I think it is a sum, not a count. My mistake.

Thanks for your help.
I don't think I can use a subreport though becuase I need that sum/count to create formulas that reference both numbers (the total and the asterisked number) to figure out percentages, etc.

 

[lbass]

I'm having trouble telling what you want to do. You CAN use a subreport in the report header for the amount, and then set up the amount as a shared variable which can be used in formulas in the body of the report, e.g., in the sub, use:

whileprintingrecords;
shared numbervar newcust := sum({@newcust});

Then in the main report report footer, reference it in formulas like:

whileprintingrecords;
shared numbervar newcust;
newcust % distinctcount({table.customer})

If you need the percentages at the group level, you would place the sub in the group header, with a link on the group field.

-LB

 

[LughS]

Personally, I think you need to set Crystal aside, and go back to Access. I think you can solve this problem much more easily in the source database.

First, what is the relationship between tblRequestedInformation and tblNewCustomers? One to many? Many to many? I.e., do you have records in tblRequestedInformation that are not in tblNewCustomers? Do you have records in tblNewCustomers that are not in tblRequestedInformation?

I'm assuming that you have both, based on the previous conversation. But, I wanted to clarify.

Second, you can simply insert the information you want into the query you built in Access. In the query, add an additional subquery that simply gives you the total New Customers you want. Have it look like:

SELECT RI.*, IIF(NC.PN IS NULL,"*","") AS NewCheck, NT.NewTotal
FROM tblRequestedInformation RI
JOIN 
   (SELECT COUNT(PN) AS NewTotal
    FROM tblNewCustomers
    WHERE {insert criteria here}) NT
LEFT JOIN tblNewCustomers NC
ON RI.PN = NC.PN
WHERE {insert criteria here}

That will put the total of new customers in each record you retrieve. Put a Max in the footer to reference it there. Do with it what you will.