Multi table queries ACCESS- URGENT HELP

[663scott]

Hi

I am pretty new to ACCESS. I have created some small databases previously. I need to run a simple query searching for a USERNAME which will gather information from five to ten tables containing seperate bits of information and combine them into a report. The primary key on all of the tables is the username.

AS AN EXAMPLE:

TABLE 1

USERNAME
DATE JOINED
NAME
ADDRESS

TABLE 2

USERNAME
FAVOURITE FILMS
FAVOURITE ACTORS

TABLE 3

USERNAME
DATE OF PURCHASE
FAVOURITE SHOP

I would like to create a search for a single username which would find matching results in each table and then combine the relevant information:

DATE JOINED
NAME
ADDRESS
FAVOURITE FILMS
FAVOURITE ACTORS
DATE OF PURCHASE
FAVOURITE SHOP

all in one report. I am using multiple tables instead of combining the info into one table, because each of the tables is filled in by a different department and seperate tables would prevent confusion for those filling the information in. prevent confusing found it easier so separate the information into individual tables. I thought of doing a UNION query but was unsure how the user would enter their desired query prior to running the UNION query. Please help!!!!!

[MMcCarthy]

Firstly you should have a primary key on Table1 (e.g. UserID) which would be number based and use that as the link to the other tables. Using USERNAME as a text field has two problems. 1) It is harder for the database to run queries based on a text field join. If there are any difference in spelling etc in these fields the tables won't join.

Secondly if any of these tables has multiple records for the user the query won't be updatable.

However, the query you need is...

SELECT  USERNAME, DATE JOINED, NAME, ADDRESS, 
FAVOURITE FILMS, FAVOURITE ACTORS,  DATE OF PURCHASE,
FAVOURITE SHOP
FROM Table1 LEFT JOIN 
(Table2 LEFT JOIN Table3
ON Table2.USERNAME = Table3.USERNAME)
ON Table1.USERNAME = Table2.USERNAME;
 

You should probably take the time to read the following tutorial.

Normalisation and Table structures

Mary

[663scott]

Hi

Firstly I would like to thank you for your quick response!!!

Each of the tables currently have the username as the primary key. I find that I can create a query combining two to three tables, and a simple search via username yield a correct result every time. However I recieve a problem when I add a more tables, which I thought would simply add extra fields to my results. However this is not the case, when I run a search I see the field headings only with no results whatsoever even though I know my search is correct. If I change the primary key to a number as you suggest would the results display correctly ??

thank you.

[MMcCarthy]

Hi

Firstly I would like to thank you for your quick response!!!

Each of the tables currently have the username as the primary key. I find that I can create a query combining two to three tables, and a simple search via username yield a correct result every time. However I recieve a problem when I add a more tables, which I thought would simply add extra fields to my results. However this is not the case, when I run a search I see the field headings only with no results whatsoever even though I know my search is correct. If I change the primary key to a number as you suggest would the results display correctly ??

thank you.

I think your problem is in your table design. UserID (replacing USERNAME) should only be the primary key on the first table. Each table should have it's own primary key and UserID should only be a foreign key in the other tables.

Did you check out the tutorial link. This is explained there in a lot more detail.

Mary

[663scott]

Hi,

Wow thank you, I have been able to modify my relationships and now I have been able to join all the tables I require together. I totally restructured the tables and now gather clear results from any query I run. I would like to thank you for helping to teach me to resolve this issue.

There is another small problem which I have found however which I would really appretiate some help on if possible. The relationships in this example does not relate to the new relationships I have created. I am simply wishing to illustrate my thought process on what I would like to achieve. In one of the tables I have the following fields as an example:

TABLE 1

USERNAME [Primary Key]
FAVOURITE FILMS (eg action/Adventure/comedy)
FAVOURITE ACTORS

I would like to have another table with the following information which will list every single type of film the particular customer watched in each genre:

TABLE 2

GENRE
FILM TITLE 1
FILM TITLE 2
FILM TITLE 3
etc

I would like to be able to link the FAVOURITE FILMS link in table 1 to the GENRE link in table 2 as they would contain the same information (ie a genre code). The problem I am having is potentially I would need to enter an unlimited amount of film titles. If I made GENRE in the 2nd table the primary key, duplicate entries would not be allowed so I would need to enter FILM TITLE 1- 10000 potentially to accomodate an unlimited amount of films the customer may watch. However If I did not make it the primary and use a smaller number of entries say FILM TITLE 1- 10 on the table. This would allow the user to generate a new table of entries as and when they are needed on TABLE 2. If a query was run on the user however I would recieved duplicate details from TABLE 1 for each time a new list of entries for table 2 was generated. WHat do you think would be the best way of solving this issue?

thank you very much

[MMcCarthy]

In this case I would have a tables called Genre, Film, Actor and a table called FavouriteFilms

Genre
GenreID (Primary Key)
GenreName

Film
FilmID (Primary Key)
FilmName
GenreID

Actor
ActorID (Primary Key)
ActorName

FavouriteFilm
FavFilmID (Primary Key)
UserID
FilmID

FavouriteActor
FavActorID (Primary Key)
UserID
ActorID

You don't need to refer to Genre in FavouriteFilm as the Genre is already recorded in in the Film table for each film. Also each film and actor only needs to be added once to the film and actor tables.

Mary

[663scott]

Hi,

Thanks for that. My Access database has come on in leaps and bounds thanks to you.

I have another question and I'll give you another example to illustrate it, I hope you can help. If I had a table like this with the top 5 film ID's:

Table

Account ID
FILM ID 1
FILM ID 2
FILM ID 3
FILM ID 4
FILM ID 5

would it be possible to create a query which would allow the user to type in a film ID once but the system would search each field FILM ID 1 - 5 without the need to typew in the ID five times??

Yours gratefully

[MMcCarthy]

Hi,

Thanks for that. My Access database has come on in leaps and bounds thanks to you.

I have another question and I'll give you another example to illustrate it, I hope you can help. If I had a table like this with the top 5 film ID's:

Table

Account ID
FILM ID 1
FILM ID 2
FILM ID 3
FILM ID 4
FILM ID 5

would it be possible to create a query which would allow the user to type in a film ID once but the system would search each field FILM ID 1 - 5 without the need to typew in the ID five times??

Yours gratefully

Sorry I don't understand what you mean by the question. You should probably post a new question for this anyway.

Mary