Want to begin working in Access - could I do these things with it?

[pandzio]

Hi!

I am details-&-sorting lover artist, but not a programmer (not very good with such thing). I have been making my databases in Excel and OneNote, but to only limited satisfaction. I know a real database would be better, since I store strings of text and images (for example page scans and rich-text-formatted book paragraphs as my main data, and other cells are various keywords and data for sorting and filtering). I know Excel is not design to contain lot of images and "long text" cells. I am considering switching to Access, which I hadn't done in past few years because I couldn't find how to do certain things or it seemed they cannot be done.

Will I be able to:

1) Have my database with images being able to be displayed with all the images visible at the same time? [Just like in Excel. Lets say that 14 rows are visible on a screen, each of them has an image in one column - all of them are displayed. I don't have to click or double click a row to see the image]
I would like that to have upon opening, without the need to generate anything each time, and to be able to edit all the fields in that view (I understand with images it might open some pop-up window etc.). Especially formatting the "long text" fields - to have both all images displayed and the Access-buttons for font, font size etc.

2) Will I be able to have this view (all images in all cells displayed simultaneously) and be able to sort and filter several columns (and still see all the images for the rows that are displayed after this sorting/filtering) (again - as in Excel)

3) Are there any considerations/problems regarding EMBEDDING (not linking) images? [For example Excel bloats spreadsheet size to astronomic size and you have to "paste>cut>pasteSpecial:JPG" instead of a simple "paste" to avoid that]. I usually use a print-screen app to get JPGs to the clipboard and paste in my Excel/OneNote file.

4) I go by the rule to have my columns to be the smallest chunks of data possible, because it is easier to merge data than to divide in the future.
I have year, month, day, hour, minute, second, timezone name, timezone +/- prefix, timezone counter of hours difference from GMT0 - all in separate colums, instead of in 2 or 3. This is also for avoiding Excel to add zeros for minutes when that field should be empty ("no data") nor zero, etc.
I've read same is advised for Access.

5) Can I control the size of cells with images and long text?
5A) Is it possible (again in the mode where I see all fields and can edit every one of them I want without exiting that view mode) to have all fields set to wrap text to display all of it (nothing hidden in the view even if text is very long) with all the text-formatting of them visible?
5B) How it goes with images? Is there a way to stretch the column and have all the images stretch with it. Do they fit to width and make the row as high to have these images visible wholly, or does it adjust to stretch-fit the longer side? Or do I have to drag each image by the corner to stretch it?

6) Could I have an alternating type of field - the same column sometimes is an image and sometimes is a long text? I guess not, but I'd like to know.


If someone can further me to some relevant guides it also would be nice. I was not able to find clear answers (perhaps I don't know how to phrase my questions in Google).

Thanks

 

[MarkK]

A database is useful if you need to model complex systems of related objects. The rules that govern those relationships are called principles of "database normalization." Before doing anything in a database I recommend you google that phrase, or search this site, and do some reading. If you don't understand database normalization you should stick with Excel.

 

[pandzio]

A database is useful if you need to model complex systems of related objects. [...] If you don't understand database normalization you should stick with Excel.

Hi.
From tutorials I watched, I seem to get how NORMALISATION works. I can see myself using and appreciating it.

I have this set of nagging questions, and I impatiently wonder will I be able to have them work that way, or should I calm down with planing to move my entire Excel databases into Access workspace and will be forced to have some stuff only in Access, some only in Excel (sadly not with 90-100% overlap).
If I can do most of it - I will invest much more crazy amounts time to learn Access.

Generally - I have specific questions before I understand all the basics. I'd like experts to shed some light on them, before I would have to spend anxious hours of babystep (and, pardon me, often cookie-cutter) tutorials.

 

[MarkK]

What are we talking about here? What problem are you going to solve with a database? Do you have a warehouse, a CD collection, or maybe you manage rental units? A database models a real world system, storing and processing the data that a real-world system produces.

Let's get started building it and you can see what is involved. What do we name the first table?

 

[pandzio]

articles and books divided in chunks that are of scholar matter. Contemporary text analysing and cross referencing the ancient texts. Several translations of same ancient texts with cross references. I am trying to have a database of old text ancient Greek corpus I work on with all the cross references to my notes and notes from other sources and tagging each chunk (cell) as each chunk relates to many or few other chunks.

It relies on lot of images and lot of formatted text.

My concerns are for the visual part of working experience.
In Excel I can drag and resize everything and see all texts wrapped and perfectly fit the row hight, all images displayed and however I filter I can see and read all the texts. My images often are scanned texts (like old manuscripts) so I am highly "sensitive" to have the view adjustable not only in exported format (report or however you call it) but also in the input view. It will be hard for me to add new data and connections and select the data I need to connect if I don't see the whole image/text in each cell.
Also by default my Access makes all the rows of the table same hight, so I have the row with long text field that wraps to 25 rows the same high as 20 following rows which have two words in the long text column and no image in the image column or very small one. Which is totally inefficient use of screen space, because I see only 4 rows when I could easily see 21 if their height was adjusted to the content.

You asked for the basic table. It has columns like:
| auto-number
| short margin-subtitle-note like tagging of the whole row
| a paragraph (or sometimes couple if short) from an article or scanned manuscript image [in Access this will have to be two different columns I suppose]
| additional image
| modern author
| publication/source title
| year of publication, url, etc - which I could link to publication ID in separate table
| translation/explanation/commentary/modern example of the method at work/other
| original ancient author
| ancient text
| book
| chapter
| paragraph ID [same paragraph of one Ancient book will be in different languages and translations or explanations]
| ancient period, dating, nature of the whole text, etc -- link from other table of Ancient texts ID's
| [and several fields with keywords - mostly with yes/no value (so I keep the as separate columns because most of them are not exclusiveness and each row can have any given combination of them) like:
- religious?
- calendar?
- mentions [this topic]
- mentions [this topic] ... etc
- Person life analysed [person's life ID]

I am also thinking of several columns that I am not sure if I can keep in one row for a group or better every item per column.
For example:
column "Ancient authors mentioned"
(a row could have "Ptolemy" OR "Hippartos, Asclepios of Nicea, Ptolemy, Barthatos" - I know I can filter them by occurrence of word Ptolemy, but probably it will get messy when I would try to sort them but the names will be in random order (not always the same, not always alphabetically etc).

And the "compare" column which I still am not sure how to "design" because of complex values:
Cells would have values like "Ptolemy" "Ptolemy ii, 3" "Ptolemy iii, chapter title" "Ptolemy, Hippartos" "Ptolemy iii, Barthatos ii"


It's just a part of the table.

Another table would have person's life ID and birth data and several other data - to be linked with the field [person's life ID] in the 1st table

I am still thinking it through. I see it is easier to format RTF text in Access than it is in Excel. But I'm not sure would I treat that for other functionalities I had in Excel (view the whole of the cells, all images and hight of rows snapped to the content length).

 

[jdraw]

pandzio,

There are number of free data models at Barry Williams site. You may get some ideas from a few of these generic models.

Also, I recommend this 8 part video series on Database by Dr. Daniel Soper --- starts here.
Watch 1,2 and 4 to start.

Good luck with your project.