Using SQL to sort data

I have an mdb file that has two feilds in a table.  Invoice Summary, and Invoice Date.  I would like to be able to sort through the data and return a series of tables to be used for another purpose.  I need to be able to have a table with monthly data, quarterly data, and complete fiscal data.  I also need the same tables with the same parameters, except with prior year data instead of current year.  Any help would be greatly appreciated!  
rporter45Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
As long as the date columns are legitimate datetime constructs, the the graph can do the necessary summations etc , or, can even create formula fields within the report to be the periods you need. Best to stay with date constructs because it will sort / display properly.

0
 
frankyteeCommented:
do you want to create a new table or do you want to run queries on this table to identify the fiscal data periods?
0
 
rporter45Author Commented:
I want new tables.  My end result will be using these tables in Crystal Reports to summarize the data by the period specified and graph it.
0
 
CMYScottCommented:
You should be able to create the queries right in Crystal Reports - whenever a user runs the report, they would be prompted for the dates you need for the various query conditions - then it would use its own data engine to query the database.

Unless I misunderstand what you want to do - this approach would be better - as it eliminates creating and then at some point deleting tables from your Access MDB - something which quickly leads to file growth - which even in the latest versions of Access increases your chances of data corruption.

Just my two cents :)

Good Luck
0
 
rporter45Author Commented:
My problem is that I don't really know how to take that data and then use it in the mixed graph I need to create from it....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.