Collate Field Content From Multiple Records
Jul 15, 2007
I have a database with company records in one table and calls made to companies in another table; the two tables properly related on a CompanyID field. A query joining the tables returns a dataset with multiple instances of CompanyID's because each company may have received 0 - n calls.
I would like to collate the content of the 0 - n[/I] callnotes records for each company into a single 'CollectedNotes' entity. I have two questions please.
Can anyone think of a way to do this without using code?
If not, can anyone give me a helping hand with the code?
In pseudo-code terms, I anticipate something like accessing the recordset for CompanyID's related to CallID's looping through to write the content of each instance of a CallNote (identified by unique CallID) to a new 'CollectedNotes' object. Help gratefully received. Thanks. MITW
View Replies
ADVERTISEMENT
Nov 25, 2013
As I began thinking about the data that I need to include in one of my reports I relized that I need to gather some extra data.Each design change has a lifecycle with 7 basic states from not started through to closed. States 4, 5, and 6 have two posible sub-states that I need to capture and report. It is almost like having options.
My data entry form already records the 7 basic states. What I would like to do is have another field that records the sub-states if the design change is in one of those three states.
Will a ComboBox do this?
Do I need a test routine for the After Update event in the first text box? Something that will check for states 1 - 3 and 7 move on to the next field and if states 4 -6 require users to enter the sub-state.
Would a nested if-then-else routine do the job?
View 1 Replies
View Related
Nov 16, 2004
Hi all!
I'm rapidly beginning to get a little confused... I'm trying to mass-delete records based on the content of a field.
I want to run either a query or a button on a form (or anything, for that matter) that will delete any records when there is an X in the N_Disp field.
Any ideas?
Thanks,
Barry
View 3 Replies
View Related
Nov 23, 2005
hi there
I'm building a forum from the scrath. It is already working, but I want to have in each topic the number of replies it has.
there are 2 tables, one for the topics and other to the replies. in the replies table there is a field called id_post that has the id of the post witch it belongs.
I solve the problem by inserting a query inside the loop of the posts:
<%
While Not rs.EOF
%>
<%
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT COUNT(*) as cont FROM replies where id_post= " & rs("id") & " "
rs2.Open sql,Conn,1,2
%>
post: <%=rs("post")%> replies:<%=rs2("cont")%>
<%
rs.MoveNext
wend
rs.close%>
but someone told that this would make the page slower, so I want to know if I can do the same thing without having the query inside the loop.
I don't know if I made myself clear, sorry for that.
tks for the help.
View 1 Replies
View Related
Oct 31, 2014
I have a class roster and need vba code to print a first day package of forms. I want them to print each person's package then move on to the next person until the roster has printed each person's package.
View 5 Replies
View Related
Apr 16, 2007
Can I create contents of one field based on the contents of others?
I have a database of pc systems which we've tested and was wanting to generate a single text line to identify the individual pc
The single filed would contain data from the following fields
Job Number
CPU Type
CPU Speed
RAM
HDD size
eg
So the new field would contain "Job345-P3-1000-256-20"
Can this be done?
View 3 Replies
View Related
Feb 26, 2006
in my form I created an unbound field with a requested combination, now how can I put those in a table field.
in other words, how can I send the content of a form's unbound field to a field in the table?
your help is very appreciated.
Regards,
CS.
View 3 Replies
View Related
Jun 4, 2013
I am making a very simple 'registration' database for a children's event in a couple of weeks.I the table/form there is a checkbox field called 'consent' which, if checked, indicates that a child can leave the event without parental consent.
There is a report printed on each child (a registration page which the leaders get a copy of). I would like on this report a 'red box' to appear if the child cannot leave without permission (i.e. the consent box is not checked). I would also like this 'red box' to appear on the form. I had thought of doing it this way - but I'm not sure if it's the best, or if it's possible:
Have a field in my table called 'consentindicator'. When the 'consent' box is checked, there is a period ('.') placed into the 'consentindicator' field. It is set to turn red when a period is present. That way, when the consent box is checked, a get a red 'box'.
View 12 Replies
View Related
Jul 8, 2013
I am working on a report that will have 16 sub-reports, one right below the other. Because of the complex nature of the calculations in the groupings it seemed easier to "build" the report using vba rather than using the report's built-in grouping ability. The issue I am running into is that there seems to be no simple way to control the height of the sub-reports. I had hoped that setting "can grow" to yes would change the height of the sub reports and move all subsequent sub reports down (Allas)
As an alternative I thought too that I might be able to set the height of the sub-report control, which would make for a tricky but not impossible bit of programming, but (Again allas) I can't seem to find a reasonable way to determine the required height of the sub-report's control.
View 3 Replies
View Related
Feb 4, 2015
how many elements matching to my primary elements from any records of my query and count match, if some element doesn't match then I need to add it to my primary elements, then at the end (rst.eof) count how many primary elements I have.
E.G
id colours
1 blue;red
2 purple;blue;green
3 red;violet;purple;blue
dim matching_elements as long
dim primary_elements as string
dim TheNumberOfPrimaryElements as long
First of all, if I open recordset primary_elements is empty so I need to assign a value form first record.
primary_elements = rst!colours (so primary_elements = blue;red)
Now I can start comparing my primary_elements with second record:
matching_elements= 1
primary_elements = blue;red;purple;green
comparing my primary_elements with third record:
matching_elements= 3
primary_elements = blue;red;purple;green;violet
It's my last record so I need to count primary_elements
TheNumberOfPrimaryElements = 5
I need "matching_elements" to count other function in my application.
View 7 Replies
View Related
Apr 15, 2008
I have a field in access database called result (coming from webform) and the content is something like: 'not much; 2' or 'frequently; 5'. How can I split numeric and text value into two separate columns in query?
I will greatly appreciate any help.
Debbie
View 14 Replies
View Related
Jan 18, 2005
Hi all
I'm trying to find a way to streamline my printing.
I've made a student report card DB. I have a report for classroom stuff. I have another report for music and another report for physical education. (I do this because there's too much info to put on one report)
I have 1000 of each report. Historically, I'd print them all of then sit down with my 3 big piles of paper and collate them one at a time. A time consuming job.
Is there a way I can print all 3 reports and collate them for each student at the time of printing??
I appreciate any advice.
View 4 Replies
View Related
May 11, 2014
I have a form with a number of fields and sections which change visibility based on the data entered in other fields,
The fields have an "on update" event to check the content of the field and make the appropriate changes,
I've changed this form to be able to edit records instead of "Data Entry" so now I need the checks to occur when the record changes as well as when data is entered,
How best can I achieve this without simply duplicating the code (which seems like a bad idea) into the "On Current" event?
View 3 Replies
View Related
Jan 26, 2014
I would like the text from the previous field Invoice_Contact copied into the field Invoice_Contact of each new record created. I think I can use CTRL ' - but would like it done automatically if possible.
Field location:
Form = PatientUpdate - Subform = F_Invoice - Field = Invoice_Contact
The form and subform are linked through: Pat_ID
The subform is based on the table called Invoice with Invoice_ID as the primary key.I tried several variations of this DLookup from examples I found on the web but the field comes out with "error" in it and its flashing!
View 14 Replies
View Related
May 24, 2012
I want to display the ID of a dataset in a form but I don't want the user to be able to edit it. Therefore I want to display the id in a label and not a textbox or combo etc.
I can't see a way of how to do this though. Is this possible, or am I going about it the wrong way?
View 3 Replies
View Related
Dec 31, 2014
I have a subform with continuous records. One of the fields in the recordset of the subform is a field named "Remarks". This field does not need to be visible on the continuous subform as it is rare that this field will have any entries.
I plan to apply conditional formatting on another field (IDcardNo) in the record line of the continuous subform so that when field "Remarks" contains any data it will show as a different format on the field IDcardNo.
I would like to make a small form appear when one points to the IDcardNo field with the different format, so that the data in the field "Remarks" pops up when one points to the field IDcardNo with altered format, showing that there is data in the field "Remarks".
View 3 Replies
View Related
Aug 15, 2007
Hi all. Using Access 2003 I am trying to figure out how to print and collate a report and an existing pdf file that is opened based on a field in the record. I figure that it probably has to do with looping through the records but I am not very good at that.
So I have a form with a sub form that opens records based on a combo box. I then have a report (a checklist sheet) that prints 1 page per record. I would like to print a pdf file associated to that record before it prints the next record.
Here are the names of the objects being used:
tblMoveDoc
frmMoveDoc
subfrmMoveDoc
rptCheckList
Any help would be appreciated.
Thank you,
Matthew
View 4 Replies
View Related
Nov 7, 2006
Let's say I have two tables:
Product.
Item Description
A It's Red
B It's Blue
C It's Green
D You'll love it
Category.
Item Category_name
A Cat1
A Cat2
A Cat3
B Cat1
B Cat4
C Cat1
C Cat6
I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:
ItemCategoryConsolodation.
Item Desc Categories
A It's Red Cat1,Cat2,Cat3
B It's Blue Cat1,Cat4
...
How does one do that?!
What's it called when you try to do this so I can Google it?
I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?
View 3 Replies
View Related
Dec 17, 2004
Hi all,
I was wondering if anyone had code to split a field by semicolums, to create multiple records.The field has the first line of address for each property with a particular postcode.
For instance
field name:
PRMF
Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
I want to be able to split fields like the one above with these properties by the semicolums so they become seperate records, but share the same other fields e.g. postcode, city...
Thanks in advance,
M-.
View 5 Replies
View Related
Dec 21, 2004
Hi,
still not sure how to split one field therby creating multiple records from it.
For instance:
split field:
PRMF
Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
to
PRMF
Craven House
Ample Mansion
WHSmith
Bulevard Terrace.
I have made it a little clearer on the attached word doc.
The plan is to prompt the user to enter a postcode, then based on postcode an sql statement splits the fields of the field metioned with the criteria on a query. Any help very much appreciated, Thanks in advance.
M-.
View 2 Replies
View Related
Dec 15, 2006
Howdy.....I'd like to know if there is a way to populate the same field
in several hundred records with the same text value instead of typing into each field in datasheet or form view. Macro?
Thanks for your support!
bugleboy
View 2 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
Nov 17, 2006
I have a table of every reading of every electric meter. This history goes back a couple of years.
Field1 = date
Field2 = Meter#
Field3 = Single digit code identifying method of reading
The same meter is read multiple times in a year. The number of readings in the database is 1 or more (no limit).
I want to create a single line of text in 1 field that shows a history of the reading method of a particular meter.
Reading method translations
A=Radio Frequency
B=Manual
C=Not Read
X=Estimated
Here's a sample of what I would like to see .....
View 4 Replies
View Related
Mar 9, 2007
Hi All,
I can use some help with a jumping off poit.
*Please see attached file*
I have 2 tables tblSFDC and tblTheCall
tblSFDC (contact information) includes CustID (primary key) and a memo field "Notes"
tblTheCall (call information) includes CustID (relates to tblSFDC) and "activity data fields" ie "Live", Left Message" , Sent email".
tblTheCall may in some cases have multiple records for each CustID
I need to :
Concatenate from tblTheCall Field Names and field values (that are not null)
Insert into tblSFDC.Notes
Where tblTheCall.CustID = tblSFDC.CustID
*Please see attached file*
TIA
Detrie
View 8 Replies
View Related
Nov 18, 2004
Hi forum, I have this database with a main form and 5 subfoms. For the Address Subform, I need to add multiple addresses for each person I add. How we currently do it is enter the multiple addresses per person, one at a time. Is there an easier way to do this.
For eg: Id Last Name First Name
Then Address 1
Address 2
Address 3
View 1 Replies
View Related
Jul 30, 2015
I am linking to an SQL database that is designed in such a way that the problem description is recorded in multiple records. See a simplified example below. I want to run a query that will merge the information in the PROBLEM_DESCRIPTION and return only one record for PROB_ID 55678.
PROB_ID ORIGINATOR DATE PROBLEM_DESCRIPTION
55678 Jim 01/01/2015 While cleaning my computer, I noticed
55678 Jim 01/01/2015 the screen was cracked. I called
55678 Jim 01/01/2015 the IT department and they said I had
55678 Jim 01/01/2015 to write a work order. However it
55678 Jim 01/01/2015 was rejected without reason.
View 7 Replies
View Related