Can't find the primary key! HELP!! lol

[MsNaniB]

Hello everyone! Thank God for this forum... I am not an expert at Access but the executives in my office are looking to me to build this HUGE database, so here I am, doing my best, and I've run into a problem.

I have a table with the following field headings:
AutoNumber, Budget #, Project, Function, SubFunction, Process, Account, Budget Month, Plan Year, Budget Year, Budget Amt, and Notes.

When I first built this table I let Access choose "AutoNumber" as the primary key just to get started. My AutoNumbers are all out of whack at this point, so obviously I can't use that field as a primary key and besides, I know better. But now I'm stuck with no idea what to make the primary key since every field in this table will have duplicate info.

My boss wants the "Budget Amount" of $60,000 for Project "x" to be broken down by $5,000 a month for 12 months, so that she can pull up any random month for this year, and see what's budgeted for that month ($5,000). My table is already built which means I'd have to add 12 new records at the bottom for "Project X", broken down by 12 different months. So this is how all of my fields are going to end up with duplicate information. I'd have to manually type in all of Project X's info 12 times..hence..duplicate info.

Can someone tell me the solution to this problem, please? I feel like I want to run out of my cube screaming right now...lol Thank you!
Nani

 

[spikepl]

I have the same comments as in this post: http://www.access-programmers.co.uk/forums/showthread.php?t=212614

 

[MsNaniB]

Thank you for your links, but I'm not sure I have it 100%. I don't know how to use what I just read and relate it to my issues. It seems like I need to make another table, but I'm not sure what kind of table (what fields)? I'm so frustrated. Everything was fine until I had to break this budget amount down into months..ugh! And I'm still not sure what to make the primary key.

 

[darkstar257]

Can't the Primary Key just be assigned to an autonumber that doesn't play any other part in your database and is hidden from the user?

And why are all your auto IDs out of whack? And does it even matter? Shouldn't they have been set to not duplicate by default? And what are u using the auto IDs in your database for besides tracking each record anyways?

I am confused as to what you're trying to do. Will you have a main table for each month? or each product X, Y, Z...etc.?

 

[jdraw]

I think spikepl was just showing you that others are in the same boat. They desire to create a physical data base, before the requirements have been defined (even to a stage that would represent version 1 of the proposed database).

You need a model(a data model) to represent the things you are dealing with and how they are related. The "requirements" for the "system you are building" are not finalized-- in fact they are just evolving. This is not uncommon. A data model will help you greatly, and will give the boss and others something to focus on and facilitate communications.

You should read the first few topic -- Normalisation, Normal Forms and Entity Relationship Diagramming -- at Roger's site
http://www.rogersaccesslibrary.com/forum/topic238.html

There are some data models at
http://www.databaseanswers.org/data_models/index.htm

Your model may be different than any of these, but you'll get an idea of the things involved.

Good luck.

 

[MsNaniB]

Hi Darkstar!

Let me see if I can try and clear things up for you and thanks for responding.
I have a "buget" table and an "actuals" table that are set up exactly the same, and they are tied to other financial tables. This is a financials database. My boss wants to be able to compare the "actuals month" with the "budget month" and the amount of money spent in each month against what was budgeted for each month, but I didn't know until yesterday that she wanted to do that so my tables weren't originally set up to do that. So I have these 2 tables tied together in a relationship, linked by autonumber...simply because I didn't know how else to link them. But as I tried to cut and paste and add records yesterday, my autonumber went from 77 to 79 and so on. Everytime I made a change, my autonumbers got out of sequence. Does that matter since this table and the "acutals" table are linked by autonumber?
So my main question is: How do I have 12 rows that have exactly the same info in them and still be able to make something a primary key? Do I have to have a primary key? This table needs to be related to the actuals table.
My first row has info about a vendor, the budget year, the budget month and the budget amount of $60,000.
My boss now wants to see that $60,000 broken down into $5,000 for each month, so that she can compare by month. So I'd have 12 rows (for each month) with $5,000 in each "budget amount" and of course the same vendor info would be repeated 12 times. I'm asking what should I make the primary key since all the fields will basically be duplicates, besides the month? There is no special field for me to make the primary key. Do you see what I'm saying? Gosh I hope I'm explaing this right. I wish you could see my table.

 

[spikepl]

Your not normalized table structure contains redundant data. That's why I and jdraw gave you the links to study. Access is very definitely not Excel. By not structuring your data properly, you will very quickly wind up with plenty of grief down the road.

The primary key is a means to relate records in one table with one or more records in another table or tables. The actual value of a primary key is meaningless, and should not concern you in any way. Its FUNCTION is to ensure that the right items are linked together and remain so.

Your question then should not be "what do I use as primary key" but "how do I structure all my data, so it is easy to create, read, update and delete". This is the starting point of any viable database.

1. Study the supplied links.
2. Find a small template DB doing something similar to your needs from the link provided, and see what the data are like, for inspiration
3. Propose a structure for your data: which table with which data
4. You can ask for guidance here, but that requires that there is some definite data to discuss, with table names and names of fields

 

[MsNaniB]

Ok...I read...I studied... I'm seeing now that I need to break down my huge table into smaller tables "normalization". So, since my boss will want to see budget amount by month, I'm thinking I need to make a table that has the following field headers: Project, Project Year, Jan, Feb, March...and so on. And then I can make the "Project Month" the primary key?
Does that sound right? Am I getting there? Thank you for your patience.. I'm doing my best under extreme pressure.

 

[spikepl]

No that is not right - I think you are mentally abused by Excel

Data of the same type should go into the same column and be marked by something.


So if you have, say , an expense (or budget)

tblExpenses
-------------
ExpenseID (autinumber, primary key)
Amount
ExpenseDate (you cannot use the reserved word Date)
ProjectID( the project for which the particular expense on this date was incurred)

and then eg projects

tblProject
----------
ProjectID (primary key, autonumber, NOT FOR HUMAN CONSUMPTION)
ProjectNumber/Name (Soemthing that follows the naming convention of your buisenss)


This is a one-to-many relation.

For things that can be shared, you need to add a many-to-many relation. eg

tblProjectSupporters
--------------------
ProjectSupporterID
PSName
.other stuff

and then since each project supporter can work on more than one project:

ProjectSupport
-----------------
ProjectSupportID (Primary key, autonumber)
ProjectID (foreign key the proejct to which this applies)
ProejctSupporterID (foreign key, the ID of this specific supporter working on this specific project

and in this way you can share something between more projects.

I would again strongly suggest you find a template and look at how your not uncommon requirements are dealt with

You budget things woud then be

tblProejctBudgets
----------
BudgetID (PK, autonumber , BNOT FOR HUMAN CONSUMPTION)
BudgetDate (eg the first of the month for which the budget is valid)
ProjectID (foreign key)

 

[MsNaniB]

Ok...thank you sooo much, Spikepl, I'm gonna go give this a shot!

 

[MsNaniB]

Ok Spikepl...

So I used your examples but just plugged in the info I needed for what I'm trying to accomplish..thank you so much! However, I can't find an example or template that helps me with my one, burning question.... So here are the tables I have created:

Project_Budget
-------------------
Auto_Num (pk)
Project Name
Budget# (for ex. "B6" would go here because B6=benefits)


Project_Table
-------------
Auto_Num (pk)
Project Name


Project_Year
---------------
Auto_Num (pk)
Project Name
Project Year (Most of the 77 records will say 2011, but some will be 2012)

Ok..now for my burning 10,000,000 question. If my boss wants to pull up the budgeted amount for:
Project Name= Chapman Kelly
for the year = 2011
for the month = July
budgeted amt= 8,000

How do I put this in a table when I need to list 12 months with different amounts of money for Chapman Kelly, so that each month she pulls up will show her that amount budgeted for that month specifically? Don't I need to make a table with all 12 months listed at the top and then will have to list Chapman Kelly in 12 rows , showing each budgeted amount, under the specified month? That seems wrong but I don't know how else to do it and I can't find an example. I'm sorry... I'm trying.. so please be gentle

 

[spikepl]

You should try to do redo some of the links on normalization.

Name of a project goes in one place only - the table tblProjects containing the project-specific data. The Primary key there would be ProjectID. In other tables only ProjectID is present, because through that the required project info can be determined. That table would perhaps also have two dates: ProjectStart and ProjectEnd.

Forget about YEAR. The year can be derived on the fly from a date.

What is Budget# ? Can multiple projects have same Budget# ? And the budgett# leads to some amount in another table?

I 'm afraid you have to provide many more details as to how things hang together

" Don't I need to make a table with all 12 months listed at the top and then will have to list Chapman Kelly in 12 rows , showing each budgeted amount, under the specified month? "

NOOOOOOOOOOOOOOOOOOOOOOOOOOOOO! How you PRESENT data is a completely different thing from how you STORE it Data of particular type goes into one column, and a second column, in same row, tells you eg the date.

May I suggest that you talk some local consultant who can access all pertinent info and help you out in just one or two hours? Provided you have a budget# for this type of thing, that is

 

[MsNaniB]

Hello All!

Ok..so I broke all my tables down and started over, thanks for your help. I have a new table request by my boss and I'm having a hard time finding the answer in my research, so I hope someone here can help

I have a list of Vendors and their IDs in a "Vendors" table, here are the fields:
Vendors Table
-----------------------------
Vendor Name
Vendor ID

That information comes from another table, called "Actuals", that will be updated with new Vendors. My boss wants Access to be able to pull up the new vendors that have been added to the "Actuals" table, that are NOT in the Vendors table so she can identify the new vendors. So I know I need to write a query that includes the Vendor name from the Vendors table and the Vendor name from the Actuals table, but I'm not sure how to write the "show me what's here and what's not there" criteria/formula. Any help would be appreciated.
Thank you!
Nani