You can definitely perform JOINs in MSAccess database queries if all of the tables reside in the same database, although the syntax for MSJet-SQL (the underlying SQL engine in the MSAccess format) may have slight differences from ANSI SQL, T-SQL (used in SQL Server), or the SQL dialect in MySQL (I'm not sure what that one is called). You are far better off trying to get JOINS working in direct database queries than trying to stitch together a bunch of query-of-query queries. We can help you through that if you provide more information:
- What is the structure of the database tables you are pulling data from? Specify which columns would be used to join the tables.
- What statistics are you trying to calculate (it looks like you are doing various counts of records).
Also, you might be able to peform the percentage calculations in your output code (not in the database query) by using ColdFusion arithmetic.
* There is just a single table. I am interested in just the following field from it:
*The name of the vendor (leadsource)
*The date the vendor sent us a lead (date_received) that I want to reflect as a count
*The date an appointment was scheduled (will contain a date or will be null if we are unsuccessful) that I want to reflect as a count
*The BL_Number if the appointment results in new business (a booking) that I want to reflect as a count
So I look to create 3 separate counts for each vendor. So vendor A might have sent us 25 leads - of which 5 appointments were successfully set - from which 2 bookings resulted. The table to be created would list each vendor and display how many leads, appointments, and bookings occured. The trick is for the table to list every vendor having sent us a lead regardless if an appointment booking occurred.
Vendor Leads Appts Bookings
ABC 25 5 2
XYZ 10 0 0
MNO 5 2 0
I think this can be done with effective use of JOINS and subqueries. First, write separate queries to create each of the three counts:
- Count records WHERE vendor sent a lead, GROUP BY vendor (if this table only contains leads, then you wouldn't need a WHERE clause since every record would be included in the count). Your query should output two columns: vendor, and the count (maybe call it "leads_count").
- Count records WHERE appointment date IS NOT NULL, GROUP BY vendor. Your query should output two columns: vendor, and the count (maybe call it "appointments_count").
- Count records WHERE booking number IS NOT NULL, GROUP BY vendor. Your query should output two columns: vendor, and the count (maybe call it "bookings_count").
Once you verify each of those queries works to your satisfaction, you can combine the code to create a master query (like this pseudo-query):
SELECT a.vendor, q1.leads_count, q2.appointments_count, q3.bookings_count
FROM myTable AS a
INNER JOIN (put your first query from above here) AS q1 ON a.vendor = q1.vendor // Every vendor will be present at least once in this table, so an inner join should be okay here
LEFT JOIN (put your second query from above here) AS q2 ON a.vendor = q2.vendor
LEFT JOIN (put your third query from above here) AS q3 ON a.vendor = q3.vendor
ORDER BY a.vendor
You will get a row for each vendor. For vendors that have not made any appointments or have no bookings, those columns will be NULL. There is a way to have Access replace any null values coming from the LEFT JOINS with something else (like a zero) involving using IFF( IsNull( column_name ), value_if_true, column_name ).