unused items from list

  • Thread starter Thread starter Kim Weiss
  • Start date Start date
K

Kim Weiss

Hi!

I am trying to create database of customers and market areas they have
reserved. Now I have made a list of areas, as list of numbers. Problem is
that I can select same number more than once.

I have this kind of SELECT sorting out numbers, doesn't work. Area is table
to get all numbers and AreaReservation is table to hold reserved number. And
hopefully to sort out already used numbers.

SELECT DISTINCT Area.AreaNumber FROM Area NOT IN (SELECT
AreaReservation.Number FROM AreaReservation);


If you have any idea, please help me! Thank you!

br. Kim
 
SELECT DISTINCT Area.AreaNumber FROM Area WHERE Area.AreaNumber NOT IN
(SELECT AreaReservation.Number FROM AreaReservation);
 
Kim:

You need to put the subquery in a WHERE clause:

SELECT AreaNumber
FROM Area
WHERE AreaNumber NOT IN(
SELECT Number
FROM AreaReservation);

Alternatively you can use the NOT EXISTS predicate:

SELECT AreaNumber
FROM Area
WHERE NOT EXISTS(
SELECT *
FROM AreaReservation
WHERE AreaReservation.Number = Area.AreaNumber);

However, you don't really need to use a subquery; you can use a LEFT OUTER
JOIN and test for the Number column in AreaReservation being NULL:

SELECT AreaNumber
FROM Area LEFT JOIN AreaReservations
ON Area.AreaNumber = AreaReservations.Number
WHERE AreaReservations.Number IS NULL;

This last method is the one used by Access's built in 'Find Unmatched Query
Wizard'

You can prevent the same number being entered more than once in the
AreaReservations table by creating a unique index on the Number column.

You don't say what other columns the AreaReservations table has, but I'd
expect it to include a CustomerID column or similar to identify which
customer has selected the area in question. From what you say I take it that
each area can be reserved only once, but each customer can reserve more than
one area, i.e. there is a one-to-many relationship between customers and
areas. If so then the AreaReservations table is in fact modelling this
relationship. This is quite valid, but unusual as the relationship can be
modelled simply by including a CustomerID column in the Area table.
Unreserved areas would have a Null CustomerID column. Modelling a
one-to-many relationship by a separate table, as you appear to have done, is
a recognized method of avoiding Nulls, however, so there's no reason for not
doing as you appear to have, and some would argue that it is a better model
where Null foreign keys are otherwise possible.

BTW the above also holds true if the relationship between customers and
areas is one-to-one, i.e. each customer can reserve only one area, and each
area can be reserved by only one customer. In that case the CustomerID
column in AreaReservations (or in Area if a simple CustomerID foreign key is
used in that table) should also be indexed uniquely.

Ken Sheridan
Stafford, England
 
Thank you Dave and Ken for your posts, they have been very useful.

I know that many things can be made easier and many mistakes could be
avoided by better table design. Still I'm trying to learn =)

Ken, I have other columns in Reservation table. There is column for
CustomerID, InvoiceStatus, NeedForElectricity, DataOfReservation. I think I
can put all that indormation to Area table, and that should be easier than
sorting out Area numbers. Other problem is that I cannot index it, because
same customer can make reservation to many Areas. Have to think it over...
This is fun even it is hard =)

-Kim-
 
Kim:

With the other columns in AreaReservations it looks to me like your table
designs are pretty good. You could put the CustomerID, InvoiceStatus,
NeedForElectricity and DataOfReservation columns in the Area table, but this
would only would allow you to record the current reservations for each area.
If you want your database to keep a record of all reservations over time,
however, then the AreaReservations table allows you to do this as you can
enter as many rows per area into it as necessary, each with different
CustomerID and DateOfReservation values, or the same CustomerID and different
DateOfReservation values if a customer reserves the same area more than once
at different times. The current reservation would be the one with the latest
date of course.

What the AreaReservations table actually represents is a many-to-many
relationship type between the Area and Customers entity types. Tables model
entity types and their columns represent attribute types of each entity type.
A relationship type is really just a special kind of entity type, so
AreaReservations is also an entity type with its own attribute types
represented by its columns.

As far as indexing this table is concerned the combination of Number and
DateOfReservation values must always be unique, as the same area presumably
can't be reserved twice at the same time, so these columns form what's known
as a 'candidate key', and should be indexed uniquely in combination. In fact
you can define them as the primary key. In table design view you do this by
highlighting both fields (fields and columns are essentially the same thing,
but column is a more correct term in the context of a table in a relational
database). You can then right click on the highlighted fields and select
Primary key from the shortcut menu. The key symbol will appear against each
field.

BTW I'd recommend you use the same column names in each table when they
represent a primary and foreign key pair, so I'd suggest you use AreaNumber
both in Area and AreaReservations. Queries are easier to both read and write
if this convention is followed. It can't always be done. You might have a
primary key column EmployeeID in an Employees table and a foreign key
SupervisorID referencing the primary key of the same table for instance. As
they are both in the same table they obviously need to have different names.

Ken Sheridan
Stafford, England
 
Now I think I have had too complicated database. I think I should tell you
the whole story. We arrange two day summerfestival, where to we sell about
100 marketplaces. I have got almost 100 postit notes where do we have contact
information for market sales people. Now I would like to make database to put
all sales people information and also marketplace reservations.

Now to make it simple, I like to make this database concerning one summer,
one time. For next year I can empty some columns or develop it further.

I believe I can put all the information in two tables, one for Area and
another for Customer. Area table has columns for AreaNumber, AreaSize and
AreaElectricity. Customer table can hold all customer details, address, phone
number etc. But new idea is to put also InvoiceStatus and DataOfReservation
to Customer table. And also information who we have contacted by phone and
who we have sent information post.

Now I have to think into which table to put information about which customer
has reserved which area(s). And does it really work to sort out already
reserved areas... But here we go, thank you very much for your help. Keep
reading this forum, I have new questions some day later!

-Kim-
 
Kim:

The InvoiceStatus and DateOfReservation columns could go in the Customer
table provided that, if a customer reserves more than one area, they are both
subject to a single invoice and reservation date. If each area is invoiced
separately to the same customer, and/or a customer can reserve separate areas
on separate dates, then the columns would have to go in the Area table.

Whichever way you do it you can still easily identify which customers have
reserved which areas, or which areas are as yet not reserved,

As regards contacts, you'll probably need a separate Contacts table for this
with a foreign key CustomerID column, and columns for the contact's name,
phone number, date and method of contact etc. If you include it in the
Customer table then you can only record one contact with the customer, which
I'd have thought unlikely.

Ken Sheridan
Stafford, England
 
It has been fun doing this. Thank you for your help!

Now I have another problem. For each customer I might not have both person
name and company name. So there will be private person and also small
companies. At the moment I have three columns, Company, FirstName and
LastName. I am wondering if I should combine FirstName and LastName. Then I
have one coluns less. I made a dropdown combobox for Area table to select
from all three information but then it leaves only one column visible. If
there is no company name, cell remains blank. Or something else if I chance
order.

I dont want to put names and company names to one list because if I have all
three information, I can use them all in posting material to them.
 
Kim:

It gets to be even more fun now!

Keep the three columns; its always easier to combine values form more than
one column than to parse a single column into two or more values.

In the Areas table you'll store the CustomerID of course, regardless of
what's in the other columns, so you can control what you see in the combo box
to handle the possible scenarios you describe. Set up the combo box like
this:

RowSource: SELECT CustomerID, (FirstName+" ") & (LastName) & IIf(
IsNull(LastName),CompanyName,(", " + CompanyName)) AS FullName FROM Customers
ORDER BY CompanyName & LastName & FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one (that's for the benefit of Americans, who
still use inches, but probably not relevant in your case, I'd guess). The
important thing is that the first dimension is zero to hide the first column
and that the second is at least as wide as the combo box.

This works because Nulls 'propagate', i.e. Null + anything = Null. So
(FirstName+" ") will only add a space after the first name if a first name is
entered in the field. Similarly IIf( IsNull(LastName),CompanyName,(", " +
CompanyName)) inserts the company name on its own if no last name is in the
field, or, if a last name has been entered, a comma and a space followed by
the company name if a company name has entered. So the value in the
computed FullName column if only personal names are entered might be 'Ken
Sheridan', if both personal and company names are entered ''Ken Sheridan,
Sheridan Widgets', or if only a company name is entered 'Sheridan Widgets'.
This is what you'd see in the combo box, but its value would be the
CustomerID value in each case. The list would be ordered alphabetically by
company name if a company has been entered, otherwise by last name.

When compiling mailing lists from the database you can use a query similar
to that used as the RowSource above for the RecordSource of a report; you'd
just need to add in the address columns, though you'd probably want to insert
carriage returns/line breaks (Chr(13) & Chr(10)) between the columns so each
appears on a separate line. The query would go something like this:

SELECT Customers.CustomerID, (FirstName+" ") & (LastName) &
IIf(IsNull(LastName),CompanyName,(Chr(13) & Chr(10) + CompanyName)) &
(Chr(13) & Chr(10) + Address1) & (Chr(13) & Chr(10) + Address2) & (Chr(13) &
Chr(10) + City) & (Chr(13) & Chr(10) + PostCode) AS FullAddress FROM
Customers ORDER BY CompanyName & LastName & FirstName;

which would give results such as:

Ken Sheridan
Sheridan Widgets
42 Nameless Street
Stafford
ST66 9LG

or:

Sheridan Widgets
42 Nameless Street
Stafford
ST66 9LG

or:

Ken Sheridan
42 Nameless Street
Stafford
ST66 9LG

depending on what data had been entered.

BTW there was once a company called 'Sheridan Widgets' which produced Visual
Basic add-ins, but it had no connection with me.

If you have any problems implementing the above mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

and I can send you a file with the queries etc.

Ken Sheridan
Stafford, England
 
Wow! It really works! I already got a kind of solution but it looked a lot
worse. Names were all in different slots.

This is great! Now I can put also another column for year 2007 data and dont
need three more columns to show all the information. And I guess this formula
is useful for me for my other projects! I have been tryin to make a trip and
sales database for my small business.

I try to make address labels a bit later, I'll get back to you if there is
something... Never done that before.

Thanks again for your help Ken! I guess you have been doing these for a while?

-Kim-
 
Kim:

Yep, more years than I like to remember! Its exactly 20 years since I put
together my first database application. I was using dBASE in those days and
started developing applications in connection with my work as an
Environmental Planning Officer. I changed to Access in 1997.

Good luck with your other projects. You have my email address, so don't
hesitate to mail me directly if there's anything you think I can help with.

Ken Sheridan
Stafford, England
 
Back
Top