Table Design

[John K]

Hello,
I am new to access and databases. I do understand that a database is no better than the table design. Without a good table design you have big problem when you create queries and code.
My question is do you always need to split a table? For example if I created a database on stars. You only have facts about one thing. You would never need a one to many relationship. What I dont understand with this is when you start creating queries or code to get the info you want. For exampe I could have 30 fields for each record. Lets say I want to query record number 1 against every other record within the database then start with record 2 until I complete all the records to find a records that match certain feilds.
Another question about tables, I want to store pictures of each star. What is the best way to store them. I may have 1 photo of record one and 3 of record 2 and 5 or record 3. I want to be able to display this info in a web page or be able to send it be email.
This may be to much info for this thread but without understand what I am trying to do I dont feel I can get the correct answers.
Thank you for taking the time to read this post and any help I will be very greatful for.
John

 

[Beetle]

For example if I created a database on stars. You only have facts about one thing.

I'm no astronomer (I assume we're talking celestial stars here, not the Hollywood kind) but right off hand I can think of a few bits of information about stars that would justify separate, related tables;

1) The type of star (Red Giant, White Dwarf, etc.)

2) If it's part of constellation (if you track that sort of thing)

3) Which galaxy it's in (if you're tracking stars outside the neighborhood)

4) The color

Probably more if I knew more about stars. Now, some of these tables might really be just look up tables for ease of data entry and consistency, but separate tables none the less.

 

[Beetle]

I want to store pictures of each star. What is the best way to store them. I may have 1 photo of record one and 3 of record 2 and 5 or record 3.

Another reason for an additional, related table. As far as the best way to store them, that depends somewhat on the version of Access. Generally speaking you want to just store a path to the file location, but newer versions of Access have become more suitable for storing the images in the database itself, to a point.

I want to be able to display this info in a web page or be able to send it be email.

If you have a need for direct access to the data via web pages, that's an entirely different subject and Access (the Ace/Jet database, not the UI) is likely not the best tool for the job. There is the A2010 and 2013 web database option, but that has specific requirements (i.e. you must use Sharepoint with Access Services) and for any existing applications would likely require an almost complete redesign (minus perhaps the tables) from scratch, because none of the older objects (queries, forms and reports) are compatible. You might want to give some thought to exactly what your requirements are, then do some research as to whether or not Access is even the appropriate tool for what you want to accomplish. You might find this sticky helpful.

 

[John K]

I am using Access 2010. I dont really need the data to be updated on the web. I also dont know if you can create a static web page based on a query and add photo's from access. I am really just playing around with ideas and trying to learn. I very much enjoy thinking about this and seeing if I can get things to work.
John

 

[Beetle]

Well, if you're in the mood to tinker with things for learning purposes, you can actually sign up for a Sharepoint Online account for as little as $3 per month per user, so if you're the only user that's pretty reasonable. Even the $3 account includes Access Services so you can publish an Access web app. If you determine it's not going to suit your needs, you're not out much money. Look here if you're interested.

This will of course add the additional learning curve of migrating and managing your Sharepoint site, but you seem to indicate you enjoy that sort of thing (like I do), so "carpe diem".

I also dont know if you can create a static web page based on a query and add photo's from access.

You might be able to accomplish what you want with a web form running in a browser on your Sharepoint site (if you decide to sign up). Couldn't really say for sure because I don't know your specifics. Then again, even if you were able to do that, if you wanted others to have access to it they would have to be added as users on your Sharepoint account (at the required monthly fee for each) so that might not be of much use if you want this available to a lot of users. A Sharepoint Online account does include a public facing web page, so there may be a way to load image files to that from your Sharepoint Lists (when you publish an Access database to Sharepoint, the tables are converted to Sharepoint Lists). Again, I don't know if this is possible, never attempted it, I'm just spitballing ideas.

Also, when it comes to the Access web applications themselves, you're dealing with a bit of a different animal than you may be used to if you have used Access in the past. A web application can include both Client objects and Web objects (by objects I mean queries, forms and reports). Client objects are much more flexible when it comes to design, and they can use VBA code (in the forms and reports), so you can do more with them, but they will only run inside the Access client. Web objects will run in a browser, but are less flexible as far as design and they cannot use VBA code, so you have to do everything with macros. Depending on what you're attempting to do, this might require a table level data macro, an embedded macro in the form, or a combination of the two. It can end up being a lot more work to accomplish the same thing that could be done with a few lines of VBA code.

I guess I'm rambling on a bit here, so I'll conclude with this. Having said all of the above, the first thing you should do if you're new to databases is do some research on proper relational table structure. All of the above will be quite pointless (and a colossal waste of time) if you attempt to build on top of a faulty table structure. You can find a good tutorial on the basics here.