I have several tables and queries based on tables and cannot get them to generate the results I want when querying them (just one record shows when many should be showing), so this leads me to believe there's a problem with the way I have set my relationships;
tblStaff (Holds lists of all staff and their contact details)
queryCoordinator (Filters list of staff from all staff list based on whether they are a projectmanager)
queryLeader (Filters project leaders from list)
queryMinion (Filters minions from list)
tblProject Contains all details from project
I have the staff queries so I can choose from a list of names that are relevant to that job in a form, so we don't have to look through a huge list of staff.I then want to use a query to pull all this information together (project details and contact details of each person involved) so that I can print a single record on a report, however the query isn't working correctly and returns only one project record, not all of them.
There is only one leader, one project coordinator and one minion per project (as far as I am concerned) and I want to reflect this in the query by providing their name and contact details from the single staff list.
Is the relationship between the project and the individual roles Many-to-Many or One-to-Many. I have many projects, and many staff, however each project only has one leader, one coordinator and one minion. I'm thinking One-to-Many, however when I relate the tables together, using the name as the relationship on each form this is where I have problems.
I need to hold information in a staff table about a person's Line-manager.
My initial idea was to just have the Staff Table and use the PK of a member who is a Line-manager as a FK in the Line-manager field in the same table for their staff.
I then came across a problem.
I need to be able to select all the staff under a Line-manager. For example a head of department may have 3 team leaders who each manage 10 staff. I need to know all of them, not just the 3 team leaders who are directly managed by the HoD but their staff as well.
As it happens there is no rigidgly defined managment structure per se for this organisation which makes things even more difficult.
I assume there is a reasonably standard way of doing this as all organisations have some form of line-manager/staff relationship. Anyone care to enlighten me?
I have a database of staff members. There is a table with the details of staff; name, dob, address etc.
I need to set something up that will record the training that staff attend and then be able to print a report of who attended the training session and also a report of training sessions individual staff members attended.
I need to set up fields in a table for date, topic and presenter and then I need to somehow mark off staff members who attended.
Access 2007 is behaving very peculiar concerning sizing of forms. For example, some open at an enormous size (25 plus inches) when the width size in the property box is 8 inches. I've tried various combinations of Auto Center, Auto Resize and Fit To Screen -- all with bazarre, unpredictable results. Does anyone know if there is an "issue" (bug) in Access 2007 Trial concerning form sizing? Thanks.
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
Table 1: contains staff names and contact numbers Table 2: contains training above staff have been on or need to go on Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
Hi everybody, Recently I created a database for staff details in my company and I used OLE Object in Data type to make it. The problem now there are more than 500 employees in the company so it is very difficult to scan one by one photo. Is there any way that once I click on command button photo will be detected from scanner and saved directly in the field instead of doing it manually.
I'm relatively new to Access and would like a few pointers...
I have been asked by work to create a schedule database. We would like to be able to put in dates and see who's working on that day, pull up an individual and see his/hers rota for a week. Also see if they have meetings, annual leave, sickness etc.... We want it to be a very primitive WFM tool. Is this possible and is it relatively easy? The database doesn't have to create the shifts, they will be imported from Excel....
Which leads me to believe I need to think of it differently to excel... Attached is an excel rota, which would need to be importred - but maybe I need to be think a lot differently.
I know thats a big question but any help would be appreciated, or pointers to more resources... Thanks Neil
I need a query to tell me how many staff are on holiday on the same day..here what i have so far.
A query with name, startholsdate,endholsdate, totaldays, etc, etc,,
records showing; employee A books 01/01/06 to 14/01/06 and employee B books 10/01/06 to 24/01/06 C books 01/02/06 to 10/02/06 and so on,
I need to show how many employees are off on what days eg employee A and B are on hols between 10/01/06 and 14/01/06, so only 3 more staff may take days off between the 10th and 14th. I have tried all sorts of calculations but to no avail? can anyone help..
i am unsure as to whether this can be acheived in a database..
basically this task is almost calendar like.
the company operates 7 days a week. i have a table of company drivers and each has their own driver ID.
my user would like to store what time each driver comes into work each day and what time each driver leaves. this needs to be quite flexible however because some days certain drivers wont work, but each day..if a driver has a 'come to work' time then they will for sure have a 'leave work' time.
each driver also pays rent to the company once a week. within this calendar like system i would also like to store on what day the driver paid what amount towards their weekly rent.
i cant quite imagine how this can be done in a database. but if these kind of applications are common, i would be most grateful for some direction.
thank you.
one more thing..
i would also need to record during each day what time the driver left and returned from lunch (sometimes our drivers leave for a few hours during lunch time)
i want to store this because if they miss a job while they are on an extended lunch break im gonna roast em.
i need to design and build a access database for school where staff for a business could request holidays with there holidays then appearing on sum sort of calendar which can be viewed.
does nebody know where i can find an example to help me with my desing?
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
I am trying to define a relationship between tables. However, the unique nature of my data doesn't seem to be allowing typical relationships. I am not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. Do I need a Foreign Key? FYI, I am only querying the data - no updates.
I have two tables that I am able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables. For example:
Table1.ColA has a 5 character string. Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.
IMPORTANT FACTORS: - Table1 contains data at a SYSTEM level. - Table2 contains the parts that make up the "SYSTEM" in Table1 - The PARTS in Table2 can be in 1 to many SYSTEMS from Table1 - Table1.ColA is not unique by itself (it's part of a composite PK in Table1). - Table2.ColA is not unique by itself (it's part of a composite PK in Table2).
I have a db with two table. Table1 Trade has the following among other fields: RefNo (autoNo) PK, TradeDate, HostName, Methodology, etc.
Table2 Market has the following among other fields: MarketDate as Date ( ), USD/EUR_Rate, USD/CHF_Rate, USD/BRL_Rate, etc.
I need to link both tables so that the TradeDate correspondes to the MarketDate. what is that say on TradeDate if it is on 15/01/06 I can see what the Market values for the MarketDate corresponding to that TradeDate.
I tried to link MarketDate as PK on Market table2 to TradeDate on Trade Table1. It seems to work but it does not seem right as sometimes I get errors in entering data, etc on the Market table2. The MarketDate is unique meaning only one data of values for a given date, they can be modified but not duplicated.
Can anyone help, on the best solution for the links to work?
I feel confused about something and I have the need for assistance because solving this small thing would make my life easier. I very much appreciate you taking the time to read this.
I want to normalize my database as much as possible.
I will make a make a form for Breakpoint containing two comboboxes. The top combobox will contain all Modules and I want the second combobox to contain only the Functions that are related to the selected Module from the first combobox.
I know this can be programmed, that's how I do it now: in the onChange-event of the first combobox, I load the content of the second combobox.
However: what I would like to know specifically 3 things: --> what is a common way to solve this? --> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically) --> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?
Tables: I have 2 tables, there are same fields: Name, Date, and Invoice #
I make a relationship between 2 tables, connect Name, Date, and Invoice # And click "Enforce Referential Integrity", "Cascade Update Related Fields", and "Cascade Delete Related Records".
One-to-One.
After that, I go table 1, create a record, then close it, then open table 2, the record I created in the table 1 is NOT over there. Does the relationship create record? It only update and delete. What about Add ? Please let me know about it. Thanks.
Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'. This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie. Is there any possible solution or is there a more simple way to solve the problem. Many Thanks in advance Dan Parker
I work for the school dist. and we have 2 tables for 2 different groups. I need to pull data from them both and create a new Query. Example Table 1: Name, address, phone
Table 2: Name, address, phone
(No similarities in ether database, and we'd like to NOT export data)
I'm hoping it's an easy fix that I've overlooked. Michelle
Hi guys i know this is a simple one but i have been struggling for ages now i would very much love some help. I have a customer database that has a number of tables customer, quotes and materials. I keep getting errors all the time from the database when i try enter information im not sure whether it is my relationships or my tables setup.
The first table that will be displayed is the customer one once all the information has been entered into that one then the customer database should be linked to the quotes table and then from the customers database it drops down into the quotes with the little + sign and then the user enters all the information to do with the job but the field Materials must call a dropdown box, and then display the contents from the materials database, but only the material name.
If anyone can help please do as i have been struggling with this for over a week now and have tryed to solve it myself but with no joy Thanks in advanced i have included the database link below:
I am a newbie to access and i am working on a project that has two tables the primary table has for example the following fields:
firstnameID(primary key) data type auto number Firstname data type text
the second table has for example : LastnameID(primary key) data type auto number Lastname data type text FirstnameID (foreign key)data type number
I created a relationship between the FirstnameID in the primary table and the FirstnameID in the second table .. My problem is this whenever i try to view the relationship when i open the primary table it don't show the relationship , The Field FirstnameID in the secondary field is always Empty the only time the relationship is created is when i manually insert the autonumber that is generated in the FirstnameID field , but i thought that since a link was created then that field would have automatically be inseted with data , am i assuming wrong or am i doing somethingn wrong? how do i get the autonumber to be inserted in the field automatically to create the relationship? Please Help
am currently havin a problem with a report but i fink the main problem is with the relationship between my tables!. so i have built a scholarship system. I have details like school certificate,A levels result and degree information.
i have built a report which retrieves the school certificate results,A level and degree results for a particular candidate, you have to enter the id for a candidate to get the details.
But the fing i have notice is that all these 3 tables must contain records so that all the information is displayed, for example if a candidate is applying for an undergraduate degree for the first time, he'll obviously be having only school certificate and A levels results....The report doesnt display anyfing!!....But if for a particular candidate having all three records in the 3 tables, the report displays correctly!.
Is there an option for fixing the cardinality among tables so that even if a candidate doesnt have one among the three information, the report gets displayed??
I have been trying to design an Access 2013 desktop database to report on user access to shared folders within the company I work for. Acess to folders is granted by users being added to a permission group and groups being added to folders.
What I want is to be able to query the data and find out which users have access to which folders. My tables are currently:
tblGroups GroupID: PK Autonumber GroupName: Short Text GroupType: Lookup (Permission; Distribution; Mailbox Sharing)
[Code]....
If I do have to have a junction table is there anyway it can autopopulate from the other data entered?
I have created a many-to-many relationship but am now having trouble figuring out how to add new data.
I have a table PEOPLE: ID First Name Last Name 1 Participant A 2 Participant B 3 Participant C
And I have a table BEHAVIORS: ID Behavior 1 Yelling 2 Grabbing 3 Smearing feces
And I linked these together in a many-to-many relationship in a table PEOPLE_BEHAVIORS: ID Participant Behavior 1 A Yelling 2 B Yelling 3 C Yelling 4 A Grabbing 5 B Grabbing 6 A Smearing feces 7 C Smearing feces
All that's good to go. But now I need a table so I can count how often these behaviors occur every day. I need to link PEOPLE_BEHAVIORS to a new data table that will count how often I see the behaviors linked to the people every day. So my table should look like:
ID Participant Behavior Date Frequency
But I should only document the frequency of the behaviors linked to that participant (so for Participant A, I should have a space for yelling, grabbing, and smearing feces whereas for Participant B, I,d only have a space for yelling and grabbing). Is it possible to create a table that will basically partially create records for me? So if I open the table to add data today, I'd see the following and fill in the frequency blanks?
ID Participant Behavior Date Frequency 1 A Yelling 6/16/14 2 B Yelling 6/16/14 3 C Yelling 6/16/14 4 A Grabbing 6/16/14 5 B Grabbing 6/16/14 6 A Smearing feces 6/16/14 7 C Smearing feces 6/16/14
