Query Problem - Is It Joins ?

Jan 20, 2006

I have two tables...'Suppliers' and 'Despatches'.

I'd like to create a query that shows all the suppliers that are in the Suppliers table as well as the last date of despatch (if any). Thus, this would be the maximum date that relate to that supplier ID. However, despatches that have a quantity of 0 should be excluded.

Currently I have the following, it's working fine but suppliers who doesn't have a corresponding despatch are not being listed. I need the report to list all the suppliers.

SELECT [Suppliers].[fldSupplierFullName], [Suppliers].[ID], Max([Despatches].[DespDate]) AS MaxOfDespDate
FROM Despatches LEFT JOIN Suppliers ON [Despatches].[SupplierRef]=[Suppliers].[ID]
WHERE ((([Despatches].[QtyLoaded])<>0))
GROUP BY [Suppliers].[fldSupplierFullName], [Suppliers].[ID];

Can anyone give me any direction?

Thanks :)

View Replies

Query With Many Joins

Feb 4, 2007

hi

i have 4 tables: tbEmploye, tbCirculaire, , tbCategorie, lienCirculaireEmploye

tbCategorie (every circulaire have a category)
-cirIdCategorie
-cirCategorie

tbEmploye
- empID
- empPrenom
- empNom
- empNumeroEmploye
- empMotPasse

tbCirculaire
- cirID
- cirIdCategorie
- cirNumero
- cirDescription
- cirDate
- cirNomFichier

lienCirculaireEmploye (link circulaire to employe
- cirNumero
- empNumeroEmploye
- dateLecture

SELECT tbCirculaire.cirNumero,
tbCategorie.cirCategorie,
tbCirculaire.cirDescription,
tbCirculaire.cirNomFichier,
tbCirculaire.cirDate,
tbEmploye.empNumeroEmploye,
tbEmploye.empPrenom ,
tbEmploye.empNom,
lienCirculaireEmploye.dateLecture
FROM (tbCirculaire INNER JOIN lienCirculaireEmploye ON tbCirculaire.cirNumero = lienCirculaireEmploye.cirNumero)
INNER JOIN tbEmploye ON lienCirculaireEmploye.empNumeroEmploye = tbEmploye.empNumeroEmploye
INNER JOIN tbCategorie ON tbCirculaire.cirIdCategorie = tbCategorie.cirIdCategorie
WHERE tbCategorie.cirIdCategorie IN ( 1,2 )

with this query, i get error: 3075

Syntax error (missin operator) in query expression
lienCirculaireEmploye ON tbCirculaire.cirNumero = lienCirculaireEmploye.cirNumero)
INNER JOIN tbEmploye ON lienCirculaireEmploye.empNumeroEmploye = tbEmploye.empNumeroEmploye
INNER JOIN tbCategorie ON tbCirculaire.cirIdCategorie = tbCategorie.cirIdCategorie

any idea?

View 2 Replies View Related

Query Joins

Sep 4, 2007

Hi all,

Simple problem, but my access and sql skills are very limited.

I have two tables. One containing a group of frequent customers with a column called 'member status'. Another table containing a group of non-customers. The addresses in both tables have been matched using group1 software.

I would like to create a query that shows me all the customers with 'member status' = 'A' and all the non-customers who live at the same address.

When i run the query, everybody comes up as 'member status'=A. I think this is because there is no 'member status' field in the non-customers table, and i have failed to make the appropriate join or parameters. Is there a way to design a query that will show 'member status' for those who have it, and will display a null for those who do not?

Any help is appreciated thank you!

View 4 Replies View Related

'JOINS' Need Advice On Joins

Aug 14, 2007

Hi again,

I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.

Any Advice will be greatly appreciated

18418

View 2 Replies View Related

Query Problem With 3 Queries - Joins

Apr 18, 2008

Hi- I have three queries:
Queries: Contain:
qryPrimary - primary_code, 1Reasons, EnteredDateCount as "CountP"
qrySecondary - 2ndary_code, 2Reasons, primary_ref, EnteredDateCount as "CountS"
qryTertiary - tertiary_code, 3Reasons, EnteredDateCount as "CountT", 2ndary_ref

In another query to combine data for a report, I need to see:
All the "1Reasons" and "CountP";
their corresponding Secondary "2Reasons", "CountS";
and finally the Tertiary "3Reasons", and "CountT" that correspond to the Secondary's.

Like a fool I thought it would be easy. I know it is probably a join problem, but I can't figure it out.

Any help would be appreciated. Stevie

View 1 Replies View Related

Query To Calculate Grade Using Lookup And Inner Joins

Sep 5, 2013

I have a database with:

2 tables, tblStudentMarks and tblGrades
2 Queries qryResults and qryStudents_Grades

1.qryResults:Calculates the TotalAverageMaths+English+Science)/3.
2.qryStudents_Grades:Calculates the students grades based on the TotalAverage by comparing the total average to the min & max marks on the tblGrades.

Grades are compared within a range specified in tblGrades with the following SQL wich is the query qryStudents_Grades:

Code:
SELECT SM.Student_ID, SM.Students, SM.TotalAverage, SM.Maths, SM.English, SM.Science, GD.Grade
FROM qryResults AS SM INNER JOIN tblGrades AS GD ON (SM.TotalAverage>=GD.MinMarks) AND (SM.TotalAverage<=GD.MaxMarks);

Question: i would like to also calculate the grades of the individual subjects in the same query (qryStudents_Grades), where the marks got by a student in the subject..say maths is compared to the (tblGrades) and grade is displayed in a colum say maths_grades for each student.

How can i do this for all subjects in one query. (qryStudents_Grades)?

View 7 Replies View Related

Dropping Query Joins On Long Text Fields

Nov 14, 2005

I have created some queries that are joined on long text fields (80-120 characters). I can save the query in the graphic query design window. But when I reopen the window I get messages that the Joins have been deleted. I cannot find any references to this issue. Does anyone have experience or info on this?

I don't need a lecture about the use of long text fields in Query Joins. And yes I have used Search first.

Thanks for your help.

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

Queries :: Select Query With Joins Returning Duplicates

Sep 18, 2014

I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".

I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID

All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.

Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.

Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.

View 1 Replies View Related

Query Using Joins To Disply All Records From Both Sides Of Join Type

Apr 14, 2008

I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.

How can I accomplish this?

View 1 Replies View Related

Access Projects - Query Designer - Update Queries With Joins Cannot Display In GUI

May 26, 2005

Hi, Big Jim here:

I am really not sure where to ask this one.....

My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.

Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".

Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.

Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.

Thanks in advance!

Big Jim

Set Up:
Windows XP
Office XP
SQL Server 7.0

View 4 Replies View Related

Develop A Query In MS Access 2010 To Join Two Tables Using Three Joins One Of Which Is A Date Range

Apr 15, 2015

I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.

ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.
[ABCTABLE]
ABCORDER
ABCPART

[code]...

View 4 Replies View Related

Query With Complex Joins (sub-query)

Oct 26, 2005

I need to execute a complex query. I can get the query to work if I break it into two queries, queryA and queryB, and then use queryB inside queryA. Note that queryB (combined query) produces the final results I want.

Here is the code for queryA
---------------------------------
SELECT
Table_Subgrantees.ID,
Table_Subgrantees.SDPISubNumber,
Table_Subgrantees.SDPIName1 AS SDPINameSubgrantee,
Table_Subgrantees.SDPIName2 AS SDPIName2Subgrantee,
Table_FormLogInfoSubgrantees2004.FormRequired AS FormRequiredSubgrantee, Table_FormLogInfoSubgrantees2004.FormRequiredComme nts AS RequiredCommentsSubgrantee
FROM Table_Subgrantees INNER JOIN Table_FormLogInfoSubgrantees2004 ON Table_Subgrantees.SDPISubID = Table_FormLogInfoSubgrantees2004.SDPISubID;

Here is the code for queryB
--------------------------------
SELECT
Table_AreaAbbrs.AreaAbbr,
Table_AreaAbbrs.AreaID,
Table_Grantees.SDPINShort,
Table_Grantees.SDPINumber,
Table_FormLogInfoGrantees2004.FormRequired,
Table_FormLogInfoGrantees2004.FormRequiredComments ,
Table_Grantees.SDPIName1,
Table_Grantees.SDPIName2,
QueryA.SDPISubNumber,
QueryA.SDPINameSubgrantee,
QueryA.SDPIName2Subgrantee,
QueryA.FormRequiredSubgrantee
FROM (Table_AreaAbbrs INNER JOIN (Table_Grantees LEFT JOIN QueryA ON Table_Grantees.ID = QueryA.ID) ON Table_AreaAbbrs.AreaID = Table_Grantees.AreaID) INNER JOIN Table_FormLogInfoGrantees2004 ON Table_Grantees.ID = Table_FormLogInfoGrantees2004.ID;

------------------------------------------------------------------------------------------------

I need to execute this query from ADO inside my code so I would like to come up with a single query that will return the results I want. I have played around with it for quite a while and everything I come up with generates messages like: "Join expression not supported".

Can anyone help me write this query as a single query?

Thank You,

Becky

View 1 Replies View Related

Multiple Joins In Multiple Table Search Query

Sep 21, 2004

I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.

I would like them to be able to query Retailers, Distributors and Products.

The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.

Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products

All retailers have at least one distributor BUT a retailer may or may not have ordered any products.

I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:

Code:

View 5 Replies View Related

Joins

Feb 5, 2008

Hi there,

Consider 2 simple tables

Name ID Pet
A 00 dog
B 11 cat
C 22 hamster

Name ID hasJob
A 00 no
B 11 yes
D 33 yes

how do I make a join to get this table?

Name ID Pet hasJob
A 00 dog no
B 11 cat yes
C 22 hamster --
D 33 -- yes

Help much appreciated.. thank you

View 3 Replies View Related

Joins?

Oct 16, 2006

Hey everyone! I just have a relatively quick question.

Situation:

I have a database where i have 2 tables. One table has items in one column, and the width, length, and height of the item. Another table has the exact same fields, except the only items are ones that need updated as far as their dimensions. The fields with those items include their new dimensions. How can I create a query to pull down all of the items with the correct dimensions?

Any help is greatly appreciated!

View 2 Replies View Related

Self Joins

Apr 15, 2008

I've got a table with a self join which represents a tree structure that can have variable depth.

I want to get a spreadsheet view of this for project review meetings. I can do a bunch of nested queries until I get all the branches but i would like something a little more dynamic, i.e. something that will automatically show the spreadsheet view of all branches no matter the depth of the tree.

So anyone got any suggestions on how to handle this?

View 3 Replies View Related

Joins

Apr 29, 2008

Hi Kids i'm a Newbie so be nice!

Only been using Access for a little over 6 months. It's an ongoing struggle but a worthy string to my bow.

I have been using a simple join to filter out matching fields from a bigger table that exist in a smaller table. If you like i create the smaller table around what i need to see from the bigger one. I hope that makes sense.

So what i want to achieve now is what i'm calling a "compliment join". This is where i use the smaller table to filter from the bigger table but I am left with everything but what was in the smaller table.

Any ideas...??

View 2 Replies View Related

Help With Inner Joins

May 9, 2007

Hi All,

Can anyone help me. I have a database table called orders. This contains and order status id. In a separate table i have the order status id and the what the id means i.e delivered, awaiting payment etc.

I need a record set that returns all the fields from the order table and the actual order status not the order ID.

I know i need to use a inner join but just can get it right.

Can anyone help me with this.

This is what i have but it return nothing:

Code:SELECT a.*, c.OrderStatusFROM Orders AS a INNER JOIN OrderStatuses AS c ON a.OrderStatusID = c.OrderStatusIDORDER BY OrderDate desc

EDIT:

Actually that is right and it does what, stupid me, frazzled brain at this time of night.

What i actually meant was how can i also pull the customers name from the database table Customer, based on the orderID?

Thanks in advance

Tom

View 3 Replies View Related

Subqueries/joins/SQL

May 12, 2005

I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing.
In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error:
The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement.
The way I would like to fix this problem is by creating subqueries in the SQL view, but I don�t know how to code it. If you know how or if you know a better way of doing this, please help! :)

View 1 Replies View Related

Unions And Joins

Nov 9, 2005

I would like to know the difference between these two concepts.

Would anyone care to explain it to me?

View 4 Replies View Related

Number Of Joins

Dec 5, 2005

Is there a limit to the number of joins allowed in a query?

View 2 Replies View Related

Joins To The Same Table

Nov 1, 2006

Hi there,

if i have a table with columns:
Teacher ID1 | Teacher Comment1 | Teacher ID2 | Teacher Comment 2 |

i also have another table that links the teacher ID with their names called [Staff Profiles]

how do i create a query that returns the names of both teacher 1 & 2. i have tried:

SELECT *
FROM [Subject Assessment] INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID1]=[Staff Profiles].[Teacher ID]) INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID2]=[Staff Profiles].[Teacher ID];

This throws an error. I have tried Aliasing but this also throws an error.

don't know whether this makes a difference, but the table which i gave was a join in the first place.....

i.e. teacherID1 | Teacher Comment1 is the tutor report of which there is one per student
teacher ID2 | teacher Comment2 is the subject report of which there are many per student

thanks in advance

View 2 Replies View Related

Between Dates Joins

May 29, 2007

Wierd Join needed...Here's my problem. it's been bothering for a bit...I have 2 tables, one with a date, and the 2nd table with 2 dates. I need to only pull the records from the 2nd table where the date in the first table come between them.TABLE 1DATEJOB#EMPLOYEE#TABLE2 JOB#EMPLOYEE#STARTDATEENDDATEThe query should take every line from TABLE1 and ONLY the lines from TABLE 2 that qualify.

View 5 Replies View Related

Multiple Joins

Jul 24, 2007

Hello,
I have the following code for a multiple join:

INSERT INTO [AppendAllFields]SELECT [TreatyList].[Treaty] AS [Treaty],[MLAC 42 Treaty Xref ER].[tai treaty] AS [TreatyType],[txn 01/04].[Policy_Number]
(and more other fields from [txn 01/04] table)
FROM [txn 01/04] INNER JOIN [MLAC 42 Treaty Xref ER] ON TRIM([txn 01/04].[Policy_Number]) = TRIM([MLAC 42 Treaty Xref ER].[Polnum])
INNER JOIN [TreatyList] ON TRIM([MLAC 42 Treaty Xref ER].[tai treaty]) = TRIM([TreatyList].[TreatyNo]);

Basically, the txn 01/04 table has a corresponding Polnum field in the MLAC 42 table, and MLAC 42 table has a tai treaty field, which corresponds to TreatyNo in TreatyList table.

However, when I tried to run this, I got an Syntax error.

Could anyone please help?

Thanks!

View 4 Replies View Related

Joins And Queries

Aug 31, 2007

Hi,

I have 3 tables.

From table 1, I join fields A, B, and C to fields A, B, and C on table 2. From Table 2, I join Fields 1 and 2 to Table 3. All the joins are Join 1.

When I pulled (Queried) fields D, E, and F from Table 1, field D from Table 2, and field D from table 3, I have a sum of $1000 under field (column) E from Table 1.

The second time I pulled data, I added fields A and B from table 1 to the query. However, I get a total of $1500 from the same column. i.e Field E from Table 1. I can understand that there will be more rows to provide further data breakdown, but I could not understnad why the total change.

Please help.

Thanks.

View 3 Replies View Related