help please!

  • Thread starter Thread starter nessa
  • Start date Start date
N

nessa

Guest
i am in serious trouble. i've been working on this assignment for like, 12 hours now, and it still won't work :(
i have three tables already done in acces:
1)Author:
AUTHOR_Author Name (Primary Key) (txt)
AUTHOR_Gender
AUTHOR_University
AUTHOR_Faculty
AUTHOR_Department

2)Book List:
LIST_ID (primary key) (txt ISBN nbr)
LIST_Book Title
LIST_Book Author
LIST_Cost
LIST_Unit of Books
LIST_Publisher
LIST_Year of Publication
PUB_Publisher's Name (foreign Key)
AUTHOR_Author Name (foreign Key)

3)Publisher:
PUB_Publisher's Name (primary key) (txt)
PUB_Address
PUB_State
PUB_Country
PUB_ZIP
PUB_Phone Number
PUB_Website

i created a relationship using the look up wizard and in the relationship window i have a

Publisher (1)-(infinity)Book List(infinity)-(1)Author

now when i go to run queries which involve just one table it works

but as soon as i run queries with multiple tables, the query turns out blank.


i really need help!! sorry its so long winded, and thank you very very much if anyone can be of any help
 
Hi Nessa -

The blank query means that you are either specifying some condition for which there are no records, or that the relationship among the tables is not correct.

1. Go to Tools / Relationships and look at the relationships table. Verify that the relationships between tables are on the correct fields. E.g. that the primary keys are matched to the correct foreign keys.

2. Try to build in simple steps. For example, build a query that lists all the books. Then try to make a query that lists all the books where the author's name is "Smith" (or something that you know will work).

3. If you are still stuck, try posting a jpg of your query in design view.

Hang in there!

- g
 
i have tried those things.. and when a query with just one table it works, its a mutliple table query that won't work.
my assignment is here, i think there may be something wrong with my relationship, but i can't figure it out

is there a way to show my relationship window here? if not its attached to this message.

thank you so much for helping me out, you have no idea how fustrating this is :P
 

Attachments

Hi Nessa -

There are a couple of things going on, but the biggest is your use of primary keys / foreign keys.

Normally the primary key is a number (integer) that refers to a unique record. The primary key is the record identifier in the original table. E.g.

tblAutos (a table about Automobiles)
AutoID, primary key, autonumber
AutoName, text
ColorID, foreign key, number a reference to the color table

tblColors (a table about Colors)
ColorID, primary key, autonumber,
ColorName, text

So here we refer to each auto by a unique number (AutoID), and each record about the auto has a reference to the cars color (ColorID). It may look a little confusing to use ColorID in two different tables (and it means different things in each case), but it actually works well once you understand it.

The relationship would be established between the ColorID in tblColors to the ColorID in tblAutos. This is a 1-to-many relationship (each unique color can have many corresponding auto records).

Now, your tables....

First of all you have used a text field for the primary keys. While this can work, I strongly encourage you to use autonumber type.

You also need to match the type of fields between primary and foreign keys. For example, you can't set the primary key to autonumber and then refer to it as a foreign key of type text. (Note: you *don't* declare the type of the foreign key to autonumber, just "number")

Finally, your foreign key fields in the "Books" table are all blank. There is no data entered here which is why your query comes up blanks.

So,
1. I encourage you to start over (sorry) with your tables. Declare one primary key for each table, as type autonumber.
2. Make your foreign keys of type number
3. Make sure you enter the correct data in the foreign key column of your table.

hope that helps!

- gromit
 

Users who are viewing this thread

Back
Top Bottom