Need help w/ a very simple query (Newbie)

[Jacob Burnetski]

I am trying to get what should be a relatively simple query to work,
but have had limited (read: no) success with it in 2 days. The layout
may seem complicated, and perhaps it is for such a neophyte, but I
think it's how it needs to be done.

For the specifics, I have 3 tables: Service Order, Service Data, and
Problem Codes.

In the Service Order table I store the Service Order Number and some
simple data about the call. In the Service Data table I enter the
codes for the problems. I needed to do this in order to work it into
the Service Order form as a sub-form so that I could enter multiple
codes. And finally, the Problem Codes table contains a column of the
codes (also the primary key) and a description of each problem.

I am trying to run a query that will give me the service order number
and the problem description, not code. So far I can only get the
Service Order Number and the Problem Description is blank. The SQL
statement for the query is:

-----------------

SELECT [Service Order].[Service Order Number], [Problem
Codes].[Problem Description]

FROM [Service Order] INNER JOIN ([Problem Codes] RIGHT JOIN [Service
Data] ON [Problem Codes].[Problem Code] = [Service Data].[Problem
Code]) ON [Service Order].[Service Order Number] = [Service
Data].[Service Order Number];

--------------

The Service Data table also contains a column for the Service Order
Number which is related back to the Service Order table as a
One-to-Many. The Problem Code field of the Service Data table is
related to the Problem Code column of the Problem Codes table as a
One-to-Many as well.

Can anyone tell me why I'm coming up empty, please?

 

[tina]

you have a many-to-many relationship between Service Order
and Problem Codes, which you're expressing with the
linking table Service Data. Service Data is the child
(many) table to each parent (one) table - Service Order
and Problem Codes. this is all correct. but your query has
directly linked Service Order and Problem codes; that's
not going to work because it's a many-to-many
relationship. you need to use the linking table. try the
following:

SELECT [Service Data].[Service Order Number], [Problem
Codes].[Problem Description]
FROM [Service Data] LEFT JOIN [Problem Codes] ON [Service
Data].[Problem Code] = [Problem Codes].[Problem Code];

some general tips: usually table names are plural, while
the field names are singular. don't use spaces or non-
alpha characters in table/field names, makes writing code
easier later. use prefixes for object names to indicate
class, such as tbl for tables, qry for queries, etc.
again, makes it easier to write - and read - code later.
and if you've already built too much to want to make any
changes to your db, no biggie - just some things to keep
in mind for "next time".

-----Original Message-----
I am trying to get what should be a relatively simple query to work,
but have had limited (read: no) success with it in 2 days. The layout
may seem complicated, and perhaps it is for such a neophyte, but I
think it's how it needs to be done.

For the specifics, I have 3 tables: Service Order, Service Data, and
Problem Codes.

In the Service Order table I store the Service Order Number and some
simple data about the call. In the Service Data table I enter the
codes for the problems. I needed to do this in order to work it into
the Service Order form as a sub-form so that I could enter multiple
codes. And finally, the Problem Codes table contains a column of the
codes (also the primary key) and a description of each problem.

I am trying to run a query that will give me the service order number
and the problem description, not code. So far I can only get the
Service Order Number and the Problem Description is blank. The SQL
statement for the query is:

-----------------

SELECT [Service Order].[Service Order Number], [Problem
Codes].[Problem Description]

FROM [Service Order] INNER JOIN ([Problem Codes] RIGHT JOIN [Service
Data] ON [Problem Codes].[Problem Code] = [Service Data]. [Problem
Code]) ON [Service Order].[Service Order Number] = [Service
Data].[Service Order Number];

--------------

The Service Data table also contains a column for the Service Order
Number which is related back to the Service Order table as a
One-to-Many. The Problem Code field of the Service Data table is
related to the Problem Code column of the Problem Codes table as a
One-to-Many as well.

Can anyone tell me why I'm coming up empty, please?

.

 

[Jacob Burnetski]

you have a many-to-many relationship between Service Order
and Problem Codes, which you're expressing with the
linking table Service Data. Service Data is the child
(many) table to each parent (one) table - Service Order
and Problem Codes. this is all correct. but your query has
directly linked Service Order and Problem codes; that's
not going to work because it's a many-to-many
relationship. you need to use the linking table. try the
following:

SELECT [Service Data].[Service Order Number], [Problem
Codes].[Problem Description]
FROM [Service Data] LEFT JOIN [Problem Codes] ON [Service
Data].[Problem Code] = [Problem Codes].[Problem Code];

some general tips: usually table names are plural, while
the field names are singular. don't use spaces or non-
alpha characters in table/field names, makes writing code
easier later. use prefixes for object names to indicate
class, such as tbl for tables, qry for queries, etc.
again, makes it easier to write - and read - code later.
and if you've already built too much to want to make any
changes to your db, no biggie - just some things to keep
in mind for "next time".

Tina -

Thanks very much for the insight and tips.. I'll keep them in mind
since I am still in the very early stages of setting this up. I
copied your SQL statement to the query, however I'm still getting
basically the same result. Now when I run the query I'm prompted for
a Service Order Number, and if I enter one I get the same result -
three rows of the service order number and 3 blank rows of Problem
Description. Incidentally there are only 3 problem codes in that
record so the 3 rows makes sense.

Could I have a relationship set up wrong, or a field type incorrect?
It seems like it should be pretty straightforward but I'm having no
success at all!

Thanks again very much.

 

[tina]

hmmm, i modeled your setup in Access2000 to create the SQL
code, and it worked fine. i'm going to email my model to
you; if you look at the table relationships and the query,
maybe you can spot the problem in your setup.
if you're not using a valid email address, but would still
like to see my model, you can email me and i'll send it to
you wherever.