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
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.
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.
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 ??
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)
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!
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.
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?
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
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:
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:
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 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):
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.
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...
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?
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..
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?
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.
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?
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
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?
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?
Hi I have created some reports but find difficult to keep permanganate page set up for that reports. I go to design view and do page setup with required margin and Portrait or Landscape set up and preview that reports looks fine. After saving again I check still its fine but once I close that database and reopen again some of reports page setup becomes Portrait with 1" margin on all sides (Not All reports). I cannot solve this. Can any one help?? Funny part is even I copy that report and change data source of that report it changes page setup :confused:
I've just been given the task of "fixing" a too-big Access database. It's actually about 250 tables in 29 .mdb files scattered over a filesystem. But I'm very green at Access (although I'm pretty OK at SQL in general). So here's my question: how the heck would you all explore/learn about this Access setup? Is there an automated way to cruise through all the .mdb files, get lists of all the tables, and even lists of field names? What about getting a list of the type & relationship of each field?I know I can get some of this by opening a file, right-clicking on a table, and selecting "Design View." But doing this 250 times sounds painful, and the properties window cannot be printed, anyway.Any tips about this?-Tony
