Form To Table Setup
Jul 25, 2005
I am trying to build a very simple but effective database that I can use to monitor who is working where within my factory.
I have set up a series of tables have linked them up the best way I know how, I dare say I have done this wrong but I have done it anyway (open to suggestions on improvements).
What I am trying to do ultimately is to be able to calculate how many people I have in each area etc… to calculate if I have any vacancies…
The set up is as follows:
Each employee has a job role
Each employee has a shift
There are three shifts associated with each Production line
Production lines are based in a specific Dept
Dept are based in specific areas
The only things that will really change is the employee, they may get moved from one role, line, dept etc…
As I said I have put some tables together, but am not sure If I need another table to store all the info for each person, by this I mean…..
Do I need to create a table to store each persons info, dept, area, line role etc…
I want to have a form that I can enter each populate, which will let me make the relevant links between people, lines etc…
Can anyone help me with this please…….if someone would prefer to do this as a freelance project I am happy to listen to offers
Cheers
Andy
View Replies
ADVERTISEMENT
Mar 20, 2008
Alright, so here's my layout. I'm working on creating an inventory database with forms to enter new data and edit that data.
I already have a table for the new data, and a form to enter the new data into that table. Each new entry has it's own unique ID.
Now here's the tricky part.
What I would like to do is this:
Part 1
I want to create a sub-table of any EDITED entries. Each of these entries would be tied to it's original entry but it would have it's own unique ID number. So, for example, if Part 1 comes in, it would get an ID number like 134. Each time Part 1 is edited, those new entries would get ID numbers like 134 A, 134 B, 134 C, etc.
Part 2
I want to create a form based on a query that will search based upon part number, and once the entry is found that needs to be edited, it will do one of two things. If there are no edited entries yet, it will use the original entry as a base and create an edited entry based upon it. OR, if there IS an edited entry, it will pull up the most recently edited entry (by date) and use THAT as a base for another new edited entry.
It would then save the NEW edited entry to the Edited Entries table.
So, if Part 1 hasn't ever been edited, the form would pull up Part 1 and store any edits to it in the Edited Entries table with ID 134 A. Now, if Part 1 was edited AGAIN, it would pull up the edited entry 134 A instead of the original. It would then save any changes to that entry as 134 B.
I know what I want to do, and I'm pretty sure it can be done--I'm just at a loss as to how to structure it.
View 3 Replies
View Related
Sep 7, 2005
I have ta table that has the following fields:
factorID (Primary key, AutoNumber)
supplierID (Foreign key to suppliers table)
categoryID (Foreign key to Categories)
Factor
I'd like to ensure that there is only one Supplier/Category pair in the table, but I can't figure out how to set it up to achieve this. Any help will be much appreciated. Thanks.
View 5 Replies
View Related
Feb 3, 2006
I've thought I had a decent understanding of database normalization but I've come across a fairly simple problem that I'm having difficulty resolving.
I have a database which I use to track submittals from companies.
Originally it was set up as follows:
tblCompany
Company_ID (PK)
Company (No duplicates allowed)
tblSubmittals
Submittals_ID (PK)
Description
Date
Company_ID (FK)
Now I've decided that I want to add a contacts table with fields such as first name, last name, company, etc. My dilemma is that in the Submittals table I need to keep track of which company the submittal is from but I don't care WHO it is from.
So I created another table as follows:
tblContacts
Contact_ID (PK)
Company_ID (FK)
First_Name
Last_Name
This will work except that when I add a new contact I will have to add code to see if the company exists in the tblCompany before I add it.
It doesn't seem like this would be the correct way to set this up because I'm adding the same data to 2 different tables.
I'm sorry if this seems like a stupid question. Thanks for your input.
View 5 Replies
View Related
Mar 29, 2006
HiNew to the forum and pretty new to MS Access.Here's what I intend to do.I want a table for Special Tasks. This will include TaskID, Date, Time, Reason, Description and Dependencies.I want a table of staffFor each task each member of staff will have a tick box for "Needs to Read" and "Has Read".In a form I want to be able to enter new tasks using the task table, I also want to use a subform that allows me access to the list of staff and if they have read the task or not.There is probably an easy way round this but at the moment it's just not clicking into place.Can someone point me in the right direction for table setup and relationships ??
View 1 Replies
View Related
Dec 27, 2007
Hello, I am having difficulties normalizing my tables. So far, i have 4 tablestblODF:- ODFNumber (PK, manually enter ID)- UserName (txt)- QueueID (number)- ScanDate (date)- Status (text)tblEmployee- EmployeeID (pk, autonumber)- UserName (txt)- LastName(txt)- FirstName(txt)- Extension(number)tblQueue- QueueID(Pk, autonumber)- Queue (txt)tblStatus- StatusID(pk, autonumber)- Status(txt)I am also having problems setting up the relationshipsI want to setup a query and i want all these data to appear:- ODFNumber (txt)- UserName (txt)- ScanDate (date)- Queue (txt)- Status (txt)I am really stuck in setting up the tables and their relationship. So in short, help:DThank you, (some of you might recognize this project)
View 4 Replies
View Related
Mar 17, 2008
I've approached this once before but I never did figure out how to go about setting up this table. If anyone can steer me in the right direction I'd appriciate it!
Overview: I have 25 products that I track repairs on. I am given quotas every month for some or all of those products. All this data needs to be kept vs overrighting last months quota information with this month's.
What I have done was created a table with columns of: ProductID, 1/08, 2/08, 3/08, 4/08, etc... and ran that out for a years time. So, that is how my table is currently formatted...
Now when I need to pull out information I have a query that looks at a particular month column and compairs that to my work table. However, every month I need to go into that query and change the column name it looks at in my quota table. This again works, but is it the best way? I also need to pull out history information month by month for a production graph. The query I had to build again requires me to manually go in and enter new month information every time it's needed.
It just seems to me that there is probably a better way to format this quota table. If I remember correctly someone suggested to me that I needed to normalize this table; and the format suggested was more like:
productID; date; quota
But instead of just having 25 lines I will now have 25 each month. That just seems like it'll get excessive real quick...
SO; if there are any great ideas for setting up this quota table vs what I am currently using I would really like to know!
Thanks!!
View 14 Replies
View Related
Jun 10, 2005
any ideas on how I go about this:
I want to keep track of projects and people on projects but most of all money that people will get.
1 project my have many individuals on it
1 individual may be in many projects
so i guess i need 2 tables with a junction table linking them both up (is that right?)
thats the easy bit....
On each project there will be a deposit (e.g. Project A is left with deposit of £100). i need to work out firstly 20% of that deposit and then split that 20% between the individuals who are involved with the project. (e.g deposit for Project A is £100, so £20 gets split between the individulas who are assigned to that project).
So I want my database to firstly look to see how many indviduals are assigned to a project and then split the money accordingly.
firstly where do i record the individual split amounts and how do i work it out?
to make things a bit more complicated i also need another table with expense claims. this i guess is a One to Many relationship with the individual table. but what i want to do is be able to record the total balance between expenses claimed and the deposits splits mentioned earlier.
all this and it needs to be in a form that makes it easy to input the data.
i have the general layout in my head just cant work out how to go about linking the tables up and how to do the calculations
thanks in advance for any help
KTJ
View 2 Replies
View Related
Sep 12, 2006
Hi All
I am having trouble setting up a table to contain this information and then putting it into a query and a report.
Members (many names)
Race Class (a member could be in 1 or more class)
Annual Membership (1 per year)
Insurance (1 per year)
My report I want to look something like this.
Race Class____Member Name____Current Membership Y/N___Current Insurance Y/N
Motoard:______Billy Brown_______________Y______________________Y
_____________Tommy Turtle______________Y______________________N
Road Racer:___Billy Brown_______________Y______________________Y
_____________Fred Bloggs_______________N______________________Y
Any suggestions would be really great.
Many thanks Tee
View 1 Replies
View Related
Dec 1, 2006
Hello,
I am rather new to Access and looking for some help with my table. I have a report that I do, that I used to do in Excel and was wondering if it was setup right or if there was a better way of doing it.
From looking around I noticed that people usually have mulitple tables and such, while mine is all in one. I see there is a table analyzer but am not sure if I should use that, or what the importance of having it split up.
I have attached my table in XML if anyone wants to share opinions on how they would set it up or what they would do.
Is there a bad thing to having it all in one table?
Thanks for any insight or help.
View 5 Replies
View Related
Feb 20, 2008
Can I setup a table to have 365 columns for dates &days of the week for the entire year? I would like to relate this table to another to show how material was delivered over the course of the year, including those days and weeks when material was not delivered to all my storerooms:confused:
View 4 Replies
View Related
Nov 7, 2007
Hello,
ACCOUNT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED
These are fields that are imported from Excel. I have to import 500-1500 rows each week which contain the data I need. Then I must, by using a form, go through these "Service Calls" and mark some that give the employee credit for the call and some that are unique calls. Meaning if they have serviced the same person 5x in the quarter I can only count that once.
There can be muliple people on a service call so I have these:
EMPLOYEE1
EMPLOYEE2
EMPLOYEE3
(Entered_By field contains the employee who recorded the call and they would have gone on the service call)
Then I have to record who they contacted regarding the account. The SHORT_ACCOUNT_TITLE will often differ from who they have contact regarding this account. So I have this field:
CONTACT
Then since muliple employees go on service calls together some may have already contacted this customer aleady. I have to keep track of employees unique customer contacts. So I have these fields:
CREDIT1 (checkbox for employee listed in the ENTERED_BY field)
CREDIT2 (checkbox for EMPLOYEE1)
CREDIT3 (checkbox for EMPLOYEE2)
CREDIT4 (checkbox for EMPLOYEE3)
Then I have to keep track of service calls regardless if they where unique ones or not. So I have this field:
ELIGIBLE
Then I put a notes section in so when I am filtering the data and finding what calls where good calls and need to make changes or whatever I can have a place to put that:
NOTES
Finally since some of these customer calls involve large amounts of customers (group meetings, etc) I have this field to type in a number of additional credit:
ADDITIONAL_CREDIT1
ADDITIONAL_CREDIT2
ADDITIONAL_CREDIT3
ADDITIONAL_CREDIT3
------------------------------------
So since I have to import Excel rows that contain the data I start with I am unsure of how to set this up and normalize it. I have attached my form I have used to find what fields I would need to show you more of what I am trying to do.
This form allow me to filter data that shows up in the subform. Then I can just double click it and bring up the record. Then mark the ones that count.
I am looking for any suggestions. Thanks.
View 12 Replies
View Related
Sep 19, 2005
Good morning,
I am having problems figuring out how to set up my table/query structure for a series of surveys that are based on specific user groups. In a nutshell how do I set up the relationships for the Questions, Employees, and Answers if I have the following tables (simplified):
tblQuestions
--------------------
QuestionID (P)
UserGroupID
Question
tblEmployees
--------------------
EmployeeID (P)
Name
UserGroupID
tblAnswers
--------------------
AnswerID (P)
QuestionID
EmployeeID
Answer
The functionality requirements are:
1. Create a set of questions for two or more user groups (each set contain different questions).
2. Assign Employees to a specific user group.
3. Employees are able to enter the DB to answer the questions within their user group.
What should happen is the employee accesses their set of usergroup questions and enter the corresponding answers. Each time I try to set up the relationships however, the answer field ends up being uneditable.
Any help you can give would save what little hair I have left.
Thanks!
Bob
View 2 Replies
View Related
Apr 22, 2013
I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.
There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.
For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.
I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...
View 1 Replies
View Related
Nov 5, 2013
I want a List Box on a Form that takes its available drop-down values from tblDiscipline and puts the value selected into the Discipline Field of tblEquipment.
The problem is I set the Control Source of the List Box to tblDiscipline (so the List Box can use these values for the drop-down selections) and when I make a selection it tries to put it back into tblDiscipline instead of tblEquipment, because I can't find anywhere to set the table that the result should be Bound to.
How can I set up a control, such as a List Box, so that it gets its values from one table but returns them to another?
View 1 Replies
View Related
Jun 7, 2013
I have recently created a database meant for managing inventory. I have started writing queries and building forms, and a lot of it is running smoothly, but I am having a few snags.
To give a brief background, I am currently designing a form in which a user will fill out when an incoming part comes into our company. The form NEEDS to ask the user for at least the PartNumber and the PONumber, but the way I have my relationships set up, I am not exactly sure how to design the forms (not the physical layout, but where the data goes). So when I have an incoming part, will I use the PartPOID to figure out which part number and PO number it is? I am not exactly sure how to make a form for the user that is not confusing.
Attached is a picture of my relationships and my database.
New Compressed (zipped) Folder.zip
View 14 Replies
View Related
Dec 9, 2013
I am opening a new mail message in outlook using a button in a form.I want to change the "from" email address to a specific inbox (NOT personal email). Even though the email address is switched, after I hit sent and check my sent items - the email is actually sent from my personal email address and not from the inbox email address i wanted it to be sent from. I have access/permission to sent emails from the other inbox.
Is there a way to make sure emails are sent from specific email addresses and not ONLY personal inbox.What I read online is probably because of some "network" connection issues. Is there a way refresh connection between access and outlook? I am not even sure what the problem is.
View 1 Replies
View Related
Apr 3, 2014
I'm wondering is it possible to create a multiple parameter query which will return results even if you leave some of the parameters blank ?. I'm trying to set up a Form which will allow users to select parameters using combo boxes but at the moment you need to fill them all in or you'll get no results...
View 10 Replies
View Related
Mar 7, 2005
Trying to set up a database in access to be used for customers to track payment history problems. how does this sound???? ...
Table 1 would have fields: account # ; account name; region
Table 2 (would need a separate table? ) With fields: account #; date entered; entered by; Comments:
I Need to be able to enter multiple entries for payment history comments as this will be info entered by date to track
So data would look like:
Account #: 111-11111 Account name: test company Region: new york
Entry date: 3/5/05 Entered by: Joe Shmoe Comments: Company short paid, sent letter 3/5/05 requesting additional $$
Entry date 3/10/05 Entered by: Sue Smith Comments: Sales office says there is a problem with the bill, extend grace period.
I can’t put this in one table right?
I will have many entry dates & comments for an account that I would like to print out in a report.
Wouldn’t I need 2 tables with a relationship linked by account #?
Thanks... I will be around the rest of the day to respond to any commments..
please help me with setting this up..
View 9 Replies
View Related
Jun 23, 2005
I'm creating a data access page that can be used over the internet to add information into the database. The database is password protected. The functionality currently is that when a user accesses the data access page, they need the database password in order to submit information. We don't have a small number of users and giving all these people the password to the database would comprimise the security of the system.
I am looking for a functionality such that the people that edit the database, either by the front end application or by opening the database directly to be password protected. But the data access page not to be.
Any ideas on how I can achieve this functionality?
View 8 Replies
View Related
Aug 24, 2006
I have a database for an event with 2 main tables.
Parents and Children of the parents with a one to many relationship.
I need to be able add the table number that each parent and child will be at (not the same table as parent). I could add another field for the table to each database but not all the entries are attending the event.
Could someone point me in the right direction as to the best way to design this.
WarrenB
View 1 Replies
View Related
Jul 23, 2007
I have a database that contains information on football games. There are many
different games from many different seasons, teams, opponents.
I will eventually have a fairly large number of different reports and forms
that will want to be viewed but filtered by the above three catagories.
Usually all of the reports at any given period of time while running the
reports will want to be viewed off the same criteria as the one before. (i
don't think you would want to have to fill out the parameter manually for
each report or you will be entering redundent data as the parameter report
after report.)
I was thinking of having a form with 3 multi select list boxes, [seasonlst]
, [teamlst] , [opponentlst] and having each form filter information based of
the values entered in each box.
Is this the best way to do it or is there a better way?
View 1 Replies
View Related
Nov 14, 2005
Hi
I have a client whose working week runs from 0700h on Monday. They do a lot of overnight stuff and so have this cutoff. I am doing a time sheet db for them. All the time sheet records have a starting date and time, and an ending date and time. The end date defaults to the start date but can be updated if an overnight shift. I separated the date and time since I remember issues using the general date format.
I am puzzling over how best to organise my queries so that I can pull up weekly reports that tie in with their week criteria.
Do I build my own table with the date for Mondays over the next few years or is there some smarter way to identify Mondays and then say I want everything between 0700h on that Monday and 0659h the following Monday?
Any thoughts or pointers would be most appreciated.
Thanks and best wishes
View 2 Replies
View Related
Mar 28, 2007
Hi all,
I opened my database on a newly installed access programme (same version) to notice that the design control page settings had reverted to default for all my forms and reports. I need them to be A4 and not letter when it is opened on any access system. How could I go about doing this? In Code?
Cheers
Tania
View 5 Replies
View Related
Jan 11, 2008
Hello,
I have a problem to solve using ACCESS 2007. I have an 'appointments' table and a calendar. So far as I click on any date on the calendar it is supposed to show me the appointments for that specific date on the form (this info is gotten through the 'appointments' table which has the date for each appointment). Appointments have to be separated by a 20 minute difference (8:00am, 8:20am, 8:40am, and so on....). I have to have about 30 spaces or appointments to be filled up per day.
So overall, the customer wants to have 30 spaces (already filled up with the times) so he could fill out the rest of the appointments info in each needed space as he sets up appointments.
Do I have to create 30 records for each day? How would I do this? What happens with the paces (records) that are not filled? Wouldn't that be a record that is not used (wasted record in the table) ? Is there a more efficient way to do this?
Thank you very much everybody.
View 1 Replies
View Related
Jun 14, 2006
Hi all..
I have a simple database setup...
When I do a test query on the tblMain.. all I get are numbers.. How do I change the numbers to text?
Thanks again
R~
View 2 Replies
View Related
