f



multiple conditions in a join, access doesn't like on (table1.field=table2.field and table1.field=table3.field)

Hi I have written the following query and am having problems with the
last outer join in ms access... it doesn't like the multiple
conditions that I have in there, if I have just one or the other its
OK but not both... and I have tried putting the second condition in a
where clause but then i don't get an outer join

i'm pulling my hair out on this one... i would be extremely greatful
for some ideas...


SELECT ro.thickness,ro.width,ro.grade,sum(ro.pcs) as pieces,
ro.length,GT.thicknessid,t.thicknessid,gt.gradethicknessgroupid,
gtd.gradeid, gr.gradeid
FROM(((NewOrders as RO
LEFT  OUTER JOIN Grades as GR on GR.gradename=ro.grade)
LEFT OUTER  JOIN thicknesses as t on t.imperialdecimal=ro.thickness)
LEFT OUTER JOIN GradeThicknessGroups as GT on
GT.thicknessid=t.thicknessid)
LEFT OUTER JOIN GradeThicknessGroupDetail as GTD on
(GTD.GradeThicknessGroupID=GT.GradeThicknessGroupID and
gtd.gradeid=gr.gradeid)
where gtd.gradeid=gr.gradeid
GROUP BY ro.thickness,ro.width,ro.grade,
ro.length,t.thicknessid,gr.gradeid,gt.thicknessid,
gt.gradethicknessgroupid, gtd.gradeid
Order By ro.thickness,ro.width

my tables looks like this

[NewOrders]
ID (PK)
Width
Thickness
Grade
Length

[Grade]
GradeID (PK)
GradeName

[GradeThicknessGroups]
GradeThicknessGroupID (PK)
ThicknessID (FK: [Thickness])

[GradeThicknessGroupsDetails]
GradeThicknessGroupDetailID (PK)
GradeThicknessGroupID
GradeID (FK: [Grade])

[Thickness]
ThicknessID (PK)
ImperialDecimal

0
dman911 (3)
8/13/2007 9:24:48 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
781 Views

Similar Articles

[PageSpeed] 45

oops i left the where statement in.. remove the where clause from the
above query (where gtd.gradeid=gr.gradeid)... that was my failed
attempt to make it work...

0
dman911 (3)
8/13/2007 9:28:22 AM
An update... i tried the same query in MYSQL and it worked!  hmm so
its not that i am violating any fundamental rules of joining
tables ... but i don't really feel like going to the pain of migrating
to MYSQL just becuase acess says "join condition not supported" when
you have two conditions ANDED together in the ON conditional section
of a left join... any suggestions on how to make this work in ms
access??


here is the query as it worked in MYSQL...

SELECT  ro.thickness            ,
        ro.width                ,
        ro.grade                ,
        sum(ro.pcs) AS pieces   ,
        ro.length               ,
        GT.thicknessid          ,
        t.thicknessid           ,
        gt.gradethicknessgroupid,
        gtd.gradeid             ,
        gr.gradeid
FROM RawOrders AS RO
LEFT JOIN Grades  AS GR
ON      GR.gradename=ro.grade
LEFT JOIN thicknesses AS t
ON      t.imperialdecimal=ro.thickness
LEFT JOIN GradeThicknessGroups AS GT
ON      GT.thicknessid=t.thicknessid
LEFT JOIN GradeThicknessGroupDetail AS GTD
ON      GTD.GradeThicknessGroupID=GT.GradeThicknessGroupID and
GTD.gradeid=gr.gradeid
GROUP BY ro.thickness           ,
        ro.width                ,
        ro.grade                ,
        ro.length               ,
        t.thicknessid           ,
        gr.gradeid              ,
        gt.thicknessid          ,
        gt.gradethicknessgroupid,
        gtd.gradeid
ORDER BY ro.thickness,

0
dman911 (3)
8/15/2007 6:29:04 AM
Reply:

Similar Artilces:

Merge a field into a field
I have an email distribution list. I need to add the person's name in the body of the text. What do I do to merge one field into another. I know that <<>> doesn't work. thx Create a textfield Create a calculated field into the textfield type your text, where you want the name to go type QWERTY (or any other code you like) In the calcfield (result text) Substitute ( textfield ; "QWERTY"; NameField) More options ofcourse are possible, this is just clean and dirty. Ursus <fromero@csudh.edu> schreef in bericht news:1138227655.180034.311000@o13g2...

Access populate field in table from another field in the same table
I have two fields, "Item" and "Information" in the same table. "Information" contains three items from a seperate table created by a lookup. I would like to substring out the first item and place it in the "Item" field. Is there an easy way to do this? I would like the "Item" field to populate after the contents of "Information" have been selected. Thanks in advance for any assistance. Item Information Item1 Item1 Item2 Item3 On Tue, 20 May 2008 13:40:45 -0700 (PDT), sd_eds <sd_eds@hotmail.com> wrote: With a combination of Left$ and InStr you can pick apart the Information value and arrive at the Item value. You are dangerously close to storing redundant information, a no-no in proper relational database design. -Tom. >I have two fields, "Item" and "Information" in the same table. >"Information" contains three items from a seperate table created by a >lookup. I would like to substring out the first item and place it in >the "Item" field. Is there an easy way to do this? I would like the >"Item" field to populate after the contents of "Information" have been >selected. Thanks in advance for any assistance. > >Item Information >Item1 Item1 Item2 Item3 ...

Sorting according the numerical value of certain field, if that field doesn't exist, keep the order of appearence of the line.
Hi all, I want to do the following thing: Sorting according the numerical value of certain field, if that field doesn't exist, keep the order of appearence of the line and put all these lines after those lines which have the value the sorted key. Say, for the following example: $ cat sort-test 1 2 3 4 2 3 4 3 4 5 8 2 3 6 8 3 4 $ sort -rn -k4 sort-test 3 4 5 8 1 2 3 4 8 3 4 2 3 6 2 3 4 I want to obtain the results like this: 3 4 5 8 1 2 3 4 2 3 4 2 3 6 8 3 4 Any hints? Regards -- ..: Hongyi Zhao [ hongyi.zhao AT gmail.com ] Free as in Fr...

Accessing text form fields name=field[label]
I'm pretty much a JavaScript novice. I'm good at learning by example and changing those examples to suit my needs. That said .... ..... I have some select fields in a form I created for a database search that I am unable to figure out how to access. (The search is implemented in Php/MySQL.) The user enters search values for: name, address1, city, .... etc., ..... and for each of these they also select whether the search should look for the field to be equal to, not equal to, Like, or Not Like the value in the database. So in the form I have: <input type=text name=city> in combination with <select name=func[city]> <option value='='>=</option> <option value='!='>!=</option> <option value='LIKE'>LIKE</option> <option value='!='NOT LIKE>NOT LIKE</option> </select> For an example, this search could be to find an entry "WHERE city $func[city]] $city. (Php - e.g., WHERE city = 'Denver'). This works fine - in Php. I use similar combinations for name, address, etc., ... Okay, here's the JavaScript problem. For the Reset action I have a JavaScript function to Reset all the form fields. I want to Reset the selected indexes for the "func" fields (func[name], func[city], func[state], ...) using a loop that goes through the associative array, "func." I cannot find the right syntax to address them as an array and I am beg...

Form fields to same field
I have a form to be completed by a teacher. They are to input 30 grades on the form (along with teacher name, period number, etc). This will store all 30 grades in one field in my sql table. The multiple forms into one field has me baffled. I am a newbie but appreciate all the help! Thanks in advance. >I have a form to be completed by a teacher. They are to input 30 >grades on the form (along with teacher name, period number, etc). This >will store all 30 grades in one field in my sql table. Is that 1 grade each for 30 students? Better be sure you keep the order straight if so, a...

Wildcard doesn't match using LIKE '%' on a varchar field, wierd!
Right this has to be a Micro$oft mess-up surely...? I'm running SQL 2k standard with SP3. I have a table which I'm trying to query using a LIKE operator on a varchar field as follows .... WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE '%' .... In actual fact the % is passed in by the application when the user selects "All managers" from the drop down list used to select the Manager to filter by. If they select a manager's name from the list it becomes LIKE 'ajames' or whatever. BUT - the table currently contains 2972 records. If I take out the WHERE...

Separating one field into two fields based on a character in the field
I know there has to be a way to do this, but I've gone brain dead. The scenario..a varchar field in a table contains a date range (i.e. June 1, 2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). The users have decided thats a bad way to do this (!) so they want to split that field into two new fields. Everything before the space/dash ( -) goes into a 'FromDate' field, everything after the dash/space goes into the 'ToDate' field. I've played around with STRING commands, but haven't stumbled on it yet. Any help at all would be appreciated! DTS? Try this..... CREATE TABLE Test ( [startdate-enddate] varchar(100) ) insert into Test VALUES ('June 1,2004 - June 15, 2004') insert into Test VALUES ('September 1, 2004 - September 30') ALTER TABLE Test ADD startdate varchar(100),enddate varchar(100) GO UPDATE Test SET startdate = SUBSTRING([startdate-enddate],0,CHARINDEX('-',[startdate-enddate],0)), enddate = substring([startdate-enddate],charindex('-',[startdate-enddate],0)+1,LEN([startdate-enddate])-CHARINDEX('-',[startdate-enddate],0)) FROM Test SELECT * FROM Test ALTER TABLE Test DROP COLUMN [startdate-enddate] GO SELECT * FROM Test DROP TABLE Test "steven virnig" <pezguy@mn.rr.com> wrote in message news:<sHjdd.20301$mR.920@twister.rdc-kc.rr.com>... > I know there has to be a way to do this, but I've gone brain dead. The > scenario..a varcha...

error message; microsoft access can't find the field 'ToggleLink' referred to in your expression
i have a couple of buttons in a database which when pressed give the above error message. when I click OK i get access to the form anyway. how can i get rid of this apparent glitch? many thanks grahamwarren2009@googlemail.com wrote: > i have a couple of buttons in a database which when pressed give the > above error message. when I click OK i get access to the form anyway. > how can i get rid of this apparent glitch? many thanks Do you have access to the code. If so, open up a code module for the form and do a CTRL+F and search your code for references to ToggleLink. Perhaps it was a field that existed but was renamed or removed but something is still referring to it. On 14 Mar, 20:45, Salad <o...@vinegar.com> wrote: > grahamwarren2...@googlemail.com wrote: > > i have a couple of buttons in a database which when pressed give the > > above error message. when I click OK i get access to the form anyway. > > how can i get rid of this apparent glitch? many thanks > > Do you have access to the code. =A0If so, open up a code module for the > form and do a CTRL+F and search your code for references to ToggleLink. > =A0 =A0 =A0Perhaps it was a field that existed but was renamed or removed= but > something is still referring to it. Tried that sorry, can't see what ToggleLink refers to. Is there any way I can get someone to look at file and play with it to try and fix? (It's about 42MB but don't know why it...

SQL Server View integer field shows as text field in Access
Hi All! I have an SQL Server View where my field in the view is an integer field and works well. When I link to it from Access, the field is defined as a text field in Access. Does anyone have any idea why this is happening or how to fix it? Thanks in advance! KB On Sun, 3 Aug 2008 20:31:43 -0700 (PDT), kevinfbutt <kevin.butt@iavalley.edu> wrote: That's quite unusual. Can you post the SQL for that field in that view? What version of Access and SQL Server? Fully patched? Can you replicate the problem with a view in one of the SQL Server sample databases? -Tom. Microsoft Access MVP >Hi All! > >I have an SQL Server View where my field in the view is an integer >field and works well. When I link to it from Access, the field is >defined as a text field in Access. > >Does anyone have any idea why this is happening or how to fix it? > >Thanks in advance! > >KB Is the column in the view the result of a calculation? If so, try applying the CAST or CONVERT function to coerce the result back into an integer. "kevinfbutt" <kevin.butt@iavalley.edu> wrote in message news:162bd7b3-e29e-4aba-bb57-e92bfd689684@2g2000hsn.googlegroups.com... > Hi All! > > I have an SQL Server View where my field in the view is an integer > field and works well. When I link to it from Access, the field is > defined as a text field in Access. > > Does anyone have any idea why this is happening or how to fix it? > >...

Can't access field in struct
I can't seem to access the field in this struct: #include <stdio.h> struct data{ int x; int y; }; static struct data test; struct data *pp = &test; pp->x = 23; in the last line I get "parse error before "->" token. How do I set x to 23? JS wrote: > I can't seem to access the field in this struct: > > #include <stdio.h> > > struct data{ > int x; > int y; > }; > > static struct data test; > struct data *pp = &test; > > pp->x = 23; > > > &...

'No Access' to a related field
I've got two related databases, with appropriate passwords/group access to allow a user to edit one particular field, the TB Tested field. The "TB EXPIRES" Database just has names, ID's, and a TB Tested field. So does the "MACROFILE" Database. In MACROFILE, I have a layout linking both databases by the ID's. I planned on having the person go to MACROFILE and enter the 'TB Tested' field there, so that I can keep the TB TESTED just for data holding. The relationship works that I enter it in MACROFILE and it gets entered in TB TESTED. BUT: when I ente...

Sorting a Text field like a Number Field
I have a table with around 18,000 records in it. One of the fields is a Text field, but 85% of the things in that field is a number (The other 15% is a number with a text suffix). I was wondering if there was an easy way to Sort this field as if it were a number (because, when it sorts as text it looks like this... 1 11 12 14 2 21 24 3 35 So far, the only thing I came up with was to make a query with this expression... TempSection: CDbl([Section]) But that either produces a #Error for those rows where the Section has a text character in there or it won't even open the query in datasheet view because of Data type mismatch in criteria expression. What can I do? Can I create an iif statement to catch that error? or Something? Thanks! -- Nathan Try this: sc:iif(isnumeric(x!y),cdbl(x!y),0) where X is table and Y is field. Sort on this but display y. This will place all fields containing text at the top and then sort numbers. If you want all text fields at the bottom then change 0 to 99999999. If you really need to sort the text contianing fields as well you will need a funtion to extract the numeric part and use that as the arguement to cdbl. You wouldn't need the iif function then. Good Luck Ira Solomon On 5 Oct 2003 16:31:25 -0700, ngiven@hotmail.com (Nathan Given) wrote: >I have a table with around 18,000 records in it. One of the fields is >a Text field, but 85% of the things in that fiel...

How to link fields in MS word document to Access database
Anyone have experience in linking fields in an MS Word document to an access database? What I am considering doing is taking an existing fill-in-the fields word document, for which I cannot now save the data, and allow it to be opened with no data, have the fields filled in and then save the data in an Access database. I also want to later select the document (from a file in the database), open the word document and populate the fields from the database. Of course if there are then any updates I want to put them back into the database. Workable? Anyone with sample code? Bob Alston On Jun 10, 6:21=A0pm, Bob Alston <bobalst...@yahoo.com> wrote: > Anyone have experience in linking fields in an MS Word document to an > access database? Hello Bob, Instead of Word documents I use RTF-documents. RTF-documents act just like Word documents in Word. The difference is that RFT-documents are plain text-files, and can easily be processed in Access to find - with the appropriate placeholders - the filled in information. HBInc. > > What I am considering doing is taking an existing fill-in-the fields > word document, for which I cannot now save the data, and allow it to be > opened with no data, have the fields filled in and then save the data in > an Access database. =A0I also want to later select the document (from a > file in the database), open the word document and populate the fields > from the database. =A0Of course if there are then ...

Can't Forward
Using Lotus Notes 6, I'm getting an error message whenever I try to forward a message. This is only 1 of 5 installations that this is occuring on. Any ideas? The error message simply states, "Body Field Does Not Exist" os similar wording. Thanks in advance.... Jenny Normally an error when you forward but otherwise is OK is caused by the mail fiel reference in the User's location document being wrong. eg: mail\bill.nsf instead of mail\BillGates.nsf The error you recall may well be different though. John Aylmer "Jennk" <jennnk40@hotma...

Coldfusion and Access and Field Names (It ain't Paradox)
Several years ago I wrote a database which keeps track of our campus ministry participation ... I'm no expert but it keeps working well The database is divided front and back end. Now we need to add a small front end for the web. The university uses cold fusion.. And I've taught myself how to do simple queries etc. The problem is when the database was originally written I had a couple of multiple word field names like First Name etc ( I didn't know any better.)Cold Fusion doesn't like them but to change them breaks every form report and query... Is there any utility which wou...

text fields to memo field
Is there a way to combine text fields into one memo field? This is an existing database. Some people in the database may have several "contact" entries where they have a "note" in a text field. I would like to figure a way to combine all the "note" fields for each person into one memo field. Thank you "RR" <texson552000@yahoo.com> wrote in message news:<S6HRb.969$ee6.74@fe2.texas.rr.com>... > Is there a way to combine text fields into one memo field? > > This is an existing database. Some people in the database may have seve...

Can't access to numerial fields using adoquery ...
I have posted this message before in several delphi news boards but never got any answer (help) to resolve the problem. I can't continue my work without resolving this problem, with is quit frustrating for me. Please, can sombody help me ? I'm working with Delphi 6 (SP2). I can't access to any numerical fields in my Access 2000 database via ADO. The following query (text value) works fine : ADOQuery1.Sql.Add('select * from DOSSIER'); ADOQuery1.Sql.Add('where DOS_EMPR_PRENOM = "' +'Maria' +'"'); try ADOQuery1.Open; but...

Count fields within a Field
hi I have a situation like this a field in a table has the following values of capacitors c1,c2,c5-c10 can I count the number of so that I get the result as 8 if so please help me understand how I can do that A single field should not be used to store multiple values like "c1,c2,c5-c10". You need to read: http://support.microsoft.com/support/kb/articles/Q100139.ASP HTH, TC "venkata" <ramanaindia@yahoo.com> wrote in message news:f8adfb54.0311231824.a2d6e8b@posting.google.com... > hi > > I have a situation like this > a field in a table has the ...

dbf field
Hello Group, I need to create a database based on records in an existing database. But, I only want to copy records if the field contains a specific value. The problem is, that the value I'm looking for is at the end of the data. For example, db field 1 - userid db field 2 - system db field 3 - manager, ITACC I only want to copy records if field 3 contains "ITACC". The field is 15 charactes long, but the ITACC could appear anywheres after the 4 character because there are managers that have names of only 4 characters. I know when I'm using dbfview, that I can que...

Access can't find the field referenced in your expression
I have a main form "Main" and a sub form "Sub" - the subform contains a list of relevant documents that I might want to associate with a record in the form on Main. These documents are contained in a table and include a field that has a hyperlink to them. I'm trying to use the AfterUpdate event - after the user selects a document from the dropdown list, I want the form to automatically copy in the hyperlink. Private Sub Document_AfterUpdate() Dim stDocName As String Dim stLinkCriteria As String '[fx-get document] is a form created just for accessing the documents in their table stDocName = "fx - get document" stLinkCriteria = "[evTitle]=" & "'" & Me![Evidence] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteria Dim doc_location As String 'DM_Other_Location contains the hyperlink <<<< it breaks here - can't find DM_Other_Location doc_location = Forms![fx - get document]!DM_Other_Location.Text DoCmd.Close 'Now put the hyperlink into the desired field Forms![analysis-document subform]!Location.SetFocus Forms![analysis-document subform]!Location.Text = doc_location End Sub MPS wrote: > I have a main form "Main" and a sub form "Sub" - the subform contains > a list of relevant documents that I might want to associate with a > record in the form on Main. These documents ar...

Help. after querydefs can't access field values
Using VB6 DAO with MS Access. After I do my querydef I can't see to access my field values. It did work before I started using the querydef. Here some of my code, Set wrk = CreateWorkspace("", "admin", "", dbUseJet) Set db = wrk.OpenDataBase(sDatabase) Set qdf = db.QueryDefs("Fax Numbers") Set rs = qdf.OpenRecordset(dbOpenDynaset) debug.print rs!first_name It fails trying to read the field values. What could I doing wrong? Thanks, jt ...

Blank fields in a repeating field
I have two tables, an Invoice and a Customs Manifest. The Invoice references a Product Catalogue through a relationship based on a Product ID in the Product Catalogue. I enter the Product ID in the Invoice through a Value List. Certain related fields then appear, such as colour, weight, cost etc. The Manifest has a relationship with the Invoice based on an Invoice #. Again my aim is to see certain fields from the Product Catalogue and the Invoice appear in the Manifest. Some of these categories or fields are omitted from the Invoice (MadeIn, ExportCodes etc) but now relevant. One field is Inv...

Netscape hidden fields - array
First off.. Thanks to Grant Wagner for help in a previous thread related to this one. I am at a total loss... I have multiple fields in a form with the same name. Lets call the fields with the same name "junk_array". My first field of junk_array is a input type=hidden. All the others fields in junk_array that follow are type=text. I can reference this first hidden field in IE with document.form.field[0].value. In, fact my form works absolutely wonderful in IE 6. However, netscape 4.7 does not recognize my first field in the array as the hidden field. Netscape sees the first visi...

combining multiple address fields into 1 address field
Hi, I have the following address fields in a table: flat_number house_name_or_number street village postal_town county postcode country Since I can't make all fields mandatory, I'd like to create a way of capturing only the data in fields that have data and putting that in another field called 'address' so that field reads conventionally (i.e each fields is on a separate line - and for lines to be moved up if no data is in the preceding field.) I had thought of making an update query using IIf statements but I can't see what character to use for 'carriage return'. Any help would be much appreciated. Regards, Hal. On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.uk> wrote: > Hi, > > I have the following address fields in a table: > flat_number > house_name_or_number > street > village > postal_town > county > postcode > country > > Since I can't make all fields mandatory, I'd like to create a way of > capturing only the data in fields that have data and putting that in another > field called 'address' so that field reads conventionally (i.e each fields > is on a separate line - and for lines to be moved up if no data is in the > preceding field.) > > I had thought of making an update query using IIf statements but I can't see > what character to use for 'carriage return'. > > Any help would be much appreciated. > > Regards,...

Web resources about - multiple conditions in a join, access doesn't like on (table1.field=table2.field and table1.field=table3.field) - comp.databases.ms-access

Resources last updated: 3/20/2016 10:24:23 PM