Reporting On Multiple Tables
I am trying to create a report that accesses data and calculations from two different tables.
The first table is a basic customer table that contains basic contact info, name, address, phone, etc.
The second table is a table that tracks all appointments with those customers, they are tied together by the primary key of the contacts table in a one-to-many relationship. This table tracks what product we sold them, how many, how much they paid, etc.
What I want to do is get a count on a report that will show the total number of contacts, even if they have never purchased anything or they don't have an appointment record in the other table. I just want a record count of the total. I then want another count of how many out of those records have set an appointment with us regardless if they made a purchase or if the appointment was cancelled. I just want to know how many have converted into customers/potential customers from those contacts.
How would I go about doing something like this? I've tried using calculations that count the ID records but if the report is based off of the appointment table I only get a count of the appointment records that exist, and if I try to base the layout off the contacts table, all the sales information and percentage calculations won't show up on the report.
I've been scratching my head for hours trying to figure out some way to use calculations to no avail. Please someone tell me you can help me.