query for printing

  • Thread starter Thread starter RogerTE
  • Start date Start date
R

RogerTE

We have a program, written in Delphi, that is used to print out task lists.
We now have to use Access (company new standard) and have imported the data
into tables, but don't know how to get the listings

Table 1:
Name
Main Job Code - links to Table2.Job Code
Sub Job Code - usually null, but if not links to Table2.Job Code

e.g.

Billy Bloggs, CC, null
John Doe, CC,SO

Table2
Job Code
Line display No
text line no - links to Table3.Text Line No

e.g.
CC,1, 4
CC,2,6
SO,1,102
SO,2,103

Table3
Text Line No
Text To Dsiplay

e.g.
1, text for Line 1
2, text for Line 2
3, text for Line 3
etc.

Basically most people have a single Job Code and that is no problem. If
however the person also has a sub job then we are having problems displaying
it
e.g.
Billy Bloggs should display
text for Line 4
text for Line 6

and it does ok

However
John Doe should display
text for Line 4
text for Line 6
text for Line 102
text for Line 103

Can anyone help as to how to achieve this

Many thanks
 
The problem is with Table 1. It is not properly normalize in that it has both
a Main Job Code and Sub Job Code fields. Dare I ask if there is something
like a Sub Sub Job Code field or ever could be one?

What you should have is another table with Name, Job Code, Job Code Type
fields. This would be a bridging or linking table as you have a Many-to-Many
relationship. The data would look like:
John Doe, CC, Main
John Doe, SO, Sub

Then you could link this table to Table2.

What if you can't make the above happen? There is a solution but it's slow
and sloppy. You nee to create two queries and join them with a UNION clause.
It would look something like below. Make sure that the table and field names
are correct. Your example misspells "display". ;-)

SELECT Table1.Name,
Table1.[Main Job Code] as Job_Code,
Table3.[Text To Dsiplay]
FROM (Table1 INNER JOIN Table2
ON Table1.[Main Job Code] = Table2.[Job Code])
INNER JOIN Table3
ON Table2.[text line no] = Table3.[Text Line No]
UNION
SELECT Table1.Name,
Table1.[Sub Job Code],
Table3.[Text To Dsiplay]
FROM (Table1 INNER JOIN Table2
ON Table1.[Sub Job Code] = Table2.[Job Code])
INNER JOIN Table3
ON Table2.[text line no] = Table3.[Text Line No]
ORDER BY 1, 2, 3;
 
Back
Top