Is it Possible?????????

[daherb]

Is it possible to create a report from a table or multiple queries that show every entry and non entry in a database????

What i am trying to do is as follows....

I have a database that has a table holding all my customer information and a table holding all the information regarding their enquiries. I want to produce a report that will show every customers activity. i.e all the enquiries that they have made but i also want the customers that have made no enquiries to appear in the report. That way i can identify customers that need to be contacted to find out why they are not making enquiries.

Can anyone give me an idea of how to do this?????

 

[john471]

Presume you have two tables,

tblCustomer
-----------
CustomerID (Primary Key)
NameLast (text)
NameFirst (text)
other customer attributes...

tblEnquiry
---------
EnquiryID (Primary Key)
EnquiryDetails
CustomerID (Foreign Key to tblCustomer)
other enquiry attributes...

create a query (SQL below)
qryMasterCustEnq
-----------------
SELECT tblCustomer.NameLast, tblCustomer.NameFirst, tblEnquiry.EnqDetails
FROM tblCustomer LEFT JOIN tblEnquiry ON tblCustomer.CustomerID = tblEnquiry.CustomerID;

This will show all customers, regardless of them having associated enquiries or not, and any associated enquiries, if they have them.

Build your report based on the query.

HTH

Regards

John

 

[john471]

P.S would also be wise to include CustomerID in your query, so when you have two separate "John Smith" customers, you can report on them separately.

 

[daherb]

thanks for that john, your a star

 

[john471]

You're welcome. Go forth and report !
Thanks for taking the time to reply back.

 

[daherb]

One more question if you dont mind.

In my report i have a number of enquiry entries that have been authorised. To specify whether this has happened the database has a yes/no format in the enquiries table. I have included this field in the report but in the summary of the report for each customer i want to calculate and display the sum of entries authorised. I know that Count(*) counts all the records in a report and displays the results but can you specify what you want to be calculated. i was thinking along the lines of Count([fieldname] where [fieldname]=yes) but it doesn't work as i was pretty sure it wouldn't as it was a complete guess.

Can it be done and how?????

 

[Pat Hartman]

1. When reporting on 1-many relationships, many problems are avoided by using a main report for the 1-side data and a subreport for the many-side data rather than attempting to create the report entirely from a single query.
2. To "count" yes answers:
=Sum(Abs(YourField))

Since Yes = -1 and No = 0, if you sum the values you will get a negative value that will be a count of all the yes answers. So, use Sum() in conjunction with Abs() to get your answer. Counting Nos is a little more complicated:
=Sum(IIf(YourField = 0,1,0))
In this statement, the IIf() returns 1 if Yourfield is No and 0 if it is yes and again the Sum() sums the "count".