Count total records of an unlinked table

[staciann]

Hi. I have a report that breaks down our marketing results and shows each of the ways our customers found us and how much money each source brought in. All of this information comes from tblRequestedInformation.

Group #1 (How customers found us)
Group #2 (Specific name of source)
Details (Project number, date of start, price)

Then I have a formula that marks each customer in the details section with an asterisk if they were a first time customer.

if {tblRequestedInformation.NewCustomer} = True
then "*" [/color green]

I have running totals of the prices set up for each group and a Grand Total in the footer. All of that works fine.

I need to add the total number of first time customers for the year and then how many of them we were able to track. I am able to get the number tracked by putting a count of the asterisks in the footer - but the total number of new customers is in tblNewCustomers not tblRequestedInformation.

I've tried creating a formula like that of the count by asterisk:

if {tblNewCustomers.PNDate} > Date (2007,12,31)
then "1" [/color green]

But when I try to create a sum or a count in the footer it keeps returning only the number of records matched up between the two tables or it adds thousands of duplicate records in the details section. I've tried using different types of joins but have had no luck.

The reason I am adding it is to show how many new customers there were total and how many we were able to track (as we can of course only track a portion).

I'd prefer not to have to create a subreport for this one sentence.

Is there a way to do this in my current report footer?



Thank you for your help.

 

[lbass]

If this is a join issue, we would need to see what fields you are working with in each table.

If there are many records in the second table for each record in the first, you could use running totals that evaluate on change of some field.

-LB

 

[lbass]

Oh, yes, and you should join the tables in some way.

-LB

 

[staciann]

Well... I am not using any actual fields from tblNewCustomers in the report - all I want is the total number of records in the table so I can use it in formulas.

All of the data in the report is from tblRequestedInformation.

The two tables do link by {tblRequestedInformation.PN}and {tblNewCustomers.PN}

It is not a one-to-many relationship.

tblNewCustomers has records for every new customer we have had for the year.

tblRequestedInformation has records for every quote request we have gotten via our website in a year. There would only be one record in tblNewCustomers that would match with one record in tblRequestedInformation.

There are many more new customers in tblNewCustomers than what have come in through our website in tblRequestedInformation (the others in tblNewCustomers may have come in through salesmen, phone calls, consultants, etc). I am just doing analysis of the web requests right now in tblRequestedInformation.

So I need to have the total number of new customers from tblNewCustomers so I can subtract the number of requests that have come in through the website (in tblRequestedInformation) or figure out percentages of each, etc.

For example:
tblNewCustomers may have 1,583 records in it but only 947 came in through the website and are listed in tblRequestedInformation. I just need to figure out how I get 1,583 into my report. (I can't just type it in because the numbers are constantly changing - I need it to pull the info when the report is run at any given time.)

Does that make sense? I'm not sure if I'm explaining myself clearly.

Thanks.

 

[lbass]

I think your best bet is to insert a subreport in the report header where you use an equal join between the two tables. Then set up a shared variable in the subreport report footer:

whileprintingrecords;
shared numbervar x := distinctcount({tblNewCustomers.PN});

Then just reference the variable in your formulas.

You could also try setting up a command that links the two tables with an equal join and has the date criterion, and then linking the command with a left join to the new customer field in the requested info table, but that also will be slow.

-LB