Queries :: Creating A Query With Self Join Tables

Feb 11, 2014

I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data

Order ID OrderNum Parent order id
1 MISC 2013-10
2 MISC 2013-10A MISC 2013-10
3 MISC 2013-10B MISC 2013-10

Now I am creating a query that shows the lifecycle of the order

MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached

SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum
FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID
WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));

How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..

View Replies


ADVERTISEMENT

Help Need Creating A Join Between 3 Tables

Sep 6, 2005

Hi there

I have made a form I am trying to bound 3 tables to.

The 3 tables are

tblPersonalInfo
tblBookingInfo
tblSkills

How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking.
With the skills....each person can have lots of different skills.

Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.

I have made 3 tables

tblPersonalInfo
PersonalID
chrForename
chrSurname
dtmDOB
chrEmail

tblBookingInfo
BookingID
chrJobTitle
curPayRate
PersonalID
SkillsID

tblSkills
SkillsID
Skills


I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo
I have joined tblSkills to the table tblbookingID through SkillsID

I created a query using all 3 tables and have used this to bound the form....

The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.


I would like to be able to click to add a new person and also new booking and new skills

Currently I am able to add new personal details but nothing else.

Can anyone see what I am doing wrong?

Thanks for taking the time to read this

View 11 Replies View Related

Queries :: Join Data Of Two Tables Using Query

May 3, 2015

I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.

View 4 Replies View Related

Need Some Tips In Creating Relationship Join Lines Between Tables

May 16, 2005

Hi, I am new here and need some help with MS Access97. I am looking for a more convenient way to join tables together. The usual way that I know is to drag the field(s) in one table and drop it in the other table (within the relationship window).

Is there any other way that I can do the same without drag and drop (keyboard instead of mouse)? This is real problem to me when I need to do some modification and have lots of tables scatter across few pages and the vertical scroll of the Relationship window is not working properly with drag & drop technique.

Please help. Many thanks! :)

View 2 Replies View Related

Problem Creating JOIN Query

Oct 2, 2005

i have joined a jobs table and payout table together and performed a query.
however i would like to perform SUM by the job_id but crew_id fields are duplicated.

(the payout table comes from a subform that doesnt differentiate crew type so my SUM totals all the payouts whereas i want to sum the payouts by job_id when the crew id are repeated.

TOTAL JOB_ID CREW_ID
$5000 2 2
$3290 2 2
$1000 2 7
$2500 2 7

need to return $8290 for one query (5000+3290)
then $3500 from another (1000+2500)

View 2 Replies View Related

Queries :: Creating Query To Pull Info From Multiple Tables

May 7, 2013

I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.

What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.

So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.

View 9 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 :: Join Two Tables For Further Manipulation

Feb 1, 2015

I have two tables: products and orders.I would like to query the products only with orders using the product code and/or model number and join up all product and order information for further manipulation.

View 2 Replies View Related

Queries :: Join Data Between Two Tables?

Sep 3, 2014

I have two tables

first table : customer name,QUANTITY ,delivery date,product type

second table : customer name,QUANTITY ,delivery date,product type

i want to make query to sum the QUANTITY from the two tables and group by customer name and delivery date

View 14 Replies View Related

Queries :: Join Two Tables With Duplicates

Oct 19, 2013

I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.

The two tables are spend and export
Spend
+-------------+--------+-------+-------+
| Country | Metal | Month | Spend |
+-------------+--------+-------+-------+
| China | Iron | Jan | 100 |
| China | Iron | Feb | 200 |
| China | Iron | March | 300 |

[code]...

View 2 Replies View Related

Queries :: Join 2 Tables But Filter Records

Jul 20, 2015

I want a query that gives me something like this from 2 different tables:

table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02

query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02

Is that possible?

View 3 Replies View Related

Queries :: How To Join Tables With No Matching - Unique Fields

Jun 16, 2013

I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).

I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.

View 8 Replies View Related

Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.

Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN

Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?

View 6 Replies View Related

Join Two Tables In A Query

Feb 13, 2005

I would like to join two tables with one query. Here is the twist though.
The first table would have a set of value like so:

0
50
100
300
500
750
1200
1500

the second table would have values as such

0
1000
1300
1500
1750

by just setting up the relationship to show all values from the sirt table and only those from the second table that match I get a list of values that will not work for me. What I would like to reflect
in the query is all the values from the first table and if there is for instance no value of 500 in the second table I would like to return 1000. For instance there is a zero in each table so that would be output twice. There is however no value of 50 in the second table so the query would read 50 and null or blank when I would like it to read 50 and 1000 (The next value higher).

View 3 Replies View Related

Query - JOIN Multiple Tables

Jun 11, 2006

Hello All;

Could someone please assist me on this?

The following is the Tables:

ContentTypes ---> Site Content Directory
Cats ---> Categories
Sierra ---> Store Content for Sierra
BrushStrokes ---> Store Content for BS

I have "tried" to create a QUERY for the 4 Tables
(The code that I have, originally came with 3-Table Query connection
I have added in the 4th Table "BrushStrokes")

This is the Query that I created. Before the code, a little insite

At first there was: RIGHT & LEFT JOIN's, But I would receive this:
Error 3258
After not really finding any good examples of how to create the
2- Queries and have then work together as one.
I decided to try something else, which was to create all [INNER JOIN]'s
Which got rid of the Error, and the code still will not work in my site.
========
Microsoft JET Database Engine error '80040e14'
Syntax error in JOIN operation.
=======

So, here is the code, please let me know what is wrong with it?
And if possible, a good example of creating the [2 - Queries] that work as one.

Thank you all
Carrzkiss
-----------------------SQL QUERY------------------
SELECT Cats.CatID, Sierra.CatID, BrushStrokes.CatID, Sierra.ContentID, BrushStrokes.BSContentID, Sierra.Product_Title, BrushStrokes.BSProduct_Name, Cats.CatDescription, Cats.CatTypeId, Sierra.Product_Th_Image, BrushStrokes.BSThumbnail, Sierra.Our_Price, BrushStrokes.BSPrice, Sierra.Retail_Price, BrushStrokes.BSRetail, Sierra.Aff_Name, BrushStrokes.Aff_Name, Sierra.Brand_Name, BrushStrokes.BSArtistName, Sierra.YAvailable, BrushStrokes.BSStatus, Sierra.Percent_Savings, Sierra.Unique_Product_ID, BrushStrokes.BSProductID, Sierra.Main_Cat, BrushStrokes.BSCol_Cat, ContentTypes.ContentType, Sierra.Product_Description, BrushStrokes.BSDescription
FROM ((ContentTypes INNER JOIN Sierra ON ContentTypes.ContentTypeID = Sierra.ContentTypeID) INNER JOIN BrushStrokes ON ContentTypes.ContentTypeID = BrushStrokes.ContentTypeID) INNER JOIN Cats ON (BrushStrokes.CatID = Cats.CatID) AND (Sierra.CatID = Cats.CatID)
WHERE (((Sierra.Display)=1) AND ((BrushStrokes.Display)=1));
-----------------------------END SQL QUERY------------------

View 3 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

How To Join Tables With Similar Values In Query

Apr 5, 2006

Can someone please help with the following:

I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.

Thanks

View 6 Replies View Related

Query Multiple Tables Using INNER JOIN And Multivalue Fields

Jan 28, 2013

Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.

tblEmployees

ID (autonumber) 3
EmployeeName John
EmployeePhone 555999555
EmployeeLocation New York

tblClients

ClientID (autonumber) 1 , 2 , 3
ClientName ABC Company , XYZ Company, PQR Company
fkeyLocationID

tblLocations
LocationID 1 , 2
Location New York , Chicago

tblEmployeeClients (junction table)
fkeyID 3
EmployeeClients (multivalued number) 1,2

The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.

I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).

The report (for the example above), should look like this:

Name Clients Supported
John ABC Company, PQR Company

Currently, I am able to get :

John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.

View 1 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Join Fields Query

Jul 28, 2013

I tried doing a search but couldnt find the i needed.

Category_name Sku
cars 1
Trucks 1
Bikes 1
Phones 2
Tablets 2
Hats 3

and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.

so it looks like this

Joined_categories sku
Cars|Trucks|Bikes 1
Phones|Tablets 2
Hats 3

And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.

View 14 Replies View Related

Queries :: More Than 1 Inner Join Slows Query Too Much

Jul 30, 2015

I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.

To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.

Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.

If I run the query:

Code:
SELECT VI.*
FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;

It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.

Now if I run:

SELECT VI.*
FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku)
INNER JOIN SOFF ON VI.SOff = SOFF.Soff;

It takes either 15 seconds, or up to 100 seconds.

I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.

Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.

It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.

If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?

View 7 Replies View Related

Queries :: One To Many Join / Count Query

Oct 16, 2013

I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:

Code:
SELECT
[MainTbl].Location,
Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod],
Count([Tbl1].[Agent Name]) AS [NoCompleted],
Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]

[Code] .....

But I get the following results:

Code:
Location EvaluationPeriod NoCompleted NoPassed
Location1 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location1 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location1 Month1 Tbl3.NoCompleted Tbl3.NoPassed
Location2 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location2 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location2 Month1 Tbl3.NoCompleted Tbl3.NoPassed
etc..

View 4 Replies View Related

Queries :: Update Join Set From Table To Query

Aug 29, 2013

I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...

Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.

View 1 Replies View Related

Queries :: Creating A Row Based On Difference Between Tables

Jul 3, 2013

I am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.

My data is as follows

TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000

TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000

I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -

TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0

View 5 Replies View Related

General :: Cross Join To Create Ledger Type Query From 4 Tables

Apr 2, 2013

I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .

See the attached image ...

View 7 Replies View Related

Queries :: JOIN Query To Return Most Recent Record?

Aug 18, 2014

I have two tables with a one to many relationship. The tables are linked by the INDEX column.

EXAMPLE:

Code:
TABLE_1
INDEX NAME
1 Name_A
2 Name_B
3 Name_C

TABLE 2
INDEX NUM_INDEX STATUS
1 1 REJECTED
1 2 REJECTED
1 3 OPEN
2 1 CLOSED
3 1 REJECTED
3 2 OPEN

I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).

Example:
Name_A, OPEN
Name_B, CLOSED
Name_C, OPEN

SQL that I have now.

Code:
SELECT A.FIN_Finding_Number, B.Max_Index
FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index
FROM TBL_Response GROUP BY RES_Finding_Index ) AS B ON A.FIN_Finding_Index = B.RES_Finding_Index
WHERE (((A.FIN_Finding_Index)=34));

This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.

View 2 Replies View Related







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