Combining Two Query's

Jul 13, 2007

table : match
fields : season, matchday, clubA, clubB, goalsA, goalsB, points

I'v filled the table with matches. And also calculated how many points every match has. (1 for draw, 3 for a win). So I have 306 matches, for a league with 18 clubs.

Now I want to make a query that makes a ranking based on points.

I have made two queries.
1. PARAMETERS Waarde Value;
SELECT DISTINCT match.[Club A]
FROM match
WHERE (((match.season)=[Waarde]));

Which lists the 18 clubs distinctively in the season.

2. PARAMETERS Waarde Value, Club Text ( 255 );
SELECT Sum(match.points) AS SumOfPoints
FROM (Clubs AS CA INNER JOIN match ON CA.ID = match.[Club A]) INNER JOIN Clubs AS CB ON match.[Club B] = CB.ID
WHERE (((match.[goalsA])>[GoalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])>[goalsA]) AND ((CB.[Club naam])=[Club])) OR (((match.[goalsA])=[goalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])=[goalsA]) AND ((CB.[Club naam])=[Club]));

which gives me the total points of a club in a league if I input the season with the value Waarde and the Club with the value text.

How do I combine these two. For every resultrow of (1) I want the result of (2) presented next to it.

I'v tried these two queries and they both seem to work okay.. but how do I combine them ?

View Replies


ADVERTISEMENT

Combining Multiple Columns Into One Longer Column (without Combining Fields)

Jul 17, 2013

I trying to combine three columns that I have into one column without combining fields.

Currently what I have:
(see image below)

What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc

The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.

View 14 Replies View Related

Combining Tables In A Query

Sep 27, 2006

I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. Basically I need a list that has 5800 accounts, shows who has a phone number and who does not have a phone number. I have made a relationship between the two tables based on the person’s name. I used the code below and put the code in the “criteria” and “name” block. However, I received no results. When I remove the code from the query I receive only the results from list 2 (2300 accounts) and no data from list 1. Any Ideas?

FROM [5480 Master Data] INNER JOIN [5480 Meal Collection] ON [5480 Master Data].name=[5480 Meal Collection].name

View 3 Replies View Related

Combining Two Query Results

Jan 31, 2008

I am really struggling with this, but I need to combine these two queries.
They count the number of goals scored and conceded in a fixture between two teams to give the games played, games won lost and drawn and Goals for and against and points tally.

However, I can only get it to work for the Hometeam.
The second query gives the results for the Away team, but I need to combine the two together to give one league table with all the results and points etc combined.

Hope you can help :o

This displays the home team results and calculates everything correctly.


SELECT tbl_Fixtures.TEAMID, TBL_TEAMS.NAME,
COUNT(*) AS GAMESPLAYED, SUM(IIf((HOMEGOALS>AWAYGOALS),1,0)) AS GAMEWON,
SUM(IIf((HOMEGOALS=AWAYGOALS),1,0)) AS GAMEDRAWN,
SUM(IIf((HOMEGOALS<AWAYGOALS),1,0)) AS GAMELOST,
SUM(HOMEGOALS) AS GOALSFOR, SUM(AWAYGOALS) AS GOALAGAINST,
SUM((IIf((HOMEGOALS>AWAYGOALS),3,0))+(IIf((HOMEGOALS=AWAYGOALS),1,0))) AS POINTS
FROM tbl_Fixtures, TBL_TEAMS
WHERE tbl_Fixtures.TEAMID=TBL_TEAMS.TEAMID
GROUP BY tbl_Fixtures.TeamID, TBL_TEAMS.name;


This displays the Away team results and calculates everything correctly.


SELECT tbl_Fixtures.opponentID, TBL_TEAMS.NAME,
COUNT(*) AS GAMESPLAYED,
SUM(IIf((AWAYGOALS>HOMEGOALS),1,0)) AS GAMEWON,
SUM(IIf((AWAYGOALS=HOMEGOALS),1,0)) AS GAMEDRAWN,
SUM(IIf((AWAYGOALS<HOMEGOALS),1,0)) AS GAMELOST,
SUM(AWAYGOALS) AS GOALSFOR, SUM(HOMEGOALS) AS GOALAGAINST,
SUM((IIf((AWAYGOALS>HOMEGOALS),3,0))+(IIf((AWAYGOALS=HOMEGOALS),1,0))) AS POINTS
FROM tbl_Fixtures, TBL_TEAMS
WHERE tbl_Fixtures.opponentID=TBL_TEAMS.TEAMID
GROUP BY tbl_Fixtures.opponentID, TBL_TEAMS.name;



Now I just have to figure out how to combine the two, which is proving more difficult than I thought it would be.
My SQL isn't the best :o

View 3 Replies View Related

Need A Query Combining Non-used ID Numbers And The MAX + 1

Oct 10, 2005

I'm trying to create a combo box that would show the next ID number available to name a piece of equipment, as well as any numbers in the range that can be recycled.

This is in an equipment database where the equipment is named using the following convention: Division - equipment type - #. For example, a PC would be: DIV1-CPU-0001. My current drop down box generates the next number (so you can see the next ID for DIV1-CPU-0002). It's just a MAX+1 query.

However, if we have 10 PCs and DIV1-CPU-0008 was stripped and sent to recycling, then 8 is available as an ID number. I want my drop-down box to show, in that case, "8" and "11" as the options.

I've created a table with all the numbers and created queries that show which ID numbers aren't in use for each type of equipment. However, I can't figure out how to put all this in one query for my combo box.

Thanks!

View 2 Replies View Related

Combining Info In Tables For A Query

Sep 25, 2005

Hi there,

I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id.
Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables.
As i am dutch my english isn't that good.
Thanks in advance

1:
country_1 country_2
12
13
14
15
24
25
26
210
35
45
57
512
612
89
1011
1314

2:
idname capital_id
1Belgium 1
2France 4
3Netherlands 9
4Luxembourg 14
5Germany 6
6Italy 12
7Denmark 15
8United Kingdom 11
9Ireland 16
10Spain 17
11Portugal 18
12Austria 19
13Sweden 20
14Finland 21
15Greece 22

3:
idname peoplecountry_id
1Brussel 1370001
2Antwerpen 4970001
3Gent 2357001
4Parijs 21762432
5Lyon 4184762
6Berlijn 34000005
7Bonn 2930005
8Munchen 12670005
9Amsterdam 10380003
10Rotterdam 5760003
11Londen 66780008
12Rome 28260006
13Milaan 15150006
14Luxemburg 790004
15Kopenhagen 4820007
16Dublin 5260009
17Madrid 320000010
18Lissabon 80700011
19Wenen 151200012
20Stockholm 65100013
21Helsinki 48400014
22Athene 100000015

View 3 Replies View Related

Combining 2 Different Tables To Create A Query

Sep 26, 2006

I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.

View 4 Replies View Related

Combining Multiple Query Results

Dec 4, 2006

Hi,

I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.

I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.

For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...

So at the moment i have two queries per responsibility, and one pulling those two together i.e.:

New Work Items:
SELECT Count(ticketsds.id) AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count(ticketsds.id) AS [DS Closed]
FROM TicketsDS
WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));

Then i have this query pulling the two results together:
SELECT DSClosed.[DS Closed], DSNew.[DS New]
FROM DSClosed, DSNew;

Is there anyway of combining the first two queries into one, rather than have 3??

Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....

Many Thanks!
Gareth

PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...

View 2 Replies View Related

Combining Data From The Same Column In A Query

Feb 10, 2008

I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.

Tables: (only relevant fields shown)
SavingsClub - AccountNumber (PK), CustomerID, FlagAccountClosed, FlagDelete
SavingsClubAccounts - Account, Reservation, Redeemed (PK=another field)
SavingsClubReservations - Reservation (PK), FlagSpecialOrder

The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).

I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).

(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)

SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder,
Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete
FROM Suppliers
INNER JOIN (OrderDetails
INNER JOIN (((Customers
INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID)
INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account)
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation)
ON OrderDetails.Index = SavingsClubReservations.OrderID)
ON Suppliers.Index = OrderDetails.Supplier
WHERE (SavingsClubAccounts.Redeemed=False)
ORDER BY Customers.Name

I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.

SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False)
And SavingsClubAccounts.Account Not IN
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False))
UNION
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));

Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...

View 2 Replies View Related

Combining Fields In A Search Query

Jan 5, 2005

Hi,

I was trying to combine some fields into one with a search query,using sql. I was going to search for a postcode then add the first two lines of an address (which are at present in 2 fields) together seperated by commas ",". For instance:

Strd
12 Rock View
Str
Marston Cresent

change to :

12 Rock View, Marston Cresent

I can add fields together with an expression but cannot divide them by commas, any help appreciated.

M-.

View 6 Replies View Related

Queries :: Combining SQL Statements In A Query

Sep 16, 2014

I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.can I combine the 2 SQL statements in the 2nd query without amending them in any way.

The SQL statements are;

FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));

[code]...

View 7 Replies View Related

Queries :: Combining Fields In A Query

Jan 21, 2014

I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?

View 4 Replies View Related

Queries :: Combining Tables In A Query

Jul 9, 2014

I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.

the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.

i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it.

View 4 Replies View Related

Queries :: Combining Query Results

Nov 5, 2014

I have created a booking system for a travel agency and now when the booking is complete I want to export some summary data to the Excel. The issue is that I have separate tables showing lunch, tickets, tour guides, etc for every destination and I have also created queries showing the total for each destination but the issue is that I cannot combine all the results into one query and export that query to excel.

View 4 Replies View Related

Combining Select Query And Count Query

Jul 26, 2006

Ok, hope you can help me with this one.

I have a select query that pulls data from one table that includes the following data

MOS Grade Required Authorized



I have a separate count query that pulls data from another table and counts the following data

MOS Grade Assigned

How do I combine these 2 queries so the results I get will be

MOS Grade Required Authorized Assigned


For instance:

Select Query Says

MOS Grade Required Assigned
25C E4 1 1
42A O3 1 1
38A E7 3 3


Count Query Says

MOS Grade Assigned
25C E4 1
42A O3 1
38A E7 2


I want the results to read

MOS Grade Required Authorized Assigned
25C E4 1 1 1
42A O3 1 1 1
38A E7 3 3 2


Hope you can help.

View 5 Replies View Related

Queries :: Combining Query Not Showing All Results?

Jan 29, 2015

Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).

A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).

I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...

My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:

1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)

View 7 Replies View Related

Queries :: Combining Like Records In Union Query

Nov 25, 2013

How can I add the resulting records from a union query. The results of the union are something like this:

Quantity Item

2 Cats
3 Cats
1 Dog
4 Mice

What I need to display is

Quantity Item

5 Cats
1 Dog
4 Mice

How can I get the query to "do the math"?

View 3 Replies View Related

Queries :: Combining Data In Access Query Using Builder Function

May 13, 2014

I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:

NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed

I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:

NameColour
CarBlue;Green;Yellow

How i would do this.

View 5 Replies View Related

Combining 2 Query Columns Into 1 Column With Data In Separate Rows

Mar 29, 2012

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.

Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Access 2007, Windows 7.

View 5 Replies View Related

Queries :: Drop Down - Query Not Combining And Filtering Values Based On A Form

Jul 22, 2014

I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.

The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.

I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").

Using Windows 7,
Access 2010

Is there a way to select multiple values from the drop downs?

View 14 Replies View Related

Queries :: Combining Two Queries Without Union Query

May 7, 2014

Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.

Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.

If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.

View 10 Replies View Related

Combining Tables

Feb 19, 2006

I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.

View 3 Replies View Related

Combining ID Numbers?

Apr 6, 2007

Hello,

I maintain an Access database at my workplace, and have been asked to bring in information from other company locations. Unfortunately, there are conflicts; e.g. part number 12345 might be something totally different at another facility than it is here. My first instinct was pretty obviously to add a Location column and all queries would include that as well as the ID. However, someone else is telling me we should build metadata into a new part numbering convention. An example:

PlantA.12345 (all one field) would be a totally different part than
PlantB.12345

He says it would be very beneficial to ensure that, seeing only a part number, you would know where it was from. I understand his point, but it just seems like strange database design.

Has anyone done something like this, or considered it??

Thanks in advance.

View 6 Replies View Related

Combining Tables

Feb 1, 2005

Hi,

I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?

Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.

Thx,
Agamemnon

View 9 Replies View Related

Combining Records

Jun 22, 2007

I'm modifying a database for someone in our marketing department. The DB tracks shirts that are given to employees.

The area I'm having a problem with has to do with the shirt style.

The current categories offer:

Long Sleeve (White)
Long Sleeve (Black)

etc..

I want to combine those two so they fall under the simple category "Long Sleeve"

The problem I'm having is that when I do a find/replace, it will allow me to change all of the Long Sleeve (White) to Long Sleeve, but then when I go to change Long Sleeve (Black) to "Long Sleeve", it creates duplicate entries because the same order contained x amount of Long Sleeve (White) and x amount of Long Sleeve (Black).

Is there an easy/ automated way to combine these results into "Long Sleeve" and add the quantity fields together to show the total amount of shirts ordered?

View 3 Replies View Related

Combining 2 SQL Statements

Jun 1, 2005

Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.

I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.

SQL Statements


FIRST QUERY

SELECT tblApp.Time
FROM tblApp
WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));


SECOND QUERY

SELECT tblTime.Time
FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time
WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));


Rixx

View 4 Replies View Related







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