BLOG? of my Access journey

[JusticeEmpire]

Firstly, I'd just like to check if I would be allowed to 'Blog' so to speak. I was thinking that it could not only be interesting read, but might being able to help someone in the future who is basically starting Access from scratch...........I hope the mods don't mind

Anyways, I posted this breif description on my introduction post, so I thought I would copy it into here as a good start -

"Basically, I am starting my own business, and I wanted a database to keep all my products information in one easy place. It needed to be a database rather than an Excel as there are many different distributors I use, and in the future, I want to be able to create a use a macro for a quoting tool. In order to do that however, I am sure I need to create a database first.

I am predicting (and hoping) that as my company grows, I will be able to convert/upgrade to some form of SQL server for ecommerce site, and for future staff members to be able to access the 'products' database. I know that there are sales software solutions available for that, but at this stage, money is tight, and I don't want to be spending out on expensive software that I am not going to be using to its full potential."


So, after posting that, I got to work. I then got stuck, so I then searched, and read and learnt. I think I am starting to understand the power and complexity of Access and the database programs in general, and was starting to think that a spreadsheet might have been better. BUT, that's boring and not a challenge, so I am going to stick with it. I have some mid-level knowledge of programming remote controls and home automation systems, so I am not a complete newbie at technology, but I am with computer softwares.

It is becoming apparent that what I am actually creating is an Electronic Point Of Sale program rather than just a database, and after reading through some of the posts on here, it is also apparent that I haven't 'designed' what I wanted before I started. I don't think I am in too deep to pause and design it, so that's my plan for the next couple of hours, without getting to imaginative and carried away.

The main purpose I want this database for to start with, is a place where I can input product data. So this would contain things like Manufacturer, Distributor, Product Name, Description, Price, quantity in stock and all of those sort of things. One thing that I do need to do is take out the spaces in the field names, as I can see that this might be a problem in the future?!

Entirely with the mod's permission of course, what I would like to do is keep this as a blog where I can detail what I have done so far and others can read and contribute where they see fit.

What are your guys thoughts?

Justice

 

[jdraw]

Here are a few links that you may find helpful.

http://forums.aspfree.com/attachment.php?attachmentid=4712

The first few topics here are very good
http://www.rogersaccesslibrary.com/forum/topic238.html

Existing model:
http://www.databaseanswers.org/data_models/customers_and_orders/index.htm

 

[JusticeEmpire]

Thanks jdraw, they were VERY helpful.

I took a look yesyerday and saw that I had already jumped the gun. So I stopped working on the database, starting reading and was planning to do some more work on it today, but I have been without power for around 7 hours today, which kind of hindered progress.

The mistakes that I had made were that I had things like Distributor, Manufacturer and Product category in the same Product table. After reading the above links, it became clear that they needed to be their own seperate tables, and made perfect sense after thinking logically about it too.

So today, I sat down and listed my different tables and fields. This is what I have come up with :

PRODUCTS - Product Category, Manufacturer, Distributor, Product name, Product description, Colour, Finish, Serial Number, Lead time, Buy Price, Sell Price, Discontinued, In Stock, On Order, Reorder Level

PRODUCT CATEGORY -
Multi-room Audio, Multi-room Visual, Networking, Cables, Connectors, Remote Control, Speaker, In-ceiling speaker, On-wall speaker, In-wall speaker, Outdoor speaker, Projector, Projector Screen, Lighting Control, Lifts & Mechanisms, Labour, TV Screen, Accessories, Amplifiers, DVD player, Blu-ray player, Games Console, Gaming Accessories, KNX, HDMI Distribution, Seating

MANUFACTURER - Name, Address Line 1, Address Line 2, Address Line 3, Address Line 4, Address Line 5, Town/City, County, Postcode, Country, Telephone, Fax, Website, Email 1, Email 2, Email 3, Email 4, Email 5, Rep Name 1, Rep Email 1, Rep Phone 1, Rep Name 2, Rep Email 2, Rep Phone 2, Rep Name 3, Rep Email 3

DISTRIBUTOR - Name, Address Line 1, Address Line 2, Address Line 3, Address Line 4, Address Line 5, Town/City, County, Postcode, Country, Telephone, Fax, Website, Email 1, Email 2, Email 3, Email 4, Email 5, Rep Name 1, Rep Email 1, Rep Phone 1, Rep Name 2, Rep Email 2, Rep Phone 2, Rep Name 3, Rep Email 3



Sorry if the formatting is a bit rough, I've copied and pasted it from an excel type document.
I've got a bit of an understanding regarding Foreign Keys etc, but I wanted to just clarify a couple of things.

1 - Product Colour, is it ok to have this in the same table, or do I really need a seperate table for Colour.

2 - Product Category, do I need to break these down even further? e.g. instead of just 'cables', should I go for 'HDMI cables', 'Speaker Cables', 'Interconnect Cables' etc?

I am hoping to get most of the above completed by the end of tomorrow, so I will more than likely have some more questions then.

Until then..................................

Justice