Is there a way to only pull recent data from a access database

[dodgeking3171]

Hey Guys,

So heres what im trying to do. Im using Excel to go through a access database to figure out What parts we sold yesterday, how many, and who the supplier is.

Everything worked great. There is some basic excel formulas to find the supplier and to weed out all of the special order stuff that we do not stock

I wanted to make this update automatically. Before i was just going through and copy pasting the records from yesterday and letting excel go through and make my pivot table.

I thought that i could link a access database, then sort by date to only show yesterday, and away it would go. Problem is excel seems to freeze up when i try to do this. I can fix the freezing problem by getting rid of the formula to find the supplier so that it will sort out the date, But the supplier is a huge item needed to make this function the way i want it too

The database is 440,000 records long. So its huge.

My question is can i only pull data from the last day or week from the access database? Im not sure how to supply my spreed sheet as im sure that would be helpfull for anyone trying to lend a hand.

thanks in advanced

 

[Fazza]

hello

yes this can be done, and pretty readily when you know how.

you could put a query in Access that filters the data with a criteria so that only the last day's data is returned, and then query this from Excel

however I'll describe querying from Excel and having a cell in Excel that defines the data age. that way you can enter 1 to get data within the last day or 7 to get data within the last week. There is no programming and no formulas used

from an Excel file query the data via ALT-D-D-N & follow the wizard, returning the fields you want. at the end of the wizard choose the option to edit in MS Query. Via the SQL button add a line to the end of the SQL. Something like,

'before

SELECT list of fields
FROM your data table

'change it to

SELECT list of fields
FROM your data table
WHERE CLng(Now) - CLng(yourtable.[your date field]) < [?]

Enter a value like 1 to return the last day's data. Exit MS Query via the 'open door' icon, load the dataset to a worksheet. Insert a few blank lines above the results. Now select a cell in the results and refresh the query table via ALT-D-R. When there is a prompt for the number of days, don't just input a value but select a worksheet cell & also select the option to auto refresh on change to that cell. Now as the value in that cell is changed - say from 1 to 7 or whatever - the query will refresh. All done.

regards

 

[Fazza]

please Google for examples of similar topic, such as

Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

 

[dodgeking3171]

please Google for examples of similar topic, such as

Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

Hey guys, I appreciate the help so far, With the first example, Im not sure if im doing it correctly, as it tells me it cant define the data if it cant graph it. or something like that. Im sure im doing it wrong

the 2nd example is so close, but I have excel 2010, So i set a cell with *today()-1 * That returns yesterday, then i go into the query and try and set up the criteria, but it always says sytax error in query expression. How do i go about directing the query to the cell in the table?


Thanks for the help i cant believe this is kicking my butt so hard

 

[Fazza]

With the first example, Im not sure if im doing it correctly, as it tells me it cant define the data if it cant graph it. or something like that. Im sure im doing it wrong

Sounds like no problems - you're nearly there. Just OK to acknowledge the message (about not being able to graphically represent etc) and continue.

 

[dodgeking3171]

Sounds like no problems - you're nearly there. Just OK to acknowledge the message (about not being able to graphically represent etc) and continue.

Ok well ive gotten closer shutting off the wizard helps a ton. Problem im having now is matching the date. How to a match approximate? I'm trying to match a date. the dates in the access database look like (1997-01-06 11:32:28) in my excel criteria, its set currently to 2/19/2014. I dont need a per minute match, I need to see all parts sold for the day. Ive tried putting in the criteria like[] But thats giving me a error of *syntax error in date* when i try test the query with a date that i can see in the table. until i add the minutes again.


Any help on what im doing wrong in that one?

 

[dodgeking3171]

Ive also tried date()-1 that returns nothing as well

 

[Fazza]

What happens when you try what I suggested?

 

[Fazza]

PS. to be clear, what I suggested in post #2 of the thread. I expect it'll take only a couple of minutes to set up & it does what you asked. If it is taking any longer then maybe something is not as I expected. We can investigate further if required. regards

 

[dodgeking3171]

actually since I figured out this other way of accomplishing the process i did not continue the process in post #2, this is mainly because when i put in the data i thought it required it gave me a criteria error so that left me at a loss. through the last few months ive learned quite a bit on macros and other excel formulas and built some fairly impressive ( to me ) sheets. But when i learn the macros its because i record something simular to see how the code looks then edit the code as i need. For example, I made one that used the informaion in cell F3 and todays date as the file name, saved the sheet, and printed two copies. For most here thats pry simple but i was proud haha

however there was been no way for me to see the code in this to see what data it requires, also, I do want to tie the criteria to a cell within the sheet. Also not sure how to do that. What i may do is post the SQL on here and if you can give me an example of what i want id appreciate your time.

The problem i think im having right now is im trying to search for a date in a date/time field. Im kind of suprised that the wildcard * doesnt apply to dates or maybe i coded it wrong. Also, i cant use a Contains operator, all the easy things in life haha.

My thought last night when i was driving home if i could set up a "between" argument and go from midnight to midnight and it will then pull all the data between, this could be a possiblity as well


I will post the sql tonight around 5 or 6 central time if you could help me understand that stuff.

I appreciate it much, this is a very nice change from turning wrenches all night!