Newbie Table Question..
May 21, 2006
Hello, (pls scuse my english, it's not good) I'm a newbie to Access and I'm learning access I'm trying out one scenario to see if I can do it but I'm stuck..
main scenario is that a local company has decided to computerise their wage systems, but the wage system is very confusing.. data modeling has already been done in this example but as I understand the boxes(entities) in ERD becomes tables right? so I have like five tables.. Pay method, Employ type, Employ, Department, Division..
I know what to do with Employ, Department and Division but Pay method and Employ type is confusing for me I don't know how to work out attributes (fields) for these.. I have four different paying methods depending on 7 different types of categories of employees.. how would it work?
Also if you guys have any links that explains sort of similar scenario I would appreciate it very much.. thank you..
Oh and since I'm a newbie I'd appreciate it if you give me a simple answer not complex solutions,
View Replies
ADVERTISEMENT
Sep 14, 2005
I am working on this database where each category contains certain thickness ranges that can be stored in a pack of x amount and finally paneltypes.
ie KS1000 only comes in 40mm 50mm 75mm and paneltype MR is the one associated with it.
there are 3 tables
tbl_Customer
tbl_catergory
tbl_product
--------------------
tbl_Customer:
-CustomerID (autonumber) Primary key
-CompName
-CompAddr
-ContactFName
-ContactSName
---------------------
tbl_category:
-CategoryID (autonumber) Primary key
-Category (combo box) this has set values entered i.e.
KS600
KS900
KS1000
KS1000LP
KS1000RW
------------------
tbl_Product:
-ProductID Primary key (autonumber)
-fkeyCategoryID (foreign primary key)
-Thickness (combo) this has set values of thickness ie. 40, 50, 55, 60, 70, 80, 100, 150, 180
-NoInPack (set number directly related to the Category and thickness chosen)
i.e. Category KS1000 with thicknesses of 55 can be stored in a pack of 17. KS1000 with thickness of 60 can be stored in a pack of 22.
-PanelType: (combo box) set values as follows:
MR (this selected item relates to products KS600 KS900 KS1000 )
EB (this selected item relates to product KS1000RW )
CX (this selected item relates to product KS1000 )
MM (this selected item relates to product KS1000 )
WV (this selected item relates to product KS1000 )
---------------
I would like to be able to setup a form that a user uses to provide a quote:
It would start with using the Category combo box which lets you select one of the choices (i.e. KS1000) but the combo box would also have multiple column headings ie. Category/Thickness/NoInPack. Once selected then the next several individual field boxes (ie. thickness, NoInPack) are automaticallly populated with the selection just made in the Category combo box.
NOTE: Many categories can be chosen just for one record.
I therefore do not want to start setting up fields like Product1 product 2 etc and their associated fields like thickness1, thickness2 etc.
I'm failing in the relationships section. Please see attached database.
I have tbl_category: CategoryID (1) --------> (many)fkeyCategoryID from tbl_Product
I have tbl_Product: ProductID (1) -------->(many) CustomerID from tbl_customer
I know that i need a junction table somewhere.... but i'm a newbie when it comes to this stuff.
Any help would be appreciated.
View 8 Replies
View Related
Jan 10, 2005
Quick Scenrio:
I have a current employee's table and Retired/Resigned table, when an employee either retires, resigns or gets fired, i want to be able by the click of a switch or check box of some sort to move that record to the retired table. Your input is greatly apprectiated....
Eddie..
View 4 Replies
View Related
Mar 9, 2008
I'm sure this is a simple issue, but so simple I can't find the answer.
I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.
Not sure how or where to create this formula to populate a field in the DB.
As you can tell I'm new to all this and any help would be appreciated.
Thanks in advance
Mike
View 14 Replies
View Related
Jul 21, 2006
I'm a relative newbie and I'm trying to figure out if I can link individual rows in one table to entire whole tables.
Example:
I have a table with the following fields:
Ticker Quote Volume
MSFT $25.00 3000000
IBM $30.00 1093837
SNDK $20.00 5959483
Now each of these Tickers has a whole table of options associated with each particular ticker and the EASIEST way for me to proceed would be to simply link row MSFT with an ENTIRE table of MSFT_OPTIONS then link IBM with the table IBM_OPTIONS.
Keep in mind that I don't have a field called TICKER in the options table and MSFT does not appear anywhere in the options table.
I know I can go and add the TICKER field to the OPTIONS_TABLE and then assign a primary key and then link them both but this would involve a great deal more work since I have hundreds of stocks with options tables to link. The data I get is in a specific format and I don't want to have to massage the data too much.
I appreciate your help,
Mr. Opine.
View 1 Replies
View Related
Jul 20, 2006
Hi all, new member with a newbie question. I have just started working with Access, so pardon if this question is unclear or pitifully basic. We have a basic database comprised of client names, amounts paid to-date and balance due. It also has a field named date last paid, so here is the question. When you enter or change the amount in paid to-date, can you automatically have the date of the change (ie. Todays Date) entered in the "Date last paid" field.
Thanks all for the help in advance.
Chris
View 5 Replies
View Related
Nov 29, 2004
Hey all,
I've never used Access much...i was able to use it OK at one point but ive forgotten all about it now.
I need to create something very simple for the reception at my work...
When a customer phones we want to be able to keep track of how they heard of us - so we want a very simple access/VB program.
The best way would be to have buttons of each of the magazines our company is listed in..then when someone phones and says "ahhh magazine 3" the receptionist can press a button and the button will add 1 to a field in a table?/report? next to that magazine.
Hope that makes sense :s
Any help would be very appreciated! - It seems very simple to do ?
Thanks
Acle
View 5 Replies
View Related
Dec 1, 2004
Okay I've looked around but haven't found an answer to this, if there is one in the forum please forgive me. I've created a database to help track employee passwords for differnt system I placed and Audit Trail on the form (the example from Microsoft) however it updates only the form and not the table and I need it to do both. Please Help!
Thanks
Rand
View 14 Replies
View Related
Jun 24, 2005
Great forum!! Hope I someone on here can help me!
Here's the situation. I have a Database in Excel (~3000 rows & 7 Columns)
The first Column has numbers like this (3-10-34 or 223-7-45) the other columns have names, addresses and other stuff.
What I'm trying to do is to eliminate the use of huge binders. I want to have a Blank screen with only one input box where I can type a number and retrieve a specific row from my Database. Time saving.
Now, when I'm searching, I usually only have the first numbers before the dash (such as the 3 or the 223) not the other numbers. So I cannot input 3-10-34 for instance. I can only put 3...:confused:
I've already imported it to Access and it works fine but have no idea how to do what I just described!
Thanks for the input!!
A very very green access user! :o
View 11 Replies
View Related
Oct 19, 2005
Hello
sorry if this question is answered somewhere else i did check through the forum a bit first
anyway to the question
is there a way in access 2003 so when i change a value in one table it creates a new record in another table
example
i have a table called tickets and one called fixes, when i change a value in a record in the tickets table i want a new record in the fixes table to be created for me
any help would be great
ADAM
View 5 Replies
View Related
Nov 3, 2005
Hi there,
I am not sure if i am supposed to post in this section......
--------------------------------
Using Access 2003.
I am very new to Access and have started off very well building my first project..
My help is that i would like a macro button to delete a record once you have put in to the database if that is possible also i would like a save button if that is possible...
I have put a Tab Contol on and want to change the backgound but i can't see on the properties box to change this. Is there a piece of code that allow me to do so...
Hope some one can help
Emailjunky
View 1 Replies
View Related
Nov 3, 2005
Hi There All,
I am still very new to Access 2003 and have started off very well..
Just a few help please:
Help 1. I need a macro button that will allow me to delete a record from the database via a form.
Help 2. I need a macro button to save the database if that is possible via a form.
Help 3. I have a Tab Control on my form and i want to change the background of the Tab Control but can't find it on the properties is there a piece of code i can put in to VB which will do this for me or is there another way.
Hope someone can help
Emailjunky
View 2 Replies
View Related
Aug 17, 2006
hello guys i m very new to access and i need some help!
if i had a form that contains a field like salary and i need to add all the salaries from all the records into one total salary how can i do it?
plz take into consideration that i m totally new and dumb:p in access
thank you
View 2 Replies
View Related
Feb 8, 2007
Alright here is what I am trying to accomplish. I have A, and B. Then I have 6 different tables that relate to the six different things B could be and contain all sorts of other information.
What I want is to make a form that lets you type in A, and B. Then the correct table/form for the respective B will pop in to the subform area. Then as soon as you select or type in a new A and B, the subform will change, or stay the same if the B has not changed, but a new record will have started.
"A" is just an ID... something like AA001, AA002, etc.
"B" is the stage each ID is at... like beginning, middle, end, etc.
Sorry if it is a bit confusing, I am willing to explain any details that could help. I consider myself a somewhat advanced Access user, but this just stumps me for some reason.
View 3 Replies
View Related
Aug 1, 2007
Hello all,
Im looking to create a frontend and backend for a Dojo (Martial arts center).
Basically, it will log in people(record their training time, by day not hour) either by Number or name.
Secondly, I want to be able to have all these fields seen by the admin:
Dojo Number
Date Joined
Last Name
First Name
Male/Female
Address
City
Zip
Home Phone
Work Phone
E-mail
Birthday
Occupation
Adult/teen/child
Rank
Active/ Inactive status
Martial Art Exp.
Staff Y/N
ASNJ Dues Info
USAF Dues Info
USAF Membership Number
Misc. Notes
So the backend/ admin would be able to update this field and keep records up to date.
There would be around 800 entries/people, and only one admin accessing the db at any time. The frontend would be used to log in..
View 2 Replies
View Related
Nov 21, 2007
Hi,
Can some kind soul help with pointing me in the right direction to a problem below?
In my database (property database), I have some old properties which are now sold. There are relationships between property, rent received, billing, lessees, etc. I want to be able to keep the details of the 'sold' properties.(i.e., who owned it, what they paid in the past, when they paid etc. in case I need it, but don't want it shown any longer on the main working database). I want to be able to delete the properties that are sold from the main table. Any ideas how I go about this? Hope I've explained myself ok.
Thanks
HMC
View 10 Replies
View Related
Nov 27, 2007
I have recently created a database which exports a daily report in Excel. The problem I am having is that the reports are sent out in Excel 95 which cannot be opened by Blackberry. We used to create this report from an excel database and send it out but it went out in excel 97 format which was readable. As a complete "Access" novice I am at a loss on how to cure this problem short of copying the excel 95 report which we create automatically and cutting and pasting into excel 97.
Over to the experts.
View 8 Replies
View Related
Feb 13, 2008
Hi All,
great forum here, loads of help and really busy :D
im very new to acces and have never used it before, however i have been using excel and VB for a while.
Ive been given a large table with about 5 fields in it and 40k + lines of data (!)
I need to write some kind of program/query, where i can input some txt and it will then lookup all the data that relates to that input txt.
Eg.
Car Tom Red
Car Tom Blue
Bus Dave Red
Bike Tom Pink
If i did a seach on "Tom" it would then give me the 1st, 2nd and 4th lines (all the info in that whole line)
If i did a search on "Red" it would give me the 1st and 3rd lines?
Im sure this is very simple, so any help would be great :)
Also, really sorry if this is in the wrong location, please move if so!
View 5 Replies
View Related
Apr 23, 2005
Very much a novice here
What im trying to accomplish is so simple its silly, but i cant figure it out for the life of me
I have a field id like to restrict entries to the words Buyer and Seller, and i'd like it so that in the form, if the user hits the letter B, Buyer fills in and if S seller fills in ...
What can i do in the table to accomplish this?
Thanks so much
View 1 Replies
View Related
Oct 6, 2005
Hi, I have used access in the past but very little. I need help setting up a table that I can make relationships to another table. Here's what I need.
I need a table(s) that I can log samples of medications into. It needs to have a drug name, dose, lot #, expiration date, and #of samples. As we get samples, these will be logged into here via a form made from the table (I've already done this exactly as stated). I will need to be able to get a total from the number of a certain dose of medication i.e. amoxicillin 500mg (all of the lot numbers added together).
Now, another table(s) needs to have samples signed out. It needs to include pt. name (first and last), date, drug, dose, lot #, exp. date, #of samples, ordering practitioner, and clinic. What I want to happen is when I sign out the Amox 500mg #30 with a specific lot #, it will remove the number from that specific lot # in the inventory table and reduce the number of Amox 500 total (all lot #s).
Does this sound too complicated. I tried to set up one table for the sign out process, but suspect it's too many variables. The primary key I was thinking would be the lot # but i need to be able to use it more than once if I get 100 samples and only use 30 at a time.
Would someone be willing to offer some advice!!!?? Thanks, Robin.
View 1 Replies
View Related
Jun 3, 2006
I made a simple Table with a couple of fields.Each record shows what a job needs to be done ...Now I'd like to add
simple checkbox and each time I would check this box (that would mean that job has been done) Access would automatically delete or hide that record.
Thank you
Also could you point me to good tutorials?Thanks a lot
View 1 Replies
View Related
Dec 6, 2005
Hello everyone:
I've created a simple access database for my deparment, that I want other departments to be able to query data from. I don't want them to have to open the entire database each time they pull data, because I'm afraid they'd become confused by the program.
The company seems much more comfortable with excel files, and I've been trying to use an Excel-based query to display selected information. The problem is this: Nobody can update their excel queries, because only my department has full access to the .mdb file that the data is stored in. Everyone else has read-only access to the file and the folder it's located in, so they are unable to run an update.
Is there a good workaround for this? I feel like I'm overlooking something incredibly simple.
Ideally, I'd like to create an .xls file with the necessary queries in it, and then just email that file around, allowing other users to just save the file to their desktop and open it/update it when they need to check on something. Other methods of accomplishing the same thing are welcome too, but that's what I'm going for right now.
Thanks!
View 2 Replies
View Related
Mar 6, 2006
I need help. Now I've written a post so that some one could help retrieve current information and they told me to use Dmax. Great but I don't know how to use Dmax. Basically I need step by step instructions.
This is an example of what my table looks like:
Date of Order --Product--- UNIT COST
20-May-05----- XYZM---- $8.87
20-Jun-05------ XYZM ----$9.39
21-Jul-05 ------XYZM -----$9.24
19-Mar-05----- ACE ------$7.85
20-May-05 ----ACE------- $7.89
20-Jun-05 ----ACE-------- $8.01
21-Jul-05 -----ACE------- $8.13
22-Jul-05------ACE ------$8.05
What I need is a report that will show me the last ordered price of the product.
Date of Order --Product-- UNIT COST
21-Jul-05------ XYZM ---$9.24
22-Jul-05 ------ACE ----$8.05
How do I get a query that will allow me to retrieve this information? I'm still in training and need step by step intructions. Please Help!!!!
View 5 Replies
View Related
Mar 10, 2006
Hi,
I am new to microsoft access. I am trying to develop a data base that will store data as well as return means and SEMs. I have multiple parameters entered into an access table. The fields are Group Number, MAP, AP, and KV. I have a query to return the averages for each group, but i am having trouble making a query to return the SEM. the formula for SEM is
SEM = Standard Deviation / (Count ^ (1/2))
This should be easy because access will return Stdev and Count, but when I try and enter the formula into expression builder, all hell breaks loose. Any advice would be greatly appreciated.
Cheers,
DH:confused:
View 1 Replies
View Related
Aug 30, 2005
Good Afternoon,
I am having a little trouble creating a database. I am using Access 97 if that helps.
I have created two tables the first table is a list of brokers (Brokers) with the following columns: - User Name, Contact Name and Broker Name. The second table (Main) is where I would like to store all of my records for each call recieved.
What I would like to be able to do is on the main form is to have a combo box for the user Name and then to have two other text boxes to auto populate the Contact name and the Broker Name once the User Name has been entered. Then all 3 fields will store the information in the Main table. Hopefully this all makes sense. I have been trying to use the help within Access but to no avail!.
Any help or assistance would be great.
Thanks in Advance.
View 2 Replies
View Related
Mar 16, 2008
ok first i am new to access and databases so bare with me.
i need to create a table that holds 6 doctors and 4 nurses appointments that start at 9 0clock last 20 mins finish at 5.oclock. (this bit i can do)
i need to use this over and over again so each day there is a new appointment table
how do i date the table and get it to change everyday and clear ready for the new day
can i do this???
or do i need to create a new table every day??
View 3 Replies
View Related
