Filenames as fields. (1 Viewer)

Hi,

I learned how to spell "access" earlier this week. That's how much I know about Access.

I'm pretty fluent on Word and Excel inasmuch that I cannot program VBA, but I know where to look to get the information I need online and I can hunt and peck until I find the formulas I need to get things done. With Access, I'm a complete newbie.

I want to basically make a spreadsheet at this point cause I think everything will be pretty static once I enter it. I want to use Access though cause Access allows for better interface when entering many fields of information, and viewing many fields of information.

Ok, I want to make a table (I know that much). And I want the first field in the table to be the file name. These are recordings, about 4000 of them, so I want an automated way to do this. But I know jack squat about how to do. If I were making a regular table where I enter the information from the keyboard...that I can do. This automated stuff I have no idea.

I've included a screenshot of the directory from where I'll be pulling the filenames. Of note the filenames in the directory will not change, so I do not need the database to monitor the directory in case filenames change. If that makes it easier, then good.

As a side-request if there's an easy way for the filename "mmddyy-hhmmss" (see screenshot) to also be split into two parts (fields 2 and 3) of the same record for date and time of recording, that's be great. And it'd give me an example of formulas within Access too.

Thanks.

PS : I saw another thread on this same forum that spoke against having calculated fields in the same table as entered data. So, maybe I want to have two tables, which is cool with me as long as I'm able to read from them when I pull up a record to look at.

akedm said:

I want to basically make a spreadsheet

Click to expand...

If you want a spreadsheet, I recommend Excel. You will just be hobbling Access if you come in to it with a spreadsheet mentality, and Excel is very excellent at working with spreadsheets.

akedm said:

I want to use Access though cause Access allows for better interface when entering many fields of information, and viewing many fields of information.

Click to expand...

In what way? Not that I disagree, but you really need to understand why you're making the move yourself. Many people find the jump from Excel to Access to make data entry and viewing many fields even more difficult.

akedm said:

Ok, I want to make a table (I know that much). And I want the first field in the table to be the file name. These are recordings, about 4000 of them, so I want an automated way to do this. But I know jack squat about how to do. If I were making a regular table where I enter the information from the keyboard...that I can do. This automated stuff I have no idea.

I've included a screenshot of the directory from where I'll be pulling the filenames. Of note the filenames in the directory will not change, so I do not need the database to monitor the directory in case filenames change. If that makes it easier, then good.

Click to expand...

Before you create your first table, you should probably read up on data normalization. If you create an un-normalized database, you might as well use Excel (as mentioned above).

akedm said:

As a side-request if there's an easy way for the filename "mmddyy-hhmmss" (see screenshot) to also be split into two parts (fields 2 and 3) of the same record for date and time of recording, that's be great. And it'd give me an example of formulas within Access too.

Click to expand...

I'm very weak when it comes to date/time formats, but, the presentation of the data is somewhat different from the storage of the data. You can (with Excel and Access) store the original data in one field (in a table in Access) and display it in many different fields in different formats.

akedm said:

PS : I saw another thread on this same forum that spoke against having calculated fields in the same table as entered data. So, maybe I want to have two tables, which is cool with me as long as I'm able to read from them when I pull up a record to look at.

Click to expand...

You don't need to put calculated fields into a table at all, unless the calculation is part of a milestone of some kind. Tables are used for storing data...forms and reports are used for presenting data. Do your calculations there.

See my note above about normalization before deciding on 1, 2, or a thousand tables...it'll save you a ton of grief down the road.

The fucntion found at that link will put the full path name into the field of a table.

http://allenbrowne.com/ser-59.html

Choose the List Files to a Table.

So will you have entries in each record like c:\MyFolder\Filename.extension

You have functions in Access (Excel would be similar) for Right, Left and Mid for extracting parts of a string. Also involved are InStr and InStrRev. These fucntions are used to create a field in a query and the created field will display what you want extracted such as Filename.extension.

A major difference between an Access table and and Excel spreadsheet is an Access table is "dead". It simply stores raw data.

A Select Query looks like a table when it opens. It has criteria that determines what records are selected and it usually has "calculated fields" added. Calculated fields would be the ones that split the file name for you.

With your file names you can also have it so with a "click" the file will be opened. But that would be when you had a form or forms made.

As George suggested a couple of time you need to get at the very least a basic understanding of Normalisation.

Access is all about tables that store related records. For example, one table has the records and details of a customer. Another table has the details for each invoice for that customer. Another table might have the details of file notes for each customer or appointments etc. And it coild go further down the line. Each appointment might generate phone calls other activities and they would be in another table and related to the appointments table.

Thanks for your responses. All things being equal, I'd much rather use Excel. But my problem I tried to describe about an easier interface in Access stemmed from an image I had of data entry into Access in a form format. Forgive me....here it goes...

Field 1 : ____________________________________
Field 2 : ____________________________________

Field 3 : ___________ Field 4 : ____________

Field 5 : ____________________________________
____________________________________
____________________________________
____________________________________

I hope this makes some sense. Doesnt Access allow the setup of field input into a format such as above, where field 5, for example, can be auto-word-wrapped for multiple lines. Cause field 5 may be large it'd be much easier to enter it with this type of format, and much easier to read . Maybe I'm thinking of Microsoft Works database. Well at any rate it'd be much easier in the above format than strait record/down field/across entry.

I'll read up about normalization and the other stuff. Thanks again.

Yes, Access forms can be set up that way. So can Excel forms.

Your drawing is what a form does. Basically, a form will let you place the fields wherever your heart desires. A form can also display records in a tabular format, kind of table like.

Forms are not just important for display but the form is where Macros or VBA are run from when clicking on something or because of conditions etc. Thus a Macro or VBA might run because the value of a field is >10 or whatever.

As to Access and Excel, you can do a lot more in Access but some things are easier in Excel. In the early stages the big difference between Access and Excel/Word is you need to know a lot more about Access before you can get going.

Others may disagree with me but I think it would be extremely difficult to learn Access if your preference is to use Excel and doubly so if Excel can do what you want. You also have forms in Excel.

But you can combine Access and Excel but that will usually require and extensive knowledge of Access

Alright then, I'll use Excel to get done what I need done, and I'll repost a question about forms over on that thread. Thanks for helping out .