matching days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have an access database with two tables. one is details of customers and
the other is installers. in both tables i have the fields "available sunday"
"available monday" all the way through to sunday (therefore 7 fields). for
each of these, i have just yes no options. i need, if possible, to build a
query to match up the customers available on one day to installers on that
day. thankyou in advance
 
Your tables are wrongly designed, I'm afraid. By storing the days
availabilities as Boolean (Yes/No) columns you are doing what's known as
'encoding data as column headings'. Data should only be stored as values at
column positions in rows in tables. What you should have are separate tables
CustomerAvailability with columns CustomerID and DayAvailable, and
InstallerAvailabilty with columns InstallerID and DayAvailable, so if
Customer 42 is available on Monday's and Tuesdays only there would be rows in
CustomerAvailability:

42 2
42 3

The second column should reference a DaysOfWeek table with 7 rows 1 Sunday,
2 Monday etc. By enforcing referential integrity in the relationships
between this and CustomerAvailability and InstallerAvailabilty you can't
enter a non-existent day (day 8 of the week for example).

To find matching Customers, Installers on Mondays say:

SELECT DISTINCT Installer, Customer
FROM Installers, InstallerAvailability, Customers, CustomerAvailability
WHERE Installers.InstallerID = InstallerAvailability.InstallerID
AND Customers.CustomerID = CustomerAvailability.CustomerID
AND InstallerAvailability.DayAvailable = 2
AND CustomerAvailability.DayAvailable = 2;

This joins Installers to InstallerAvailability and Customers to
CustomerAvailability by means of the join criteria in the WHERE clause, and
restricts each to those available on Mondays (day 2). As the results of
these two joins are not joined by any criteria it joins every row from each
with every row from the other (what's known as the Cartesian product of the
two sets) so what you get is all possible combinations of Installer/Customer
on Mondays.

Ken Sheridan
Stafford, England
 
Thanks for that. Ive built the tables and set up relationships

I now need to build the query. The one which you have written

"> SELECT DISTINCT Installer, Customer
FROM Installers, InstallerAvailability, Customers, CustomerAvailability
WHERE Installers.InstallerID = InstallerAvailability.InstallerID
AND Customers.CustomerID = CustomerAvailability.CustomerID
AND InstallerAvailability.DayAvailable = 2
AND CustomerAvailability.DayAvailable = 2;"
Click to expand...

do i need to just write this into a query 7 times (changing 2 for each day)
and will this work

please advise thanks
 
When i tried to enter that query, it said that the syntex was incorrect and
it needs paarenthasis

pls advise
thanks
 
I think you must have made a mistake somewhere in entering the SQL as I've
tried it and it works fine. As regards dealing with all 7 days, rather, than
have 7 separate queries you can have just one and restrict its result either
by means of parameters in the query or by basing a report or form on the
query and filtering it to the day in question. A query for the latter
approach, extending it to bring in the Days table so that it shows which days
the Installers and Customers are mutually available would be:

SELECT Installer, Customer, InstallerAvailability.DayAvailable, DayName
FROM Installers, InstallerAvailability, Customers, CustomerAvailability, Days
WHERE Installers.InstallerID=InstallerAvailability.InstallerID
AND Customers.CustomerID=CustomerAvailability.CustomerID
AND CustomerAvailability.DayAvailable = InstallerAvailability.DayAvailable
AND InstallerAvailability.DayAvailable = Days.DayID;

Maybe the easiest way to get you started would be for me to put this all
together in a little demo database for you. If you'd care to mail me at:

ken<dot>sheridan<at>dsl<dot>pipex<dot>com

I'd be happy to send you a file back. Let me know what version of Access
you are using.

Ken Sheridan
Stafford, England
 
Back
Top