Brand New to Access

[south0085]

Good afternoon. I am an avid user of Microsoft Excel, but I am just now beginning to dabble in Access. This is a tool that I MUST learn to use. However, my level of knowledge and experience in Access is super limited. Below, I have some excel data showing the scrap that our company throws away each month. For this example, I am using September's data. Here's a breakdown of the data. I'll walk you thru the first line. The month is September; the line is just the name of the production line that we have (P2 means Paint Line #2); the program is just another name for the car (it could say dodge ram, or ford escape...and so on, but these are the codes we use); the item is the part that was thrown away or scrapped; the color is the color; RL is either a right hand part or a left hand part; total is spray is the qty of pieces that were painted; the qty scrap is the qty thrown away; scrap cost is the dollar amount that we threw away. For example, a 120N Cover might cost 5.00. If we scrapped 10, then the total cost of the scrap is 50.00.



Anyways, here's what my data looks like. This is not all of it (obviously). I have TONS of data and for every month. That's why I need Access lol.

Line	Program	Item	Color	RL			Total Spray	Qty Scrap	Scrap Cost
P2	120N	COVER	4Q2	L			98	10	27.3
P2	2QM	VISOR	536	L			101	53	237.44
P2	2QM	HOUS	536	L			107	44	199.32
P2	120N	COVER	202	L			110	25	68.25
P2	120N	COVER	1D6	L			112	11	30.03
P2	120N	COVER	202	R			112	40	109.2
P2	483N	BODY	6T1	R			114	29	122.96
P2	120N	COVER	056	L			116	19	51.87
P2	2QM	HOUS	536	R			116	73	330.69
P2	483N	BODY	062	L			116	69	292.56
P2	483N	BODY	1C0	L			116	41	173.84
P2	483N	BODY	202	R			116	58	245.92
P2	635N	BODY	6T1	R			117	13	55.12
P2	SJC	BODY	529P	R			117	51	216.24

Every month, my executives ask me things like:
1. what are the top 5 120N pieces scrapped and how much did those cost us?
2. what color of the 2QM cover was scrapped the most?
3. what line are we scrapping the most of?

and so forth. With excel, this data is sooooo difficult and time-consuming to obtain. I know Access can do this for me. I just don't know how. I am not looking for someone to just give me the answer. I am willing to do all the leg work. I just don't even know where to begin. Thanks so much for your time. I can send you my excel data also if you wish.

[jzwp11]

The key to any successful relational database (not just Access) is the table structure. It provides the foundation for everything else. Designing that table structure requires a different way of analyzing and organizing data compared to a spreadsheet.

Of course, there are rules to be followed: the rules of normalization. This site has an overview. The site may be somewhat difficult to understand, so you may also want to look at this site as well.

Some general recommendations when setting up tables:

1. It is best not to use spaces or special characters in your table or field names.
2. It is best not to have lookup fields in your tables even though Access has that capability. This site details the problems table-level lookups can cause. Lookups (combo/list boxes) are best left for your forms.

3. Do not use reserved words as table or field names. This site has a list.


My personal recommendations:
1. Include an autonumber primary key field in every table. Make sure to replace the default name (ID) that Access gives it with something more descriptive.
2. Include a prefix in the fieldname to help identify its datatype. It also helps to avoid item 3 above. The typical prefixes I use are as follows:

txt=text datatype field
dte=date/time datatype field
pk=primary key; always an autonumber datatype field
fk=foreign key; always a long integer number datatype field
curr=currency datatype field
sp=single precision number datatype field
log=logical datatype field (yes/no)
long=long integer number datatype field that is not a foreign key field


Based on the info you provided, you have multiple lines, so those should be listed as records in a table

tblLines
-pkLineID
-txtLine
other fields specific to the line

You have multiple programs; each should be a record in a table

tblPrograms
-pkProgramID
-txtProgramName
other fields specific to the program

You also have items

tblItems
-pkItemID
-txtItem

...and colors

tblColors
-pkColorID
-txtColor


...and L/R designation


tblSide (2 records in this table: Left, Right)
-pkSideID
-txtSide

The next step is to determine how the various entities above are related. From your discussion, a program can have many items; that describes a one-to-many relationship. Further, an item can apply to many programs, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (programs and items in this case), you have a many-to-many relationship which is handled with a junction table


tblProgramItems
-pkProgramItemID
-fkProgramID
-fkItemID

Now you specifically mention that the cost for an item is dependent upon the item and the program: a 120N Cover might cost 5.00. If those are the only things that the cost depends on (and not color or L/R or anything else) then the cost should be captured in a field in tblProgramItems.

tblProgramItems
-pkProgramItemID
-fkProgramID
-fkItemID
-currCost


If the cost is dependent on other things, then you will have to provide more details.

Do the colors for an item depend on the program or are the colors of an item independent of the program?

Are the L/R designation dependent on the item or the item and the program?


You obviously capture total and scrapped. How often do you capture that data: daily, weekly etc.?

[south0085]

Wow. Thank you so much for your reply. I've been reading through this a couple of times to get it in my head. I'm not familiar with some of the terminology so it's going to take me some time. Before we move on, I feel I should mention one thing. The purpose of this database I'm needing is not to "enter" the data. But rather to "analyze" the data. Our company is currently entering all the data into an excel spreadsheet. Yes, I know a database would be the best way, but this is how it is for now. All I do each month, is take this excel data and sort and organize it until I have what I need. That is very time consuming. So, what I'm needing is to input this data into access and then analyze it. Is that what you have described above? It seemed to me you were telling me how to design a database for inputting the data. I apologize for the confusion. I should have mentioned that up front.

Moving on, my data each month looks the same. Here's an example of September's data again:

Line	Month	Program	Item	Color	RL	Combine	Total Sprayed	Qty Scrap	Cost	$ Scrap	Pass?
P2	September	2QM	BASE	536	L	P22QMBASE536L	89	35	3.57	124.95	61%
P2	September	2QM	BASE	536	R	P22QMBASE536R	47	31	3.57	110.67	34%
P2	September	2QM	BASE	578	L	P22QMBASE578L	576	181	3.57	646.17	69%
P2	September	2QM	BASE	578	R	P22QMBASE578R	557	203	3.57	724.71	64%
P2	September	2QM	BASE	587	L	P22QMBASE587L	261	149	3.57	531.93	43%
P2	September	2QM	BASE	587	R	P22QMBASE587R	190	71	3.57	253.47	63%
P2	September	2QM	BASE	700	L	P22QMBASE700L	342	86	3.57	307.02	75%
P2	September	2QM	BASE	700	R	P22QMBASE700R	372	112	3.57	399.84	70%
P2	September	2QM	BASE	731	L	P22QMBASE731L	402	93	3.57	332.01	77%
P2	September	2QM	BASE	731	R	P22QMBASE731R	727	257	3.57	917.49	65%

Sorry for the uneven lines. Anyways, each month I get another spreadsheet just like this one with that's month's data on it. Should I import each of these month's excel files into access as a different table? Or just import it to the same table over and over? Should my data be from left to right like this?

							September 2011			October 2011
Line	Program	Item	Color	RL	Combine	Pc. Cost	Total Spray	Qty Scrap	Scrap Cost	Total Spray	Qty Scrap	Scrap Cost
P2	120N	COVER	056	L	P2120NCOVER056L	2.7300	116	19	$ 51.87	58	7	$ 19.11
P2	120N	COVER	056	R	P2120NCOVER056R	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	071	L	P2120NCOVERSO71L	2.7300	0	0	$ -	113	18	$ 49.14
P2	120N	COVER	071	R	P2120NCOVER071R	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	1C7	L	P2120NCOVER1C7L	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	1C7	R	P2120NCOVER1C7R	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	1D6	L	P2120NCOVER1D6L	2.7300	112	11	$ 30.03	0	0	$ -
P2	120N	COVER	1D6	R	P2120NCOVER1D6R	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	1E3	L	P2120NCOVER1E3L	2.7300	58	3	$ 8.19	0	0	$ -
P2	120N	COVER	1E3	R	P2120NCOVER1E3R	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	202	L	P2120NCOVER202L	2.7300	110	25	$ 68.25	56	2	$ 5.46
P2	120N	COVER	202	R	P2120NCOVER202R	2.7300	112	40	$ 109.20	57	3	$ 8.19
P2	120N	COVER	4Q2	L	P2120NCOVER4Q2L	2.7300	98	10	$ 27.30	0	0	$ -
P2	120N	COVER	6Q7	L	P2120NCOVER6Q7L	2.7300	0	0	$ -	0	0	$ -
P2	120N	COVER	8J7	L	P2120NCOVER8J7L	2.7300	58	47	$ 128.31	0	0	$ -
						TOTALS	664	155	$ 423.15	284	30	$ 81.90
						2ND PASS PERCENT		76.7%			89.4%

There, you can see that Sept's data is in one set of columns and oct's is in the next and so forth and so on. How can I query data for a specific month unless my data is from left to right? Plus, if I compile the spreadsheets on top of one another, there will likely be some duplicated values.

I'm sorry for sounding so confused. I really need some slow guidance. Thanks so much once again.

[jzwp11]

It seemed to me you were telling me how to design a database for inputting the data.

Whether you are using the database for inputting the data manually or importing the data into it, the design must be sound & must follow the rules of normaliztion in order to do the quering, analysis, reporting or whatever else you may want to do with the data.

Sorry for the uneven lines. Anyways, each month I get another spreadsheet just like this one with that's month's data on it. Should I import each of these month's excel files into access as a different table? Or just import it to the same table over and over? Should my data be from left to right like this?

Like data should be in 1 table, so you would not have separate tables for each period/month. Now you would import your spreadsheet data into a table in Access and then run a series of append queries to get the data from your imported data table into the table structure I am proposing. After that, you would delete the table of the imported data. You would have to repeat this process every month. If the spreadsheet is formatted the same each month, the process can be automated... but that requires programming and is more than you probably want to get involved with at this point.

So if you want to continue with Access, then we need to work on the design of the table structure, and I ask my questions again:

On what factors does the cost of an item depend? program? color? L/R designation?

Do the colors for an item depend on the program or are the colors of an item independent of the program?

Are the L/R designation dependent on the item or the item and the program?

[RayMilhon]

If you have tons of data every month and all of the data is stored in Excel. How many Data entry people do you have employed there? Sorry but I have serious doubts that's the true source of your data. I suspect that your data source is probably SQL Server Maybe Oracle and your spreadsheets are reports exported from the Database. See if you can find out the true source of the data that'll make it much easier. You can then link to the source database and run all of your queries/reports from the source data saving your company tons of man hours and money.

[south0085]

I'm 100% positive that the data is being entered every day on excel. No doubt about it. I know....it's crazy.

[RayMilhon]

Wow, that really surprises me especially with the amount of data you're talking about. Oh well, never mind. jzpw11 is giving you the info you need hope it goes well.