Queries in reports

[Guy999]

Hi,

It's a bit hard to explain, but can anyone help me with producing an invoice report?

I'm trying to insert a query for each company invoice that shows a summary of the messages they've sent. I've written the queries but I'm asked each time I run them to enter the ID to select the data from, so I guess when I insert this query into the report, or insert a subreport based on this query, it cannot reference it (even though the id is already used in the report to identify the company)

I'm trying to insert this total on each company report:

SELECT Count([id]) AS emailandnumber
FROM Mar_02
WHERE [mar_02].[id]=[company].[id] And ( Is Not Null And [mobile] Is Not Null);

Any help appreciated.
Guy

 

[mdthornton]

Not too clear on how you are trying to go about this. Can we have some more detail? Could you not just include the "emailandnumber" value as part of your main report's data set? Best Regards,
Mike

 

[Guy999]

I'm generating the invoice for each company from a table which contains what's been sent (table Mar_02) and an id which links to a company from the company table.

Displaying the company name and then what's been sent in the report is no problem, but I'm then trying to insert a query showing a summary for each company which doesn't work very well.

Every time I run the query I'm asked to enter the company id to select the data from, so when I put this into the report I'm asked the same thing, which I don't want to do for each company that it generates for.

What I guess it should be doing is taking the company ID which is already used in the report and run the query with that ID for each company.

Any ideas?


Tables:-

Company
========
id
name
etc

Mar_02
========
id
customername
mobile
email
etc


Report:-


#company.name#
#company.address#

Total email and mobile sent: #emailandnumber#

Name Mobile Email
#mar_02.name# #mar_02.mobile# #mar_02.email#
#mar_02.name# #mar_02.mobile# #mar_02.email#
#mar_02.name# #mar_02.mobile# #mar_02.email#


Query to put emailandnumber in first message.


The main report query is:

SELECT Mar_02.id, company.name, Mar_02.name, Mar_02.mobile, Mar_02.email, company.id, company.address
FROM company INNER JOIN Mar_02 ON company.id = Mar_02.id;

 

[mdthornton]

Could you make your main query an aggregate and generate a count that way, eg:

SELECT Mar_02.id, company.name, Mar_02.name, Mar_02.mobile, Mar_02.email, company.id, company.address, count(Mar_02.*) as emailandnumber
FROM company INNER JOIN Mar_02 ON company.id = Mar_02.id
group by Mar_02.id, company.name, Mar_02.name, Mar_02.mobile, Mar_02.email, company.id, company.address;

This is a bit of a fudge but should give you what you want.
If not let me know, I have some other (more involved) ideas. Best Regards,
Mike

 

[Guy999]

Hi

Won't that just add a record count for each company though? I need to run a query with a where clause to count records with an email and number in them, records with just an email in them and records with just a number in them to generate the summaries.

Can I write something like a query with the expression builder and stick it in the report?

Thanks