Excel Data Base?

[REDX]

I've been working with access for about 7 years now, I migrated from the old LOTUS days when you could make a pretty fair data base from a spread sheet, I really miss that.

My question is:

Is there anyway to turn my excel spreadsheet into a one stop shop? I'm tired of having to manipulate formula's thru excel and then import to access to use the data base functions.

I would love to be able to bring up a form that would querry my spreadsheet. The current form option is very limited and will not except my rather larger spreadsheet (37 columns).

 

[just_jon]

Welcome to MrExcel - you may soon be in luck!

See this topic.

 

[gingerafro]

Welcome to the board.

It depends on what sort of summary you need, whether there are calculations required, and how complicated the query needs to be.

I think these will be a bit basic for you if you have access experience but I would suggest:

1. Using autofilter for information only queries
2. A pivot table if calculations are needed
3. Using the D formulas.

Post back if you are not sure about any of these suggestions or why they wouldn't work.

 

[REDX]

Thanks Just_Jon, office 12 sounds great so far.


gingerafro, I've never heard of "D" formulas and tried pivot tables years ago without success.

It would be nice to view my records on on form...I have a spreadsheet that is 37 cloumns wide and 7K deep.

Do I need to learn VBA to make this form? The only VBA I've ever played with is from recorded macro's that I manipulate.

If VBA is needed is there a crash course to get there?

 

[just_jon]

Thanks Just_Jon, office 12 sounds great so far.


gingerafro, I've never heard of "D" formulas and tried pivot tables years ago without success.

It would be nice to view my records on on form...I have a spreadsheet that is 37 cloumns wide and 7K deep.

Do I need to learn VBA to make this form? The only VBA I've ever played with is from recorded macro's that I manipulate.

If VBA is needed is there a crash course to get there?

That's a lotta data for formulas ...

Give a bit more detail about your layout, the type data you have, the formulas you need regularly, etc.

 

[REDX]

OK, here goes...

I have an old main frame that gets down loaded every night to a txt file, I convert this to an excel spread sheet because the dates come over as text, I have nine date columns...I've tried to import directly to access but never found out a way to make the dates work. (tried the "Key Strokes" route in a macro)

More detail on the date problem:

Txt files show a date as text of 20050101 which has to be converted to a date of 1 JAN 2005 in an excell spreadsheet, the way I do this is to add columns to the TXT file before it comes over and separate 2005 from the 01 and 01. Then add a column to my spread sheet and do a date formula "=IF(O2=0,"",DATE(O2,P2,Q2))", this aligns the date back together in a date format and then I delete the txt column.

It's a fairly straight forward spreadsheet that depends on dates primarily, I manage support equipment and DUE DATES are crucial.

It would just be simpler to stay with excel and manipulate my data through a form? Sometimes access can be figity, plus viewing a record or row would easier on a form.

 

[Norie]

Are you only using Excel to convert the 'text' dates to 'real' dates?

If you are then why not just use Access.

You could easily change those dates by going to Advanced... in the Text Import Wizard.

Also if you are doing this import on a regular basis then you could create an Import Specification for it, you could even automate the import with simple TransferText code.

In Access you could easily create a form with the data as a record source.

In Excel you could create a form to display the data but you would have to code any navigation/delete/add/update buttons you wanted, that functionality is standard on an Access form.

 

[lenze]

No need to go to Access. Once the data is in Excel, you can do all you need. Besides the DFunctions, Sumproduct, and Pivot Tables, you can also use MS Query within a WorkBook. See Tusharm's Web site for tutorial http://www.tushar-mehta.com/

HTH

lenze

 

[REDX]

Hi Norie, thanks for the help.

I've tried the advanced import directly from the TXT file in the past and it wipes out the dates when it imports, just tried it again with the same results.

Access just doesn't recognize the format (200501010) to convert to a date?

 

[Norie]

What options did you try to use for the date?

I just tried importing a small text file with dates in the format you indicated and it worked fine.

In Advanced... I set the following options.

Delimiter - nothing, ie I deleted the standard /.

Date Order - YMD (This might actually be YDM for you, it isn't clear from your example).

I also set the field type for the date field to Date/Time.

By the way, you do realise you could use almost exactly the same formulas in Access to create a real date?