Comparing Dates from Table

[Walshie]

Afternoon!

I've been searching the forum but can't seem to find what i'm looking for, hopefully someone will be able to help with my problem?

I have a table that contains sales data from the past 24 months, the table is in the following format:

Part number | Description | 21/11/11 | 14/11/11 | 7/11/11 | etc.
T1234 | Timber | 100 | 97 | 99 |
C2536 | Panel | 56 | 28 | 30 |

I want to create a query that will feed into a graph. And display info if the following criteria is met:

Part number = value in txtPart
Date range = between dates in txtSDate and txtEDate

e.g. if part number T1234 is in the txtPart, 20/11/10 is inserted in txtSDate and 20/11/11 is inserted in txtEDate then all the sales figures for T1234 will be plotted on the graph between the two dates.

Is this possible with the dates been in the header of the table? (they are stored in number format) or would I have to revise this, preferably I'd like to keep them as they are

Thanks in advance for your help!

Chris

 

[John Big Booty]

Welcome to the forum.

You have a major problem in that your table structure is not normalised.

Your table structure should look like;
TBL_SalesHdr
TransactionID (PK)
ClientID (FK)
SalesManID (FK)
SalesDate

TBL_SalesDtl
TransactionID (FK)
PartNumber (FK)
Quantity

Once you have normalised your table structure you can then simply use the following as Criteria;

Between [URL="http://www.techonthenet.com/access/functions/date/date.php"]Date()[/URL] And [URL="http://www.techonthenet.com/access/functions/date/dateadd.php"]DateAdd[/URL]("m", -24, Date())

Which will give you all transaction for the last 24 months from Today's date. You could replace Date() with any fixed date by using #26/11/2011#

 

[Walshie]

Perfect! Thanks for your help!

Just a final question, i've got the graph working perfect now and i can compare multiple part numbers on it.

I would like to take this slightly further now, I want the axis of the graph to be in week numbers, then I want to input a part number, when I input the part number I would like the graph to show two lines (Muliple items in the series) stating "2010 sales" and "2011 Sales" and overlap the data so I can have a visual comparison of trends.

I don't need any advice on the refreshing graphs etc. but I'm struggling with the queries to generate the information. All my data is normalised as mentioned above.

Thanks in advance!
Chris

 

[John Big Booty]

I'm not a big user of Graphs in Access. It might be worth starting a new thread dedicated to the subject