Consolidating Fields From Multiple Records Into One Record
Nov 29, 2012
In Access 2010, I have a table with two fields. The first field (Investor Name) contains several occurrences of the same text (i.e. Investor A occurs 2 times, Investor B occurs 3 times, etc.). The second field (Investment) may or may not contain duplicates (i.e. more than one Investor may have invested in the same Investment). I'm trying to create a query that will show me, in one record, all the Investments that each Investor has made.
So for the following data...
Investor Name
Investment
Investor A
Investment 1
Investor A
Investment 2
Investor B
Investment 1
Investor B
Investment 3
Investor B
Investment 4
I want the query to report back the following...
Investor Name
1st Investment
2nd Investment
3rd Investment
Investor A
Investment 1
Investment 2
Investor B
Investment 1
Investment 3
Investment 4
To clarify further, I'm NOT looking to concatenate the results into one field. Instead, I want each Investment to appear in its own field, so that I can pull each one into a report later. Ideally, there should be no limit to the number of Investments that each Investor has made, although practically speaking, it likely won't exceed 10 or so.
I have a table where there are one person can have multiple records. I would like to consolidate the each clients records to one field with an additional field showing the total of all the clients invoices. Is that possible?
Sample:
Invoice# Client invoice_amt 12345 X $5 45767 X $8 89787 X $6
What I am attempting to do is, sum the units & value fields and report the data on one line with the vendor record with the > unit count. So, for the example above, the data returned would be:
I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:
table Books: - BookID - Author - Title - Year - Cabinet
table Papers: - PaperId - Author - Title - Year - Cabinet
table Catalog: - CatalogID - Author - Title - Year - Cabinet
And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.
So the resulting query would look like:
Type.....ID....Author.....Title....Year....Cabinet Book.....001...John.....Good book....2005....C01 Book....002.....Mary....Other book...2006...C01 Paper....001....Albert...PaperABC....1987....C01 Paper...002....John.....PaperXYZ.....2006....C02 Catalog...001...Mark....Cat00A....1989.....C02 Catalog...002...Bill......Cat00B.....2004.....C03 etc.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
Not sure where to put this, but as I want the information to be shown in form view, I guess this is the bext place to start.
In breif, I have created a database, in which infomation on machinery is held.
The problem I am having (atm), is I have to create a function, which I want to be presented in form form, where by each item can be searched by it's RequestID (unique) or the customer who owns it, so that its current location can be attached (or viewed).
I'm not sure whether i've worded this well, so to sumerise:
I need a form with 3 fields, RequestID, Customer and Location.
Where by I input the RequestID, which brings up the Customer and Location, or input Customer which brings up the RequestID and Location (obviously there will be mulitple results from Customer search).
Again, I hope I haven't confused anyone because it's pretty simple what i'm trying to say, but....
Code: Item Revision Start End 332 B 9/2/11 9/15/11 332 C 9/21/11 9/22/11 332 E 11/2/12 11/29/12 2A9 A 1/13/13 1/14/13 2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code: Item Revision Span 332 C 6 332 E 407 2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
I have this access "Database" that contains only one table that I'm trying to make a form for. I want it to allow the user to enter a value for field A and a value for field B then a list of values for field C (which is the key, so each would be a new record all with the same values for field A and field B). I tried to make a multiple item form but when I click on the new row for field C, fields A and B both disappear.
Disclaimer: I know that the creation of a one table database is a mortal sin but that's what my boss gave me and there are already >8000 records with a million mispelled words, so I don't think there's much I can do to make it a relational database without entering each record in again.
I am extremely handicapped in Access 2010. I am currently trying to create a database for a small company I am interning in. One of the things I would like to do is to update or create multiple fields in different tables simultaneously.
To explain, I have two tables, Production Activity in CY 2012 and Production Activity in $ 2012. Both tables have a field "Date of Activity." In addition to some other fields, Production Activity in CY has a field "Actual Production in CY" and Production Activity in $ has a field "Actual Production in $".
Now I had initially created two forms, each dedicated to one table, however my supervisor wants to use only one form to update both fields.
I also want to know how can I connect/link both of the Date of Activity so that if one changes, the other one changes too. And when I run queries, both "Date of Activity" fields should appear as one (when I want to run query of let's say the maximum or minimum production each month).
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
I've got a module which loads the data into a table, but it can't handle the records that are ~ delimited, so spits them out as a single field.
I know there is some VBA code that can be used to split comma delimited records, and I've seen bits of it floating around online and tried to alter it to work for the ~, but I get the feeling that what I've seen is only a part of the required code, how to put it all together to make something that actually works.
So, what I need to do is; - Split some records in a table out into multiple fields where there is a ~ present - Place these newly split fields into a table (I don't mind if it ends up in a new table or not)
I'm an expert user in Excel and am trying to learn more about Access, so I relate many of the functions to what they are called in Excel. And what I'm trying to do right now is count the number of fields in a specific record where the value is a specific result (CountIf).
For example, I need to return the number of fields in a specific record where the value is "Meets Requirements". The specific record will be determined by the Name, Contact, and Email fields (which together comprise the Primary Key).
Is there a way to count the values in multiple fields that equal "Meets Requirements"?
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
I have a form bound to a table which has 20 (name)fields and 1 date per record. A user fills in this form first to indicate which people are present this day. I know it would be better to use 1 name per record but that would in this case not work since the form must show all names for that day before saving and closing.
I have a different form where I use 4 combo boxes and a date field. The values that can be selected in the combo boxes should come from the 20 names on the first form and with the same date as the other form. So only 4 people of the 20 indicated as present on that day can be selected to have performed some task.
I know how to select with a combo box from different records, but how would I do it from different fields in the same record? And then also for a particular date?
I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.
Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.
I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.
So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".
So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...
Got relationships between: "contact name" fields in both "table2" and "table3" "project number" fields in both "table1" and table2"
In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?
Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.
Any ideas? I know there must be an obvious solution, but my mind is stuck right now!
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate 1 John Doe 05/06/2014 06/27/2014 2 Jane Doe 04/24/2014 05/15/2014 3 Steven Smith 05/15/2014 NULL/Empty 4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden) John Doe 5/19/14-5/25/14 Checkbox Null 1 John Doe 5/26/14-6/1/14 Checkbox Null 1 John Doe 6/2/14-6/8/14 Checkbox Null 1 John Doe 6/9/14-6/15/14 Checkbox Null 1 John Doe 6/16/14-6/22/14 Checkbox Null 1 John Doe 6/23/14-6/29/14 Checkbox Null 1 Jane Doe 4/28/14-5/4/14 Checkbox Null 2 and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
