Tables :: How To Report On Many To Many Relationship
Jun 2, 2015
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?
(Access 2013 is the version I am using)
View Replies
ADVERTISEMENT
May 29, 2014
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.
View 6 Replies
View Related
May 5, 2013
I have 3 tables.
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 ?
View 10 Replies
View Related
Mar 4, 2013
I am about to set up a database but wanted to check the relationship of the main tables before I add to it. I have attached the relationship design
For a PROJECT, there can be many TESTS, for a TEST, there can be many PRODUCTS
Is my design reasonably sensible?
View 6 Replies
View Related
Aug 22, 2004
Hi,
Hope someone out there can help me with this.
I have a CITY table. Each city can have 1, 2, 3, or 4 technicians.
The TECH table contains (up to 7) phone #s for each technician.
I need to produce a report that displays the phone #s in a matrix type arrangement per city, some thing like:
CITY1 TECH1 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH2 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH3 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
CITY2 TECH1 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH2 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
TECH3 Phone1 Phone2 Phone3 Phone4 Phone5 etc.
Any ideas?
Thanks
View 1 Replies
View Related
Jun 19, 2013
I have a report, based on two tables which I have combined into a query.
The first table is the invoice date & ref, and the second table is the invoice amounts & descriptions, which could be more than one.
I have based the report on the underlying query, and I want all the many invoice & amounts to appear on one invoice, but I get one invoice for each of the many sides of the relationship.
I have a related key field in each table, with a common ref and referential integrity and cascade update set, but it still produces one invoice rather than a combined.
View 3 Replies
View Related
Sep 9, 2013
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.
View 1 Replies
View Related
Dec 11, 2012
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?
View 6 Replies
View Related
Sep 19, 2013
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).
View 2 Replies
View Related
Jan 17, 2006
Hi,
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?
Thanks
dfuas
View 14 Replies
View Related
Feb 27, 2008
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'll focus in on a small part of my database:
----------------
| tblBreakpoint|
----------------
| BreakpointId |
| Remarks |
| ... |
----------------
each Breakpoint can have only one Module and a Module can have many Breakpoints
----------------
| tblModule |
----------------
| ModuleId |
| ModuleName |
| .... |
----------------
each module can have only one Function and a Function can have only one module
----------------
| tblFunction |
----------------
| FunctionId |
| FunctionName |
| ... |
----------------
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?
View 3 Replies
View Related
Oct 23, 2005
Hi:
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.
View 2 Replies
View Related
Dec 20, 2004
First time user!!!
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
View 2 Replies
View Related
Jan 22, 2006
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:
View 3 Replies
View Related
Jun 4, 2007
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
View 3 Replies
View Related
Jan 9, 2008
Hello guyz,
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??
Kind Regards!
View 3 Replies
View Related
Jun 16, 2014
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
View 1 Replies
View Related
Dec 10, 2012
I have to make a database for my class project, and I am having problems with the relationship between tables. I have these tables:
Application
advertisement
employment agency
listed position
In the advertisement table I already linked listed position and employment agency. It shows the position name from the position table and in the agency column shows the agency name. When I link the application table to advertisement table and select position and employment agency column, it just shows up as the id numbers in the application table. How can I have it show "engineering| we find you jobs" for the table?
View 4 Replies
View Related
Aug 8, 2007
Hello....
Here is what I am doing. It is an inventory database that also is an order tracking per se database.
When you are entering in the order, the top part is the vendor with an order number, date etc (will show table later).
The subform is the order details. This is the tricky part that I am having problems with.
In the subform I also need to have the unit that is placing the order for that particular item.
IE
product 1 pens black ball point unit=exams
product 2 pens red ball point unit=admin
product 3 paper legal color white unit=personnel
I can get it to work but the unit part is what is driving me nuts.
I am attaching a very stripped down version no queries etc....
Thanks
View 6 Replies
View Related
Dec 8, 2005
I've split a table in which a number of fields weren't always relevant to enter on every occasion and linked them in a one to one relationship, the idea is to increase efficiency and help reporting. Both have the same name primary key linked with referential integrity and cascade update related field ticked.
I've used a control button on one form relevant to that table to open a form linked to the other when the user requires to add the extra info, but for some reason it doesn't automatically put in the key in the other form and therefore link the tables.
What I want to do is have the original form link to the additional info table and auto updates the key (link field) in that table if the user wishes to enter extra info.
Can anyone help, been stuck on this for a bit too long now. Thanks!
View 4 Replies
View Related
Dec 15, 2006
Can anyOne help me how to Normalize and provide Relationship among 12 Tables in a good way.
Purpose of Tables:
1. Students Registration
2. Subject Registration (Definition) for eight Semesters
3. First, Second, Third, ... Eight Tables of the eight Semesters
4. Entering Marks (Points) for the selected subjects of each student's semester
5. Storing the Result (Pass, Fail, Second Chance, ...) after the marks entered
Note:
1. Consider the Result Table
2. All the above will be through the Forms.
View 1 Replies
View Related
Aug 5, 2015
I have a table with all my contacts. Manufacturers, distributers and outlet shops. I want to be able to make relationsships that show which manufacturers sell to which distributers and outlet shops, but at the same time I also want to be able to see which manufacturers are being sold by a particular outlet shop or distributer.
I am reading everywhere that you need to make a table to link the 2 tables together for every one relationship you have and this I understand. Most of the time it is relations between 2 tables that you want to establish. But in this case all these companies are in the same table. Can I still make a many-to-many relations ship between different items that are all in the same table using one single extra table that holds these relationships?
View 6 Replies
View Related
Oct 26, 2012
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.
View 6 Replies
View Related
Jan 26, 2013
I have a database, originally made in earlier version of Access, recently converted into Access 2010. Due to the high number of tables relationship page is quite crowded. My problem is that I can't move the page to the most left-upper corner of the relationwhip page where I still have relations but they simply can't be seen and edited; I can't move the page any further with the silpers.
View 5 Replies
View Related
Feb 17, 2013
the line setting up a property database and i cant figure out how to set up a suitable relationship between 2 tables .
Table 1 is for leases, which can have up to 4 tenants (each has seperate field)
Table 2 is a table for each tenant
now I need to relate the two, each lease can have multiple tenants at the same time and each tenant can have multiple leases over several years.
View 3 Replies
View Related
Nov 13, 2013
I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.
What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?
View 5 Replies
View Related
