Query: Display All 3 Matches For A Field In One Line
Feb 19, 2006
Hi all, I'm new to this forum. I'm having a problem with a query I'm trying to create.
I have two tables in the database: tblCustomers and tblInstallations. These two tables are linked by a field 'CustomerID'.
CustomerID is the Primary Key in tblCustomers.
In tblInstallations, the primary key is InstID and for each CustomerID in tblInstallations there are either 1 or 3 InstID records.
Now what I want to do is create a query with fields: Customer; CustomerID; InstID1; InstID2; InstID3.
InstID1 will contain the first InstID for the CustomerID, and InstID2 & InstID3 will contain the second and third, if applicable.
I see in Query Design View in the 'Totals' line there is a option to display the 'First' and 'Last' match for a field. So is there any way i can use this method and also display the second record? If not, is there any other way to make this query work?
I hope somebody can help!!
Kerry
View Replies
ADVERTISEMENT
Sep 2, 2013
I am teaching myself Access 2007. I have a successfully imported my database to a table, and now I want to design Forms and Reports. It seems Access wants to basically display the data in either one of two formats, either Columnar, or Tabular. One Form displays the data in columns, with one field per line, and the other displays the data in rows, with one whole record per line. I need a Form (and Report) which will display more than one field per line, and use more than one line. For example, some lines will have 2 or 3 fields, then the next line may have only 1 or 2 fields. Following is a print-out of a record from my existing database (stored in a comma delineated .txt file and read and printed by a program written in DOS, QBasic):
.
461-1036 :c: Assignment of O&GL
Feb 28, 2008 Filed: Mar 20, 2008
Grantor(s): Plains Exploration & Production Company;
Plains Resources Inc.; Pogo Producing Company LLC;
Pogo Panhandle 2004 LP; Latigo Investments LLC;
Latigo Gas Services LP; Latigo Petroleum Inc.;
Latigo Petroleum Texas LP
Grantee(s): Oxy USA Inc.
Comment: Assigns 50% of Assignors' interest;
CORRECTION ASSIGNMENT at 466-493 deleting certain
properties from the description
[The attached .pdf file shows a better view of the print-out]This data is abstracted version of a document recorded in the public record in a courthouse. On the first line there are 3 fields:
1) the Volume-page,
2) A notation that we have a copy of the document, and
3) the title of the document.
On the 2nd line there are 2 fields:
1) the date of the document and
2) the date the document was filed in the courthouse.
The next 3 fields are displayed one at a time in columns. How I can get Access 2007 to display like this?
View 1 Replies
View Related
Jun 22, 2005
undefinedHi, apologies if this has been answered before but can't find anything relevant!
Trying to match one field to the first 5 digits in another i.e.
Field 1 = 55667
Field 2 = 5566785431 so would want to match the 1st five digits in field 2...
Any ideas?
Thanks
View 3 Replies
View Related
May 18, 2013
I have a combo box that gathers data based on a query. Is it possible to add a line of text to be displaed every time i pull down the box?
For example I have:
Data1
Data2
Data3
I would like to have:
Not In List (the added txt)
Data1
Data2
Data3
View 2 Replies
View Related
Aug 28, 2014
I have to two tables and I was add data to second table only if the field matches. Lets I have two fields site and recid on both and I want to update site on the second table only if recid from the first table matches the recid from the second table.
View 1 Replies
View Related
Feb 14, 2007
I have a table with records for products from each sales region (4 regions, N, NE, S, W). The table has the following fields: Region, Product Name, Product Description, Category, Business Line, and Supplier.
While the Product name is consistent across the regions the description, category, bus.line and supplier may have slightly different entries in each region. I need to build a query that when any of the Product Description, Category, Business Line, and Supplier fields do not match across divisions for each Product Name will display all the records for that Product Name. Any ideas?
View 2 Replies
View Related
Sep 19, 2004
I am looking for help to to generate a command line to automatically run a report within a windows program I am using. The program I use has command line fields to automatically open the access report when I complete my program session. I have tested the command line with a standard C:.... .exe to open Word, Access, etc.
I have tried using the create shortcut and snapshot methods without success and wondering if a macro is needed (havent a clue).
Hoping someone can help!
thanks... Keith
View 2 Replies
View Related
Nov 5, 2007
I have a table from an excel sheet that comes in the format above with up to 5000 rows in record groups from 1 to 10 rows in each. The repeating data is how it comes from our all singing and dancing case recording software and I can’t change that fact.
Take a group of records with the same TradingName such as “The Herriots” group, There should be one row in the group where FdInspAction = LastInspDate (in this case 06/01/2007) . In this group there is no match so we need to identify the group. The “Bloggs” group is fine (see Row 8) so can be ignored.
My feeling is it can’t be done other than by visual checking. Can anyone think of a way around this?
IDTradingNameFdInspAction LastInspDate
1The Heriotts 03/02/2001 06/01/2007
2The Heriotts 10/02/2002 06/01/2007
3The Heriotts 16/02/2003 06/01/2007
4The Heriotts 25/01/2005 06/01/2007
5The Heriotts 04/01/2006 06/01/2007
7Bloggs Ltd 02/01/2005 25/01/2005
8Bloggs Ltd 25/01/2005 25/01/2005
View 8 Replies
View Related
Mar 30, 2013
A charity has a client file (over 700 clients) that will have records that need editing. The user is presented with a form(1) holding a text box to enter the parameter (Client Surname) for a query. They can use starting letters and wildcards to narrow a search e.g. Jo*The click a command button that opens another form(2) based on a query that uses the parameters from the first form.
This Works just as planed if matches are found. If not, it displays a blank/new record. I want to either 1) stop the blank form being displayed 2) tell the user that their query was unsuccessful. I Do not want them to use this facility to CREATE a NEW Client record.
The parameter query is working fine and form(2) shows the matching records, it's reacting to a situation where no records are found, that is my problem.
View 2 Replies
View Related
Sep 5, 2006
Hi, can anyone tell me how to doubleclick on field (on a form) and it opens up a form that shows bounded filtered results. I have a sub form which has details supporting summary numbers. I want my user to doubleclcik on the currency field and the detail supporting that sum total, pops up automatically. Any help please? :confused:
View 2 Replies
View Related
Dec 18, 2013
Basically, some clever soul has used coded fieldnames but then added a description telling you what the field contains, and I need to return this description.
View 5 Replies
View Related
Jan 20, 2015
I have a query which is a concatenated field of text and then a field from a table that has a hyperlink.
i.e 15mm predesigned mould Drw: [L:10527-123-A.pdf#L:10527-123-A.pdf#]
I want it to display in the query field as just the hyperlink
15mm predesigned mould Drw: [L:10527-123-A.pdf]
View 5 Replies
View Related
Nov 11, 2007
Given a table field that is a hyperlink type.
I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value.
Any ideas how?
Thanks.
View 1 Replies
View Related
Jul 27, 2005
In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source:
[qry_op500_entry_delta]![countofphone model]
I get an error stating that this is not in my field list for the form.
If the answer is No or if needed I can give more information on what I am trying to do.
View 6 Replies
View Related
Jun 25, 2013
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
View 12 Replies
View Related
Jun 5, 2014
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
View 6 Replies
View Related
Nov 29, 2014
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....
View 8 Replies
View Related
Nov 18, 2014
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
View 14 Replies
View Related
Nov 21, 2006
Can anyone help....
I have a field on an Access db, this is a "Memo " type field which has many lines of text within it.
I need to run a query on this to get the last line of text within this field.
Aside from records where this memo field is filled in I also have records where the memo field is blank. So basically if the memo field is blank I want a blank field returned as well as fields with data.
I am using Access 2003 but also am running this on Access 2000 (not sure if there has been a change to function names)
This is seriously bugging me - can anyone put me out of my misery?
View 5 Replies
View Related
Jun 1, 2013
When I am displaying a memo field, is there any way that I can force a new line? Can I add a character to the text to do that?
View 7 Replies
View Related
Dec 16, 2012
I previously used an older Access version under XP, but I switched to Windows 7 (64-bit) and Access V10. In general, most everything appears to work as expected, but I can't be sure because I have not gotten past the "previously simple" task of making a simple label !!!!
When I construct a LABEL layout, if I use ONLY one field per line, everything appears to work OK.When I put MORE than one field per line, the additional lines are "accepted", but when I click "finished" to preview the finished labels, I get an #error on every line that has more than one field, but the lines with only one field print out as expected.If I then go into the DESIGN mode, each line with more than one field displays:
=Trim([field-x] & " " & [field-z]), where the " " is related to the space I place between field-x and field-z.
During this process I also get a box with the message "Enter Parameter Value", and below that is the text, "Trim", with a box in which to insert some kind of "parameter". I have absolutely no clew what to put into the "box", and no where in the 812 page, "The Missing Manual" is the question answered.I have wasted approximately five hours trying to solve this issue, including google searches for potential answers, some of which seem reasonable, but do not work when implemented. At this point I am ready to trash ACCESS and move on to some other process.
View 5 Replies
View Related
Oct 18, 2014
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line
with me.graph47
.SeriesCollection(1).border.Color = vbblue ' change the line color
.SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3
.SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4
.SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color,
.SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis
end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
View 1 Replies
View Related
Jan 6, 2006
I have a memo field that contains several text strings separated by line breaks. I would like to write a query to add another text string to the end of the field. How do I embed a line feed in the SQL?
View 3 Replies
View Related
Jul 21, 2006
I have no idea if this is possible or not.
I have a requirement to pull a list of Doctor's, their address, and which counties they serve. Sounds fine, right? Well, they want all the counties on one line... One record for each unique address.
So, my query would return this
Dr Address County
Dr Smith Main Street Plymouth County
Dr Smith Main Street Sarasota County
But they want it to look like this
Dr Address County
Dr Smith Main Street Plymouth County Sarasota County
Any idea on how to pull that together for them and put it in an Excel spreadsheet?
Many many thanks.
Tess
View 5 Replies
View Related
Mar 19, 2015
Ok, I have pesky solid line at the bottom of one field (bottom of the field with 6.80 in it..
I cannot figure out where it has come from or how to get rid of it!
Here's the properties for that particular field...
When I delete the column it appears somewhere else...
View 3 Replies
View Related
Jul 23, 2013
how to set focus in first or last line, but I need set to second.
View 11 Replies
View Related
