Help With Outer Join Query In Acess 2003

Apr 11, 2007

hello,

I have query , which has got 2 outer joins. The query is:

SELECT A.Project_ID, A.Title, A.comm1 AS Comments, A.Partner AS PM, A.Staff_Assigned AS TL, A.Contact_Name AS FL, A.MD, A.Status, A.Project_Type,
ISNULL(B.Delivered_Date, B.Delivery_Date) AS Start_Date, ISNULL(C.Delivered_Date, C.Delivery_Date) AS End_Date
FROM dbo.PROJECT A LEFT OUTER JOIN
dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = "Start Date" LEFT OUTER JOIN
dbo.PROJDATE C ON A.Project_ID = C.Project_ID AND C.Date_Type = "End Date"

Can anybody help me out the error with this. I get an error : at dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = "Start Date" .

Do I need to use any parantheris or change anthing.

Thanks

View Replies


ADVERTISEMENT

Query Behavior Acess 2003

Aug 31, 2007

I've just started using 2003 and had a query give me incomplete results. One table contains 6 numbers stored as text joined to the corresponding code in the data set table. Both fields are formatted as text. When using the table as criteria I do not get all of the expected records, I had to type in("1000", "2000",...) in order to retrieve all of my data.

Any ideas on what could be happening?

I use the text setting to avoid problems importing the data set which begins life as a csv file.

View 1 Replies View Related

How To Right Outer Join Several Tables In A Query?

Aug 23, 2006

Hi!

Thanks for all help so far, this forum is great ;)

I have 3 tables: Order, Product and Names.

I want to list All orders, no matter if they hold a key to a product or not, and no matter if this product has been given a name (in the table name) or not.

To make it even more complicated, the customer can give upp to 16 namesuggestions, they are all numbered from 1-16. But If they are given a namesuggestion, i only want to present the first one (nr 1).

Relations: 1 order -> 0 or 1 product.
1 order -> 0 or up to 16 names, of which i only want to display nr 1.

Any suggestions?
I am thinking of preparing the data in a temporary table, but would be glad if i did not need to do so....

View 1 Replies View Related

Edit Fields In An Outer Join Query

Mar 13, 2008

I have 2 tables (tblItemsQB and tblItemInfo) that have a 1 to 1 relationship linked by the "PartNumber" field. The tblItemsQB table contains a description field and 2 price fields (this table is downloaded from Quickbooks). The tblItemInfo table contains additional fields with information on the part that isn't contained in Quickbooks, like OrderType, Phase, SubSystem.

I import the items from QB into tblItemsQB and then do an outer join with tblItemInfo to link all the information for the part. If I have created a new part in Quickbooks, when I do the outer join (I include all records from tblItemsQB) I can see the new part but since the part number doesn't exist yet in tblItemInfo, I can't add the information to the fields in that table.

What is the easiest way to create the new PartNumbers (those that exist in tblItemsQb but not in tblItemInfo) in the tblItemInfo table so I can add the other information to it?

thanks
mike

View 2 Replies View Related

Outer Join Creating Duplicates In Dynamic Query Search Results

Jan 22, 2008

Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon

View 4 Replies View Related

Queries :: Nested Query - Left Outer Join Not Returning All Records

Oct 1, 2014

I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..

Code:

Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)

View 1 Replies View Related

Full Outer Join Queries And Make Table / Append Query

Jan 20, 2014

I am fairly new to Acces 2010.I have two seperate tables hat I need to use to compare data. As you can see table A and table B have some of the same item numbers but they also have different item numbers that are not other table. Also some of the item numbers are duplicated in each table but that is okay because the cost of the item is different. Both tables contain item numbers for the products. I want all of Table A item numbers including the item numbers that are in table B. But I also want Table B item numbers except for the item numbers that are also in Table A. In the real raw data file some of the item number fields are blank but the other fields have values. How should I query these tables so that I achieve the correct results?

Table A
Item Num Costof Item Supplier Sales Tax Purchase Month
1234 $1.00 Walmart $2.00 Dec 2013
2222 $4.00 Walmart $1.00 Dec 2013
2222 $2.00 Walmart $1.00 Dec 2013
1276 $3.00 Sams club $1.50 Dec 2013
7898 $5.00 Texaco $5.00 Dec 2013
4567 $3.50 Food Lion $1.00 Dec 2013

[code]....

View 3 Replies View Related

Outer Join Help

Jan 30, 2007

Okay i have 3 tables
I would like to join them all by a field called twp, but the problem is in some tables the field twp might contain more or less rows than table2 and table3 or vice versa.
Is there a way to include all the rows from all tables in a query.
Suppose if table2 has a column which table1 doesnt have, then it will just show table1 column as blank etc..
Iv tried just doing an outer join on all the tables but for some reason access doesnt allow it.

Thanks in advance

View 5 Replies View Related

3 Table Join, Outer And Inner.

Jun 13, 2006

Hey Folks,

I'm trying to write a query that will get all the records from one table, and only certain records from others. (Access2000) I'm getting a "join expression not supported" error however, so my question is two fold.

1. Does Access2000 support an inner and outer join the in same query?
2. Does my query below just have a syntax error thats causing the problem?

Here is the SQL, i've posted a rough table schema below too. (please note i'm using ColdFusion to query the database, so the #tstiid# is a variable passed to the query)

SELECT ((iss.iid, pg.pgid, pg.pg_name
FROM issues iss
LEFT JOIN issuespages ip ON ip.iid = iss.iid)
RIGHT JOIN pages pg ON pg.pgid = ip.pgid
WHERE iss.iid = #tstiid#




Database layout:


Table 1, named: issues

iid | issuename | a ton of other fields
------------------------
1 | April 2006 |
2 | May 2006 |

table 2: pages

pgid | pagename | pagecontent | etc
-------------------------------------
1 | Contact us | To contact us, use the following...
2 | News | Upcoming news...

table 3: issuespages

ipid | iid | pgid
--------------------------
1 | 1 | 1
2 | 1 | 2


Thanks in advance for anyones time!

View 1 Replies View Related

SQL Outer Join Statement?

Dec 14, 2007

Hi,

I have a problem about SQL outer join

I have two tables

-----------
table one

id value1

1 a
2 b
3 c

-------------
table two

id value2
2 d
4 e
5 f

---------------------
I like get the results

id value1 value2
1 a 0
2 b d
3 c 0
4 0 e
5 0 f

* if no value put 0 .I am using SQL Server 2005 now

I try left join ,right join and full join, but I can not get the results.Please help me.

Thanks for help

Mark

View 1 Replies View Related

Using A Wildcard On A Like Left Outer Join

Jun 21, 2007

Hi All

I have been running a query to find out the name and addresses of some of our customers but some data keeps coming in to the results that i dont want to see.

The data is in the Name column so I have built a second table and put the names of the items i do not wish to see in the second table using a left outer join and selecting null or not null. This works great if i know all the combinations of the name such as smith i.e d smith david smith, sam smith dd smith etc.

I would like to enter Smith into the table and have my query return everything except with the word smith in.

I can code this into the query with not like *smith* but that means everytime i want to go into it i would have to enter the query not the front end

Anyone any ideas?
Thanks

C:p

View 4 Replies View Related

Requirement Of Outer Join As Default

Nov 2, 2005

Hello All,

Beginner level question!

I am working on a schema that might need outer joins for some of the queries and thereby reports I will need to produce. So, I wanted to get an idea from the pros about the following strategy:

How about leaving all the joins in the Relationships to begin with as Inner Join and change them to Outer Join as required (for a given Query. I believe this is possible)? What are the pros and cons of this approach.

Thank you

View 3 Replies View Related

Full Outer Join For Dummies

Mar 21, 2007

I'm the dummy in question. I'm new to Access and have a fairly simple database that I'm using to find potential customers. I am targeting them by two criteria using two different queries (both queries are looking at different fields of the same table). I want to create a combined query that pulls ALL of the results from both of my target queries. Searching around this forum I see that I need to create an Outer Join which Access doesn't exactly do. I also see that there are ways to simulate this, but I can't seem to figure out those ways. If someone could please explain this to me in a very simple way, I would be very grateful. Thank you.

View 14 Replies View Related

Queries :: Possible To Use SQL OUTER JOIN Or UNION

Jan 9, 2015

I have a table and a query. The first 4 fields of the table correspond to that of the query. The query does not have any other fields, but the table has 26 more fields. Is it possible to use SQL OUTER JOIN or UNION or whatever to append the data of the query to the table or do I have to go with recordsets of VBA?

View 6 Replies View Related

Queries :: Full Outer Join In Access 2010?

Jan 21, 2015

I would like to create a full outer join in Access2010 between two tables with many to many relationship.

So I have tblServer and tblApplication and there is also the ServerID-ApplicationID table that connects these tables.

I found online many examples but all of them where for two tables with one-to-many relationship.

View 6 Replies View Related

Tables :: Cannot Enter Value Into Blank Field On (one) Side Of Outer Join

Apr 28, 2014

My goal is to create a form that allows me easily edit what State each City is assigned to, and edit what Country each State is assigned to. I have 3 tables:

tbCities
CityID (Primary Key)
StateID (Foreign Key from tbStates)
City

tbStates
StateID (Primary Key)
CountryID (Foreign Key from tbCountries)
State

tbCountries
CountryID (Primary Key)
Country

For right now, I want to focus on just States and Cities... so I also have this query:
quCity_to_State
Field: City
Table: tbCities

Field: State
Table: tbStates

The join type for the relationship between tbCities and tbStates is: Include ALL records from 'tbCities' and only those records from 'tbStates' where the joined fields are equal. That way I can see all the cities and the state they are assigned to.

However, when I view the query in datasheet view and try to type anything into the State field of this query, I keep getting this error: "Cannot enter value into blank field on 'one' side of outer join". I figured the source of the form should be this query instead of the tables directly, but if I can't input data into the query then I wont be able to input data into the form.

What am I doing wrong?

View 10 Replies View Related

Forms :: Can't Enter Data Into Blank Field On One Side Of Outer Join

May 21, 2014

I think I have just finished designing my database and I tried to test it and I couldn't enter any new records as it says "Can't enter data into blank field on "one" side of outer join" whenever I try and enter info and I don't know much SQL to work out what has happened. It probably causes this too but I also cannot select check boxes.

The form where I try and enter the info is called Crisis_support_workers v3. I have attached my database so you can look at what I have done.

View 1 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

"OUTER JOIN" On Access

Jun 19, 2006

Hello Everybody...

Pls, does someone have any idea of how I can do an "OUTER JOIN"-like effect on an Access querie? I tried to use this statement in his standard but it didn't work...

Thanks!

View 1 Replies View Related

Queries :: Access 2003 - Join Via Calculated Field

Apr 28, 2013

I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.

So I have Table A:MemberID (autonumber, Primary Key)

NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level

I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:

TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8

So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.

Table B: I want a list of "friendly name" for the different training levels.

TLID (Number, No duplicates)
TLFriendlyText (Text)

So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.

Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].

How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.

View 4 Replies View Related

Query Is Too Complex In Ms Acess 2.0

Jun 13, 2006

Hi ,

I have two queries . one to list the data for the current month and the for the pervious month which I used to get a union query. From the union query I created another query which I had put in the report_open as a query def.

It works perfectly well on small databases upto 50 mb but when tested with 70mb databases ,report tabkes 30 minutes to execute and also throws the error"query is too complex"

please advise how I can avoid this error and also speed up the report.
Thanks in advance.

the code under report_open looks like this

Dim MyWorkspace As WorkSpace, MyDB As Database, MyQuery As QueryDef
Dim MySet As Recordset
Dim psSql As String
Dim inputno As Integer

Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDB = MyWorkspace.Databases(0)
inputno = store

psSql = "SELECT DISTINCTROW QryRepCMISUnionLargeMovements.Customer_Name,"
psSql = psSql & " QryRepCMISUnionLargeMovements.CUST_ID, QryRepCMISUnionLargeMovements.CRG_CODE,"
psSql = psSql & " QryRepCMISUnionLargeMovements.CurrLimits1, QryRepCMISUnionLargeMovements.PrevLimits1,"
psSql = psSql & " QryRepCMISUnionLargeMovements.Lim1Var AS Lim1Var11, QryRepCMISUnionLargeMovements.CurrLimits2,"
psSql = psSql & " QryRepCMISUnionLargeMovements.PrevLimits2, QryRepCMISUnionLargeMovements.Lim2Var AS Lim2Var11,"
psSql = psSql & " QryRepCMISUnionLargeMovements.Input_Section"
psSql = psSql & " FROM QryRepCMISUnionLargeMovements;"


MyDB.QueryDefs("a").SQL = psSql
Set MyQuery = MyDB.QueryDefs("a")

View 3 Replies View Related

Query With Multiple Outer Joins Gives Wrong Result

Dec 15, 2007

Hello,

My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts).
The relevant fields are:

PERS
pers_id (long) ---> primary key
pers_forename (text)
pers_surname (text)

CONT
cont_id (long) ---> primary key
pers_id (long) ---> foreign key
come_id (long) ---> foreign key
cont_number (text)

The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".

Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ...
I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1):
SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone
FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id
WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1));
This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.

But now i also want to see the email address (come_id = 5):
SELECT PERS.pers_forename, PERS.pers_surname,
CONT.cont_number AS Phone, CONT_1.cont_number AS Email
FROM CONT AS CONT_1 RIGHT JOIN
(PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id)
ON CONT_1.pers_id = PERS.pers_id
WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1)
AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5));
It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression.
The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither!
I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ...
I hope that someone of you can explain this.

View 3 Replies View Related

Using SQL In Acess???

Apr 10, 2007

Hi all,
I am creating tables in SQL view in Acess, Have managed to create the tables, but am having a little trouble understanding what to do next?

I have an INSERT statement to add names, addresses etc, but cant seem to get it to work.

I am trying to insert into the SQL view in queries, thats where i have created the tables.

Should i use the same querie view or another one, or simply imput the information manually.

INSERT INTO Student ( Student_No, Name, Address, Tel_No, Course_No )
SELECT 'A001', 'Jones B', '12 New Road', '469006', 'HNC34';
this is the statment that i am trying to use:

hope someone can help
doj

View 14 Replies View Related

MS Acess Db Security - Help!

Jun 19, 2007

Dear All,
I have a database with build in (access) users and passwords. I have a admin user but I forgot the password to this user.

Is it possible to retrieve this information somehow?

Thx in advance,
Dzordz

View 4 Replies View Related

BCP Equivalent In MS Acess

May 20, 2005

Hi Folks!

Need help! Is there a BCP equivalent in access. I want to load 2 million rows into an access database.

Any pointers will be of great help.

Thanks,
Moksha

View 1 Replies View Related

Acess Numbers

Nov 30, 2004

Im trying to calculate two feilds sounds easy right.
My numbers are stored 0000003000 and 0000002000
the feilds are Price and price two in trying to caluclate these but what im gettign is 00000030000000002000
how can i either check to add only numbers or add them and get 0000005000

this is money feilds

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved