Show differences on chart

[Skizzly]

Hello experts,

I have a two tables. The table1e shows the a count of staff on shift every day.

The table2 shows when a person in the first table changes their working pattern.

What i need is some guidance on how to represent the change in working pattern compared to the table1's values... and represent this on a line graph...

i want to show table1's line and see what the impact of table2 changes will have on the numbers in table1...



I have attached sample table with dummy data

[June7]

You need to show 'dummy' chart.

What do the 1's in table2 represent - 1 person changed work pattern? Changed how - added or removed?

These tables are not normalized and I suspect what you want will not be simple because of that.

[Skizzly]

Hi June,

Thanks for the reply.

I was of the thought to use a line graph of the values in table1 and then somehow show the plus or minus change for a worker against the total values in table1... that's the issue i need your expert ideas on....

The ones in the table2 represent a change of a work pattern, the values here i wanted to be added or removed from the over all counts in table1.

Normalized? I am not sure how the table could be normalized... each column in table1 describes a 15 minute period of the day and the number of staff needed to cover that period... how would you suggest it be normalized to be more efficient to achieve my aim?

I have attached a new zip with form in with chart

thanks in advance for any suggestions

[June7]

Normalized table structure:

PatternStart	Period	Quantity
5/1/2015	06:30	25
5/1/2015	06:45	25
5/1/2015	07:00	25
5/1/2016	07:15	25

If both tables have this structure, they can be joined on the two common identifying fields (PatternStart, Period). The 2 Quantity fields can be used in expression to calculate difference.

With current structure, could join tables on the PatternStart fields then would need 98? calculated fields in query if you want to calculate change for each period.

[Skizzly]

Hi june, please see the new zip...

i was wanting to present the data in table1 in the chart and then when a change occurs then the change is represented on the chart from table2

[June7]

Okay, try a UNION query.

Build and save UNION query object. There is no wizard or builder, must type into SQL View of query designer.

SELECT *, "T1" AS Source FROM table1
UNI ON SELECT *, "T2" FROM table2;

Then the graph RowSource would reference the UNION query instead of tables.

If you started with a normalized structure, a CROSSTAB query would be the graph RowSource to get the output you want.

[Skizzly]

Thanks June,

Should i redo the table structure to your suggestion? is it the best way to house this data? The intention is to have several years worth of data stored and repeating the period in the way you suggest, is it better than how i have the table designed?

[June7]

Normalized is usually best but non-normalized can be dealt with, as I have demonstrated.

As I said, with normalized structure, a CROSSTAB would be used but CROSSTABs have their own issues. I've never needed them. http://allenbrowne.com/ser-67.html

IMO, it is a balancing act between normalization and ease of data entry/output.

Understand the alternatives and do what works best for you.

[Skizzly]

Appreciate the time you have taken to assist... see attached...

I have not normalised to your structure but have done the union query... the help i need was to show what the impact of table1 would be on table2 add or subtract and represent it on a line graph... is this possible?

[June7]

As I said, build query that joins your tables and calculate 98? fields in query to show how the value would change. This query can be source for another SELECT line in the UNION. Here you are facing one of the main perils of non-normalized data - difficult calcs.

So far we have 3 queries to get the output you want. Tables and queries have a limit of 255 fields.

Query 1 joins the tables and does calcs
SELECT table1.PatternStartDate, {98 calculated fields}, table1.LAG FROM table2 INNER JOIN table1 ON table2.PatternStartDate = table1.PatternStartDate;

Query 2 is the UNION
SELECT *, "T1" AS Source FROM table1
UNI ON SELECT *, "T2" FROM table2
UNI ON SELECT *, "Q1" FROM query1;

Query 3 is the graph RowSource

[Skizzly]

Thanks June

Will follow your advice and see if i can achieve the output needed.

[Skizzly]

Hi June,

Would the calculated field would be table1.field(00:00) +/- table2.field(00:00)?

[June7]

But you have to pick one operator sign. Do you want to add or subtract? Enter data as a positive or negative and then just use + operator.

table1.[00:00] + table2.[00:00]

[Skizzly]

thanks again for help