Trying to create trendline using multiple tables

  • Thread starter Thread starter merksh
  • Start date Start date
M

merksh

Access 2003: I have 4 tables...they all have the exact same headings and I
am trying to compare all 4 tables to create a trendline of the values for a
particular field...for example:

Table: Field: Value:
April Item 12345 32%
May Item 12345 38%
June Item 12345 20%
July Item 12345 40%

How can I display a trendline for item 12345 for each month? If not a
trendline, how can I compare each table to display only the items that appear
on each table?
 
why do you have 4 tables with the same headings what is the difference
in each table and how are they linked.

Regards
Kelvan
 
Each table represents a separate month. I run a report in Excel every month
and now I am trying to create a trendline in Access to show the items that
show up each month consistently...for example, item 12345 is on April's
report and then again in May, June, etc...the goal is to see which items are
duplicated each month. I created a "relationship" between the tables to link
them...is there a better/easier way than what I am doing? Your help is
greatly appreciated!

Thanks!
 
ok this will make it technically infinate because you have a different
table for each month

you should make 1 table with a field to note which month you are
looking at

because you will create a 5th table then a 6th table and so on and so
on your query will have to change every month to the point it will get
too big to process.

if you want to do it with yo-ur incorect table structure you have to
use

select *,"jan" as themonth
from jantable
union all
select *,"feb" as themonth
from febtable
union all
select *,"mar" as themonth
from martable
select *,"apr" as themonth
from aprtable
etc
union all
etc
union all
etc

then run a query

on the above

select *
from theabovequery
having count(itemnum) > 1

and that will get you all the duplicates

of course as i said you will haev to change the first query every
month
IF you built your tables correct or do some modification and use a
correct system you can do this

tblcorrecttalbe
correcttableid
correcttabledate
itemnum
otherfields

as you can see there is a date field in there this will allow you to
filted dates by month ie

select * from correcttable
where format(correcttabledate, "mmm") = "jan"

this will give you all jans records and so on.

if you want to migrate your data to a structurally sound data
structure ask and i will help you otherwise you have the queries on
how to do it above

hope this helps

Regards
Kelvan
 
Thank you so much for the information. I would much rather work with one
table. I am going to attempt to merge all my data into one table to see if I
can use the correcttable information. Once I get this completed, I will
respond back to see how I need to move forward...thanks again!
 
Back
Top