Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?
Nov 21, 2012
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
View Replies
ADVERTISEMENT
Apr 22, 2013
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
View 3 Replies
View Related
Jan 12, 2005
I hope the title actually conveys what I'd like to do.
I want to assign records on a subform to a group header on a form. For example, I have groups A, B, C, and D and I want the records on a subform to be assigned to groups A, B, and D. (The number of groups and their names will change so I can't simply use an "A," "B," "C" option box.) My idea is to have a Multi-Select List Box on the main form and choose all the groups to which the records on the subform need to belong. So I'd select the groups, enter the records on the subform, then create a another record on the master form and assign records to another group.
Is it possible to make this happen without a lot of programming?
View 2 Replies
View Related
May 11, 2013
I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
View 1 Replies
View Related
Oct 8, 2012
I am trying to put together a database that tracks individual clients and what products they selected for a finance company. I have setup the CLIENTS list but because each client will have different (and multiple) services I wanted to add the SERVICES list as a separate item and then link each product to the individuals that each record relates to. There may also be two clients on the one product so again it would be ideal if the product (i.e a Home Loan product) was setup in the SERVICES list, and then I could link One or Two clients to that individual mortgage record using their data which is stored in the CLIENTS list.
View 2 Replies
View Related
Oct 10, 2007
Hi,
I have been thinking of allowing our users to be able to link records together within our database.
We have a CRM system that lists customers and their details of mortgages and other insurance related products which they have. Some entries maybe linked in some way, possibly other family members etc.
My question is, is it possible to perform this kind of action? So they can then see relationships between either related records (ID ref etc).
I'm sure I've seen this in SQL based applications, but haven't come across it within Access.
Thanks
Matt
View 2 Replies
View Related
May 17, 2005
Hi all, here's a challenge for you:
I'm building an access2000 database for the development department for a nonprofit. The database houses a contact list which is populated by the names, addresses etc. of all the people that have donated money. The organization will often receive a gift from the husband OR the wife - so we want to have 'gift histories' which isolate a unique record (the individual - in this case we don't want to send a thankyou letter to John AND Mary Smith for a gift that came in from ONLY John Smith). However, when the organization has a fundraising event, or wants to identify how much a specific household has donated then we want to run mail merges and reports that pull information from both the husband AND the wife's records.
Currently the only to do these things is a manuel process. We have records for:
John Smith (husband)
Marry Smith (Wife)
THEN WE ALSO HAVE
John and Mary Smith
Therefore, whenever we want to send out a mailing or generate a report we have to delete duplicate addresses from the mail query report, making sure we use the appropriate record for the mailing.
Thus, my question is this:
Is there a way to associate two records from within the same table? That way I could:
run a report for just 'John Smith' OR John Smith PLUS affiliate record.
Send a mailing to 'John Smith' OR send a single mailing to John smith AND affiliate.
This very simple task is actually quite difficult because access doesn't want me to link two records from within the same table. Any suggestions?
Thank you in advance for your thoughts.
- John
View 2 Replies
View Related
Dec 3, 2004
Attempted to link to a table on an SQL Server, which included customer records & various contact dates, after the importing the records became corrupted in that they repeated the initial date over & over e.g.
Customer Date
AB1111 16.11.04
AB1111 16.11.04
AB1111 16.11.04
AB1111 16.11.04
...as opposed to listing same account number but with 4 different contact dates...also when the Access database was opened on a different PC (also Office 2K) the table was displayed correctly?
Has anyone else come accross the same issue, any advice/guidance much appreciated. :rolleyes:
View 1 Replies
View Related
Apr 21, 2014
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
View 8 Replies
View Related
Jan 4, 2014
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies
View Related
Jan 7, 2005
I want a report to tell me a percentage of records. i have a report that records "records that are past date". I want the report to tell me the percentage of total records that are past date.
I have a query that shows only past date records
and a query that shows all records
how do i do this?
View 1 Replies
View Related
Jul 16, 2007
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C
Parking units p1,p2,p3,p4,p5,p6
Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage
Unit A p1, p2, p5 s2
Unit B p3 s1
Unit C p4,p6 s3
Thanks,
Chester Campbell
ccampbell@jfreed.com
View 3 Replies
View Related
Nov 7, 2013
Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View 3 Replies
View Related
Oct 25, 2013
I'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand.
Here are two examples:
[URL] ....
[URL] .....
I believe I need to create a query based on another query (which I can do). I would need to create a column in the new query that calculates the percentage difference, but what's the formula/contents of that column?
View 5 Replies
View Related
Jun 15, 2005
Hey Guys,
I have 2 tables (tblSET, tblPROJECT) that have a relationship between them in the form of strPROJID (Project ID)
I need to make a form that has all empty fields in it, and when filled in distributes the entered data to the corresponding field in the corresponding table. I have tried using the design wizard and just putting the fields from all tables onto one form, but the "new record" button is greyed out when I do that. I figure this will include coding of some sort but I am far from a coder.
Thanks ahead of time,
JVermast
View 1 Replies
View Related
Jul 31, 2013
I am new to Access here, and looking to start up a database that will contain data for thousands of footballers.
I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).
How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?
View 3 Replies
View Related
Jul 31, 2013
I have multiple databases (Front-end, Back-end, Import, Reporting) which enables auditors in our company to audit loans.
I use an Import database which imports a random sample of loans (10%) into the Back End database for audit. I import a spreadsheet of loans (let's say 5000 loans) into the Import database, and I have the query return 10% of the loans, which is then imported into our Back-end database for audit (500 loans for audit).
Currently the auditors look at the daily list of loans (the 500 imported loans) and divide the work amongst themselves (5 auditors will audit 100 loans each.) They decide Bob will do 1-100; Joe will do 101-200; Bill will do 201-300; etc.
Is there a way using a query to assign these loans to specific auditors when importing the loans into the BE for audit? I would need to enter how many auditors will be working the loans so the work is divided evenly, and then enter the auditor's initials. (Since the auditors perform different audits, I would like the ability to select or key in auditor initials when importing the loans..
View 6 Replies
View Related
Oct 27, 2006
Hi,
I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?
Thanks,
Richard
View 6 Replies
View Related
Sep 20, 2004
OK I'm totally lost again.
What I want to do is;
Use one form to add records into several tables.
I have seperated my data into seperate tables and set up one to many relationships.
tblincident
--------------
Key#
Report#
Time
Date
Location
tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.
tblItem
----------------
Key#
Make
Model
Serial#
etc.
What i want to do is use one form to enter all this data into the different tables.
There can only be one record in tblIncident that can match multiple records in the other tables.
i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.
I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.
So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.
I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?
Any help is appreciated.
View 1 Replies
View Related
Jan 12, 2013
I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.
When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.
If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.
Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.
For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.
Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.
By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.
View 3 Replies
View Related
Aug 28, 2013
I am connecting to a table in our accounting software using MS Access ODBC Connection. I would like to change the value in a table for every record in the table 7,000+. In the latest version of the software a new field was added to the Vendor Table that by default is unchecked. I would like to have this field checked for each vendor. Is there a way that I can change all the records at once instead of going row by row to make the changes?
View 1 Replies
View Related
Jul 30, 2013
I have a main form that has fields from different tables. This is a research study, so each form enters data into different tables. Well, each record is a person's data. Instead of scrolling through each record, I need an option on the main form that allows me to search for a specific person and have their data populate into the form.So far I am able to make a combo box that pulls up the record from one table. Well, HOW do i do it from ALL the tables!?? Do I have to make a query?
View 3 Replies
View Related
Oct 1, 2014
I have a table with the following fields; Row; AnimalID; Weight: Date_Weighed (see attached screenshot example).
I would like to make a query that gives me the percentage weight loss/gain since the last time a particular animal was weighed. For example, animal AAA gained 100% between 2014/01/01 and 2014/02/01. Animal CCC gained 0% between 2014/02/01 and 2014/03/01.
I plan to use this to create a pop-up that informs me if an animal had lost over 15% of its bodyweight since the last time it was weighed, indicating either a loss of body condition, or that a female has given birth.
View 8 Replies
View Related
Apr 10, 2014
I a report based on query based on joined tables. Im using FK and PK accordingly in the tables and have a junction table.
I've attached a sample (removed all unique identifiers for privacy in case you're wondering).
Table structure as follows:
Employees tbl - Junction (License Link tbl) License No tbl, License Class tbl, Endorsements tbl
So the query runs on all of these tables.
When I build a report on the query, Simon shows up Six times. I suspect because he has six endorsements.
How do I produce a report on this table/query structure so that I get:
Simon.
Class, 1,2,3,4,5
Endorsement F,R,T,W,DG
View 1 Replies
View Related
Dec 2, 2011
Supposing you have 4 Tables
1.)User
2.)Admin
3.)Teacher
4.)Pupil
Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.
Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.
Question is: Is that possible? If yes, then how??
View 5 Replies
View Related
Dec 19, 2012
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
View 14 Replies
View Related
