Guidance for adding field from additional table to report

English
,
#1

Thanks for humoring a DB newbie. I’m just looking for someone to point me in the right general direction.

I have a working report that is based 100% on a query on a “Clients” table. The table includes a single-character “Route” field with a value of {A:F, Z}. The query selects all of the clients with a particular route value – a small number typically around 10 to 15 – and the report is a summary header and a detail section for each selected Clients row.

I have just created a new table, RouteAlerts – nothing is using it yet so I can trivially change it if indicated – that consists of two columns (plus an ID). The two columns are a single-character route and a 255-character Alert column. FWIW, the alert for Route ‘x’ might be text like “Note Smith changed to daily deliveries.”

Here’s what I want to do. When I generate a report for a given route – in addition to all of the current detail information that is from the Clients query – I want to print the Alert for the corresponding route in the footer. So, for example, when printing the report for Route ‘x’ then “Note Smith changed to daily deliveries” would appear in the footer.

The existing query is SELECT "Route" AS "Route", "Delivery_Order" AS "Order", "First" AS "First", ... FROM "Clients" WHERE "Route" = UPPER ( :Please_Enter_Route_Letter ) ORDER BY "Order" ASC

I know how to add the field to the footer and in fact have done so. What I don’t know – what I am asking here – is how do I get data from this additional RouteAlerts table into a report based on a query on Clients. I am comfortable writing SQL if that is the answer.

Thanks much for your patience,
Charles

#2

Doesn’t work
SELECT “A”.“Alert”, “C”.“Route” AS “Route”, […] FROM “Clients” “C”
JOIN “RouteAlerts” “A” ON “A”.“Route” = “C”.“Route”
WHERE “C”.“ROUTE” = UPPER […]
(?)

If Routes are unique could be pk I think. No need of column ID (?)

#3

I understand index columns conceptually but have no experience. When I made Route the index I could not add any rows. Did not explore the details – just went back and added an ID column.

???

#4

The query
SELECT […]
JOIN […]
doesn’t return what you need?

RouteAlerts (pk) I think could be the routes char.
IF there may be just 1 Alert for route of course.

#5

I have very limited SQL experience. If you think JOIN is the right approach I will give it a shot.

#6

Thanks all. I just wrote my first inner join. Works like a champ.

Charles

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.