Access or Excel

[cSciFiChick]

So I am not sure if this is better to do in Excel some way or Access. So I have a Excel file with Open orders. I update it every week. Right now I do it manually. Basically I have notes on some of the open orders of why they are still open etc. So when I pull the new open order report I need these things to happen. Delete any orders that are on the Old report but do not show up on the new report. Then I need to update the Old report for certain columns to new data. For example one column if it says 'Order Done' then that column does not need to be updated but if it says anything else then it needs to be updated. Some columns I need to update if the data is different like if the price has changed in the new one then I want it to update. Here is a break down if it makes sense:

Customer - Match
Order # - Delete and Old ones that are not on New list
Order Date - should match
Total Price - Update to new if different from old
Status - if already on the old data and says 'check status' then take that from the old data
shipping - always update to new data
Note - keep from old data

Sorry I am new to access and don't quite get how it works yet so if someone can help me with this or if you have a really good book for learning to do this stuff in access I would not mind looking into that.

THank you

 

[Micron]

Access is definitely the best tool for this but you will not think so if you start off on the wrong foot. Relational databases are 'tall' (records group data, not fields) whereas spreadsheets are the opposite. I'll finish this with a whack of links that you really should research - they will help you avoid many of the typical novice mistakes. However, what you get out of the project will depend very much on how much you are willing or able to invest up front in research time. You probably should look for samples/info on whatever your db is going to support (sounds like sales orders) because your post indicates you're ready to make a few procedural errors as well. F'rinstance,
- you generally don't delete db data, you flag it somehow (a date field usually works best).
- you don't store calculations as a general rule, you calculate on the fly in forms or reports - another topic for you to research

Tables design is often done with paper and pencil, believe it or not. I'll often use large sheets, naming my tables and fields, defining their data types/limits and even drawing lines between related fields to help me visualize the relationships. Once tables are actually created, you could design your relationships in Access and post a pic here for review. However, anyone would have to have an understanding of what the process is that the db will support.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields - Mendip Data Systems

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

 

[cSciFiChick]

I might need Access for dumbies or something because I think this is above my head. Mainly before I learn by doing so examples in those are helpful but too much reading and my brain closes. I will have to play around with it and see if I can figure it out.

 

[Micron]

I might need Access for dumbies or something

Well that's still reading, no? An option might be to start researching the link to get a slight grasp on what's going on. Then Google for vids only, using some search criteria that you picked up from the written pages. I'll bet you will find vid examples of normalization but probably there are not vids for all of those subjects.