Please help: what is wrong with this query?

D

drhassan

New member
Local time
Today, 02:04
Joined
May 24, 2012
Messages
10
I am trying to build a database by MS access 2010 for Animal Import Control Ports. I have three tables names are: Importers, Consignments, Staff, and one table named tbgroup (ID, importer ID, consignment ID, and Staff ID).

The problem is when I relate the primary key (the IDs) of each table with the tbgroup table, and run it in a query it dosen't retrieve any data from those tables.

What is wrong with this query?

This is the SQL Statement for my query:

SELECT tbgroup.[Importer ID], Importers.[Importer Name], Importers.[Phone Number], tbgroup.[Consignment ID], Consignments.[Date of Arrival], Consignments.[Consignment type], Consignments.Gender, Consignments.Qty, tbgroup.[Staff ID], Staff.[Staff Name], Staff.[Job Title], Staff.[Contact Number] FROM Staff INNER JOIN (Importers INNER JOIN (Consignments INNER JOIN tbgroup ON Consignments.[Consignment ID] = tbgroup.[Consignment ID]) ON Importers.[Importer ID] = tbgroup.[Importer ID]) ON Staff.[Staff ID] = tbgroup.[Staff ID];
Click to expand...


Please any idea on how to build a good relationship for this database?

Database is attached for more digestion.

Thanks
 

Attachments

Last edited:
I cannot read a 2007 Database here, but from your description, I am leaning towards a Data related reason for your problems, and that one or more of your Links is broken or not properly defined. Take your Query apart and see where it breaks. Something like the following order would be good:
  1. Select * from tbgroup
    [*]Select * from tbgroup and Consignments
    [*]Select * from tbgroup and Consignments and Importers
    [*]Select * from tbgroup and Consignments and Importers and Staff
Even though the datasets will most likely get smaller, they should not go away. If they go away, you should be able to evaluate the data at that level for your answer.

-- Rookie
 
Hi,

So from what I can tell in the database your trying to show all the information from those four tables in your one query right? Even though all the primary keys are linked to their counter parts in the tbgroup the other three tables aren't related to each other. You need foreign keys in Importers, Consignments, and Staff. So in the Staff table you need foreign keys for Importers and Consignments, in the Consignments you need foreign keys for Importers and Staff etc.

Looking at the tbgroup table I'm not really sure what its purpose is. Could you explain that?

Hope this helps
 
Yes. The main purpose to join in the data from many tables in one query.

Sorry, but how I can add the foreign keys to the tables?

Thanks
 
I apologize to this. I have no idea that the cross posting is unacceptable. My intention was only to find someone who can help me in this problem. Anyway sorry again for this inadvertent mistake.

Thank you
 
Did you actually bother to read the link in #4 ? Your last post does not indicate that. It was not posted for fun but for you education and enlightment. Please read that link before you bury yourself any deeper.

And then update your various crossposts with a notification that you have crossposted and where.

And then I am sure that on one of the threads someone will help you.
 
Hey spike -- why don't you stop bullying people and talk to them like a human? What's the matter --- picked on too much as a kid so you bully people online? D**chbag.
 
You must log in or register to reply here.

Similar threads

S
Using Not In, or wahtever will work
Replies
10
Views
281
Minty
Minty
C
Automated Production Planner
Replies
11
Views
510
GPGeorge
GPGeorge
AOB
Solved Insert records where ID combination not already present (JOIN expression not supported)
Replies
2
Views
236
arnelgp
arnelgp
R
Solved Need help optimizing my query after SQL Server migration
2 3
Replies
40
Views
1,605
cheekybuddha
cheekybuddha
K
How to remove duplicate values when multiple tables are joined
Replies
18
Views
1,203
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom