Need advice - will a form do this?

[susan]

Me: new user - access 2003

Why Access: I currently have an excel spreadsheet that my buyers use to
fill out purchasing information and submit to data entry. My buyers send me
the spreadsheet for approval, then it goes to another department for data
entry into the company's inventory system. However, I have no control over
when the information will be in the system and I need to do analysis daily
with the data. I figure if I create a form in access, my buyers can enter
the information directly into the db (instead of in excel spreadsheet) and I
will have the most up-to-date data to use.

Each submission have a unique program# and have standard information like
buyer name and supplier name. Each program # will also have multiple PO#,
Part#, Price and Volume.

This is what the excel look like:
Program#:
Buyer name:
Supplier name:

PO# Part# Price Volume
1 xxx xxx xxxx
2 yyy yyy yyyy
3 zzz zzz zzzz
etc...


Problem:
I have put together a form to include all the fields I need from the buyers.
However, inorder to include all information in a table, I need to put the
program#, buyer name, supplier name as fields in the table. The problem is,
my buyers will have to enter all the standard information for EACH PO# that's
under a single Program # (when in excel, they only have to enter the standard
info once and all PO will fall on same sheet). I don't want to create more
work for them.

I hope this explains why I want to use Access. Is this possible to do by
myself? or will it require IT expert to do? Any suggestions will be greatly
appreciated!

Thanks,
Susan

 

[Klatuu]

First thing you need to do is understand how data in a relational database
works. It is much different than in a spreadsheet. To do what you want,
you need several tables. One of the rules of a relational database is you
store a unique data item in one place only. This would apply to suppliers
and buyers, for example. In your Program table, you do not repeat the buyer
and supplier information, you use a field that identifies which buyer or
suppler record to use from the buyer or supplier tables.

Before we go on, one quick note about name fields or anything else in
Access. Don't use spaces or any other special characters like #. Use only
letters, numbers, and the underscore character _. Also, avoid reserved words
like Date, Month, Description, Year, etc. If you use descriptive names, it
will never be a problem. For example InvoiceDate, AcctMonth, PoDescription,
FiscalYear.

Now, we need to talk a bit about primary and foreign keys in tables. A
primary key is a field that uniquely identifies a record and and no two
records can have the same value in the primary key field. A foreign key is a
field in a record that identifies the parent record to which the record
belongs. In other words, to use your data as an example, in your Program
table, you will want to identify a buyer. But, you don't put the buyer's
name in a field the record, you put the value of the primary key from the
buyer associated with the record. And, it can be the other way around. For
example, you will have multiple records in your PO table associated with one
record in the Program table. the PO table will have a field that identifes
which Program the PO belongs to.

Now, if I haven't thoroughly confused and demoralized you, we can describe
the basics of the tables. First sit down with your spreadsheet and decide
which table each column belongs to. Each column will become a field in a
table.
For example, you know the Program number will be in the Program table. It
is also obvious it can be the primary key, because that is what you want to
define, a program. Now, how about the PO table. For certain, you need a PO
number, If a PO number is always a unique number and wont be used again next
year, it can be the primary key of the PO table. If there will only be on
part number per PO, it can a field in the PO table; however, my guess is a PO
can contain multiple items(parts), so you will likely need a PO Header table
that describes the PO, then you will need a PO detail to describe those items
included in the PO. You will probably also need an Inventory table that
contains a record for each Part that includes the price for that part. And,
you do need the buyer and supplier tables. The supplier table will likely be
a child table to the Inventory table because parts are supplied by suppliers.

So, to get started, get yourself a container of your favorite beverage, a
pencil with a good eraser, a few blank pieces of paper, and get in a
comfortable place. Draw a vertical rectangle on your paper. Label it
tblProgram. make a list of all the data items that relate to one and only
one program. Draw another rectangle and label it tblInventory. Do the same
thing. Then another rectangle and name it tblPoHeader. In this box, write
the name of the primary field of tblProgram. Draw a line between the two
names in the two rectangles.

Continue this process until you have each data item you need described once
except for the primary/foreign key fields.

Once you have completed this exercise, you are now ready to open your new
Access database and start defining tables, fields, indexes and relationships.
Relationships? Those are the lines you drew between the rectangles that show
which tables relate to either other.

Post back with more questions (I know you will have them)

 

[susan]

Thanks Dave for the detailed explaination - really appreciate that. I was
hoping you will pick up my question because I read a thread by you from
yesterday and felt you were very detailed and that will really help!
(unfortunately the user didn't have the patience to learn!) I will need some
time to digest what you wrote and try it out. I actually know how to create
queries and macros already so I am sure creating a form will not be that much
harder! will keep you posted! thanks,susan

 

[Klatuu]

Thanks for the kudos, susan. I know this sounds pretty daunting, but once
you get your head wrapped around the relational concept, it becomes fairly
easy.

 

[BruceM]

On the subject of reserved words, there are listings available. An
assortment of related links may be found here:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords

I have found one of the links in particular to be a great help:
http://allenbrowne.com/AppIssueBadWord.html
This link includes an easy-to-use utility for checking databases for
reserved words and other potential problems.

 

[susan]

hi dave, i read thru ur suggestion and here's my thoughts:
i don't know if i have made things easier/harder but here's how my excel
works...

buyer name:
supplier name:
former supplier:
commodity:
program name:
category:
comments:

PO# program# PlantName FinanceCode ProductLine Part#
CurrentPrice NewPrice EffDate PartDesc Volume
Unitofmeasure FCSTspend
(these are the columns in excel)

The buyers submit the excel to me along with an approval form from our
intranet called e-form. the system will generate a e-form# and i have no
control over
that number. also the program# is assigned by the data entry team based on
1. the e-form# and 2. the finance code.

The buyers would submit this form everytime there is a price change or new
part = the PO# can duplicate depending on the purpose of the form.

The program# is filled out if this is a price change so the data entry
person would know to pull up that program#, find the PO# and update the price.

The Financecode correspond to the PlantName.

The FCSTspend is a calculation (Current price - New price)*Volume

so basically none of the entries are unique on this form by itself. all i
want to do is capture all the data that comes thru me so i can use them (i
don't trust the data entry people 100% therefore don't trust the data once
they go thru the system).


***
I did what you said - to seperate the information into tables:

tbl_inventory tbl_PODetail tbl_POHeader
Part# program# (I am not getting this table
CurrentPrice PlantName as a header to describe the PO?)
NewPrice FinanceCode what do i put in here other than PO#?
Eff Date ProductLine
PartDesc
Volume
UnitofMeasure

What's left is: Buyer name, Supplier name, former supplier name, Commodity,
Category, Comments, Program name, FCSTspend(calc)

where do they fit in? Do I have:

tbl_Buyer tbl_Supplier
buyer name supplier name
former supplier name

Am i on the right track here? am I missing something? i am a little
lost...help!
Thanks,
Susan

 

[susan]

thanks bruce, i will go thru the links you have provided!