Return number of records based on a given number

[Simon Harris]

Hi All,

I am writing an Access application to keep track of what customers are
staying in what caravans on a number of Holiday parks.

Each Park has one or more pitches, each pitch has one customer assigned to
it.

I do not wish to, or need to store details of each pitch (Each pitch is the
same - A number, assinged to a given park)

I do need to:

1) Know what customer is on what pitches
2) Ensure that only 1 customer is on 1 pitch

So far, I have:

- tblCustomers (Address, site ID, Pitch Number)
- tblSites (Site Name, No of Pitches)

I am now trying to write a query that will return a list of pitch numbers
based on the value stored in tblSites which I am hoping will return a rwo
for each pitch (Even though we do not physically have this data). I then
plan to do a 'not exists in' query on this, hopefully showing me available
pitches.

Have I got my logic totally wrong here? Any ideas how I might achieve this?

Thanks!
Simon.

 

[Graham Mandeno]

Hi Simon

First, you should not be storing the SiteID and PitchNumber in tblCustomers.
These are attributes not of the customer, but of that customer's particular
stay. Presumably you want (if not now then later) to keep track of
customers who return for multiple stays. Perhaps you might want this for a
loyalty scheme, or if not, to eliminate duplicate entries in your customers
table. So, tblCustomers should contain ONLY attributes of the customer -
CustomerID (could be an autonumber), name, address, phone(s), preferred
method of payment, etc.

Next you need a table to record the stays (tblStays) with CustomerID,
SiteID, PitchNumber, DateIn and DateOut (perhaps you want to add other
fields such as the charge for the stay, whether it's paid for, special notes
like "arriving after 8pm", etc)

Now you need a simple table, tblPitchNumbers, with one field, PitchNumber,
which contains one record for each number from 1 to something at least as
great as the maximum number of pitches.

You can then list the pitches that are being used at a given site during a
given date range as follows:
SELECT PitchNumber FROM tblStays
WHERE tblStays.SiteID=[Required Site]
AND tblStays.DateOut>[Required Date In]
AND tblStays.DateIn<[Required Date Out];


So, you can find the available pitch numbers for a given date range at a
given site as follows:
SELECT tblSites.SiteID, tblPitchNumbers.PitchNumber
FROM tblSites, tblPitchNumbers
WHERE (tblSites.SiteID=[Required Site])
AND (tblPitchNumbers.PitchNumber<=tblSites.NoOfPitches)
AND (tblPitchNumbers.PitchNumber Not In
(SELECT PitchNumber FROM tblStays
WHERE tblStays.SiteID=[Required Site]
AND tblStays.DateOut>[Required Date In]
AND tblStays.DateIn<[Required Date Out]));