Breaking a linked excel table into multiple tables

[mslenker]

So what I have is an excel document the lists all the purchased product. I have this excel document linked to an access table and would like to break it down from there to see the data by months. It doesn't matter if it breaks it down into tables or queries.

Invoice DateInvoice QtyItem CodeDescription 1 Unit Price Extended Price Invoice #Acct-Dept #Dept # 04/25/201211UNS-DEF68301OFF 8-1/2X11 CLEAR LANDSCAPE SIGN HOLDER$6.50$71.5030298550112-64990112 04/25/20121FEN-KBTBLPYUSBBOFF LARGE PRINT KEYBOARD W/ JUMBO TRKBAL$129.99$129.9930299130116-64990116 03/29/2012-24MCR-9676LSAF ULTRATECH DYNEEMA GLOVE73.44-146.8830272880116-66540116 03/29/2012-24MCR-9676XLSAF ULTRATECH GLOVE XL73.44-146.8830272880116-66540116 03/29/2012-36MCR-9677LSAF ULTRATECH DYNEMA 13GA71.40-214.2030272880116-66540116 04/03/201224AHC-222930022LABE DIRECTIONAL LABEL FOR CRANE N S E W$4.32$103.6830276910116-66540116 04/09/20126MCR-104SAF CREW RATCHED FACE SHIELD/HELMET12.3073.8030282510116-66540116
Here is sample data, but the data is from the past year. Not to familiar with VBA, but I'm thinking to one have to update the excel and not continuously make queries I would need to use VBA.

thanks, for any help.

 

[Alansidman]

Suggest you upload the database or excel spreadsheet. What you supplied is barely understandable. Where do records begin?end? fields begin? end?

 

[mslenker]

I've uploaded the test excel to show what the data will look like.

 

[krowe]

I would create a pivot table in excel where you can get summary information and drill down to get the fine detail where neccessary.

alternatively in access you can create a linked table to the excel file, and then use queries to seperate the data out into months using a between criteria

 

[mslenker]

The problem with that is this excel document will be getting more data added to it and I won't be able to constantly update the queries.

 

[krowe]

or easiest solution is to apply a filter in the excel table, just tried it in excel 2007 and you get the option to filter by year/month on date columns


re your last post:

maybe link the table to the excel file, create a form with to and from fields, use these fields as the criteria in your query

 

[Alansidman]

would like to break it down from there to see the data by months.

Does this mean you want a summary by months. No detail? Total by Extended Price by month?

 

[Alansidman]

Based upon my last post here is a SQL statement to achieve that:

SELECT Year([Invoice Date]) AS YY, Month([invoice Date]) AS MM, Sum(Sheet1.[Extended Price]) AS [SumOfExtended Price]
FROM Sheet1
GROUP BY Year([Invoice Date]), Month([invoice Date]);

table name is Sheet1 as I just imported the sheet into Access.

 

[mslenker]

Thank you guys for all your help.