Report showing data from two tables

  • Thread starter Thread starter Russell Pascoe
  • Start date Start date
R

Russell Pascoe

Hi,

I run a small property rental company. I have a database that contains
several tables. The main table has Tenant Information (primary key, first
name, last name, property, move in date, move out date, rent account number),
another table called Addresses, surprisingly, holds the full address of each
property, where the property name is the primary key and finally a table
called Bank Details and this holds the account number, bank sort code and
propery name, again this is the primary key.

I would like to produce a report that allows me to print the name of the
property, its full address and all of the bank details associated with that
property. This is just a test for me to learn how to make this work for more
useful tasks with the "main" table.

I opened the New Query Wizard and selected the two tables (addresses and
Bank Details) and identified which fields I wanted to be shown. When I look
at the output each property seems to be listed multiple times (two only once)
several properties are listed four times, some three, some five. I cannot
spot a pattern as to why this would be so. I have set up the relationships (I
think) so that the database "understands" the interelationship, each table
only has one instance of each property.

What am I doing wrong? Any advice will be welcomed!

Kind Regards,
Russell.
 
Russell:

In the query the tables should be joined on the Property Name columns. In
query design view you'd have a line between these two columns in each table;
in SQL view the join would be expressed like this:

SELECT <column list>
FROM [Addresses] INNER JOIN [Bank Details]
ON [Addresses].[Property Name] = [Bank Details].[Property Name];

The relationship here is one-to-one as it is on the primary keys of each
table. You don't really need separate tables for this; all the data could be
in columns in the Addresses table.

One-to-one relationships are sometimes appropriate. This is when modelling
types and sub-types (aka classes and sub-classes), where a type might have
several different sub-types, each with its own distinct properties. You
might for instance have a table Employees, modelling the employees entity
type. This would include all employees and would have columns which are
relevant to every employee, e.g. DateHired, Salary etc. A sub type of this
might be SalesPeople, which would be modelled by a table of that name with
columns relevant only to sales people, e.g. SaleArea. The relationship would
be on-to-one with EmployeeID as the primary key of each table, but also as a
foreign key of SalesPeople referencing the primary key of Employees. Other
sub-types of employees would model other classes of employee who have
properties specific to their class.

A characteristic of type/sub-type relationships is that every member of a
subtype shares all the properties of the type, but not those of other
sub-types.

Even though the relationship is one-to-one it is nevertheless directional.
In the above example Employees is the referenced table and SalesPeople the
referencing table, so the relationship should be set up in that way, thus
allowing a row to be inserted into Salespeople only if a matching row already
exists in Employees, but a row can be inserted into Employees without the
need for the prior existence of a row in any referencing table.

Ken Sheridan
Stafford, England
 
Hi Russell,

Your issue could be for many different reasons. I think you should start by
analyzing the following to pin point the issues since you are a new user and
sometimes the basic concepts are missed.

1. In the Address Table - confirm you have no duplicates in the Address
Table (I know you said that is your Key but would like to confirm that you
have identified it as such in the table design view and you have selected -
NO Duplicates in the lower half of the design screen. Duplicates on this
table could cause your query to bring back multiple records when running a
query.

2. Do the same steps above on your Bank Details table.

If neither table has duplicates - then check your relationships.

Go to Tools / Relationships
Click on Show all Relationships
You should show a line connecting Property Name from the Address Table to
the Property Name on the Bank Detail Table.

If Relationships look ok then check your query.

Be sure that both tables are in the Design View of the Query - there should
be a line showing the relationship between the two tables "property Name".
double click on the line - make sure that it shows - only show details where
all records from Address match records from Bank Details - should be listed
as option 3.

If these steps don't work - I would then try the suggestion made by Ken.
 
Back
Top