X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Charts Showing Numerical Values From Lookup, Access 2016    
post Jun 17 2019, 04:38 AM

Posts: 45
Joined: 28-October 18

Hi All,

I have a combo chart linked to a query.

The query returns totals of products delivered by driver.

In the table, DriverName is a lookup field to DriverTbl which contains Drivertbl.[Id] and DriverTbl.[Driver]

The bound column is set to 1, columns shown = 2, and column widths as "0cm, 2.54cm" (this shows me only the name and not the ID field next to it)

The chart that is running off of this query, is showing the Drivers as DriverTbl.[id] and not DriverTbl.[Driver]

How do i get the query to return Driver names on the chart?

The Transformed Row Source (The charts data row source):
SELECT [DriverName], Sum([TotalSlabs]) AS [SumOfTotalSlabs] FROM [SlabsByDriver] GROUP BY [DriverName] ORDER BY [DriverName]

The query in SQL view::
SELECT WeeklyTransportReport.DriverName, WeeklyTransportReport.TotalSlabs
FROM WeeklyTransportReport
GROUP BY WeeklyTransportReport.DriverName, WeeklyTransportReport.TotalSlabs
ORDER BY WeeklyTransportReport.DriverName;

Keep in mind that SELECT WeeklyTransportReport.DriverName is selecting a lookup field structered as DriverTbl.[ID] + DriverTbl.[Driver]

How do i get the chart to pull the driver names from the lookup rather than the ID fields?

Please can someone help?

Many Thanks

Go to the top of the page
post Jun 17 2019, 04:49 AM

Posts: 45
Joined: 28-October 18

After i wrote out the explanation it kind of made sense to me!

Because the tables are related i used:

SELECT DriverName.Driver, WeeklyTransportReport.TotalSlabs
FROM DriverName INNER JOIN WeeklyTransportReport ON DriverName.ID = WeeklyTransportReport.DriverName
GROUP BY DriverName.Driver, WeeklyTransportReport.TotalSlabs
ORDER BY DriverName.Driver;

So glad that access does the SQL language automatically!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th July 2019 - 03:45 PM