Having a brainfart - Simple, but large query help?

[Noozer]

I'm having problem getting my head around what should be a simple, but long
query. I don't suppose I could get some help, could I?

My tables:

Group
GroupID - autonumber
GrpDesc - text

Category
CatID - autonumber
CatDesc - text
GrpIDLink - number (link to Group.GroupID)

Issue
IssueID - autonumber
IssDesc - text
CatIDLink - number (link to Category.CatID)

Call
CallID - autonumber
IP - text
Submitted - date/time

Entry
CallIDLink - number (link to Call.CallID)
IssIDLink - number (link to Issue.IssID)
Value - Yes/No
Data - text

I would like the results of my query to be:

Submitted, IP, GrpDesc, CatDesc, IssDesc, Data

Any help is definately appreciated!

 

[LeAnne]

Hi Noozer,

It would help to know how your entities are related, since we don't know
anything about the information being tracked. Can each Group have one OR
MORE Categories? Does each category belong to ONE AND ONLY ONE Group?
Does each category involve one OR MORE Issues? Does each Issue belong to
ONE AND ONLY ONE category? It appears that Issues and Calls are related
many:many (each Issue can require one OR MORE Calls, and each Call can
address one OR MORE Issues), and that Entry is the resolver table. Is
any of this correct?

LeAnne

 

[Noozer]

My bad...

I've updated the table descriptions below... Basically the xxxLink field is
Many to one ID field.

Thx!

 

[Noozer]

Renamed the info here to match tables (so much for posting from memory)...
My feeble attempt at creating queries located at bottom of posting...

I'm having problem getting my head around what should be a simple, but long
query. I don't suppose I could get some help, could I?

My tables:

Groups
GroupID - autonumber
GrpDesc - text

Categories
CatID - autonumber
CatDesc - text
GrpIDLink - number (link to Group.GroupID - Many Categories to one Groups)

Issues
IssueID - autonumber
IssDesc - text
CatIDLink - number (link to Category.CatID - Many Issues to one Categories)

Calls
CallID - autonumber
IP - text
Submitted - date/time

Entries
CallIDLink - number (link to Call.CallID - Many Entries to one Calls)
IssIDLink - number (link to Issue.IssID - Many Entries to one Issues)
Entry- text
EntryText - Yes/No

I would like the results of my query to be:

Submitted, IP, GrpDesc, CatDesc, IssDesc, Data

FWIW, these are the two NON-Working queries that I came up with... Not sure
which is closer to correct (probably the second)...

#1 ---

SELECT E.GrpDesc, D.CatDesc, C.IssDesc, B.EntryText, A.Submitted, A.IP
FROM (SELECT Submitted, IP, CallID FROM Calls) AS A,
(SELECT EntryText, IssIDLink FROM Entries WHERE A.CallID = CallIDLink) AS
B,
(SELECT IssDesc, CatIDLink FROM Issues WHERE B.IssIDLink = IssueID) AS C,
(SELECT CatDesc, GrpIDLink FROM Categories WHERE C.CatIDLink = CatID) AS D,
(SELECT GrpDesc FROM Groups WHERE D.GrpIDLink = GroupID) AS E
ORDER BY A.Submitted, A.IP, E.GrpDesc, D.CatDesc, C.IssDesc;

#2 ---

SELECT E.GrpDesc, D.CatDesc, C.IssDesc, B.EntryText, A.Submitted, A.IP FROM
(SELECT Submitted, IP, CallID FROM Calls,
(SELECT EntryText, IssIDLink FROM Entries,
(SELECT IssDesc, CatIDLink FROM Issues,
(SELECT CatDesc, GrpIDLink FROM Categories
(SELECT GrpDesc FROM Groups WHERE D.GrpIDLink = GroupID) AS E
WHERE C.CatIDLink = CatID) AS D
WHERE B.IssIDLink = IssueID) AS C
WHERE A.CallID = CallIDLink) AS B)
AS A
ORDER BY A.Submitted, A.IP, E.GrpDesc, D.CatDesc, C.IssDesc;

 

[Marshall Barton]

I'm having problem getting my head around what should be a simple, but long
query. I don't suppose I could get some help, could I?

My tables:

Group
GroupID - autonumber
GrpDesc - text

Category
CatID - autonumber
CatDesc - text
GrpIDLink - number (link to Group.GroupID)

Issue
IssueID - autonumber
IssDesc - text
CatIDLink - number (link to Category.CatID)

Call
CallID - autonumber
IP - text
Submitted - date/time

Entry
CallIDLink - number (link to Call.CallID)
IssIDLink - number (link to Issue.IssID)
Value - Yes/No
Data - text

I would like the results of my query to be:

Submitted, IP, GrpDesc, CatDesc, IssDesc, Data

It should be like this (except for possible name conflict by
use of the reserved word Group).

Air code, not sure about the parenthesis:

SELECT Call.Submitted, Call.IP,
[Group].GrpDesc,
Category.CatDesc,
Issue.IssDesc,
Entry.Data
FROM (Entry INNER JOIN Call
ON Entry,CallIDLink = Call.CallID)
INNER JOIN (Issue
ON Entry.IssIDLink = Issue.IssueID
INNER JOIN (Category
ON Issue.CatIDLink = Category.CatID
INNER JOIN Group
ON Category.CatID = Group.GrpIDLink)))

It would be easier to do this in the Query Design window.
Just Add all the tables and make sure the connecting lines
are between the appropriate fields and tables. Then drag
the desired fields down to the query's field list.

 

[Noozer]

It should be like this (except for possible name conflict by

use of the reserved word Group).

Air code, not sure about the parenthesis:

SELECT Call.Submitted, Call.IP,
[Group].GrpDesc,
Category.CatDesc,
Issue.IssDesc,
Entry.Data
FROM (Entry INNER JOIN Call
ON Entry,CallIDLink = Call.CallID)
INNER JOIN (Issue
ON Entry.IssIDLink = Issue.IssueID
INNER JOIN (Category
ON Issue.CatIDLink = Category.CatID
INNER JOIN Group
ON Category.CatID = Group.GrpIDLink)))

It would be easier to do this in the Query Design window.
Just Add all the tables and make sure the connecting lines
are between the appropriate fields and tables. Then drag
the desired fields down to the query's field list.

Doh! I've never had any luck with the Query Design window, but I admit that
it did the job this time.

Guess I just need to get a bit more practice instead of trying to do it by
hand.

Thanks!