473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Subquery help 4 queries produce one results

How do I do this in one select query

Query one - or Base Table from fields above

Expand|Select|Wrap|Line Numbers
  1. SELECT qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.LocalChain, qryASMCur.Customer, qryASMCur.CustAcct, qryASMCur.BillingDocument, qryASMCur.DistType, qryASMCur.[Calendar day], qryASMCur.Period, qryASMCur.Yr, qryASMCur.Qtr, qryASMCur.MO, qryASMCur.Week, qryASMCur.CalDate, qryASMCur.Cases, qryASMCur.NSP, qryASMCur.Margin, qryASMCur.Returns, qryASMCur.Rtn, qryASMCur.U10Cs, qryASMCur.U10, qryASMCur.[No]
  2.  
  3. FROM qryASMCur;
ASM is same as Salesmen into [qryDaysASM]

Summarized into a query summarizing Days by Profit Center, ASM#, ASMName, Period, Week, Date

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qryASMCur.[Profit Center], qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMCur.CalDate
  2.  
  3. FROM qryASMCur
  4.  
  5. GROUP BY qryASMCur.[Profit Center], qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMCur.CalDate;
Then is summarized again into [qryASMDaysCT]

Expand|Select|Wrap|Line Numbers
  1. SELECT qryDaysASM.[Profit Center], qryDaysASM.AccountManager, qryDaysASM.ASMName, qryDaysASM.Period, qryDaysASM.Week, Count(qryDaysASM.CalDate) AS Days
  2.  
  3. FROM qryDaysASM
  4.  
  5. GROUP BY qryDaysASM.[Profit Center], qryDaysASM.AccountManager, qryDaysASM.ASMName, qryDaysASM.Period, qryDaysASM.Week;
Then finally joined into query [zqryBUCurWkv1]

Expand|Select|Wrap|Line Numbers
  1. SELECT qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, Sum(qryASMCur.Cases) AS Cases, Sum(qryASMCur.NSP) AS NSP, Sum(qryASMCur.Margin) AS Margin, Sum(qryASMCur.Returns) AS Returns, Sum(qryASMCur.Rtn) AS Rtn, Sum(qryASMCur.U10Cs) AS U10Cs, Sum(qryASMCur.U10) AS U10, qryASMDaysCT.Days, Sum(qryASMCur.[No]) AS [No] INTO BUWkReview
  2.  
  3. FROM qryASMDaysCT RIGHT JOIN qryASMCur ON (qryASMDaysCT.[Profit Center] = qryASMCur.[Profit Center]) AND (qryASMDaysCT.AccountManager = qryASMCur.AccountManager) AND (qryASMDaysCT.Period = qryASMCur.Period) AND (qryASMDaysCT.Week = qryASMCur.Week)
  4.  
  5. GROUP BY qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMDaysCT.Days;
Sep 24 '11 #1
1 1078
NeoPa
32,556 Expert Mod 16PB
Check out Subqueries in SQL. This should point you in the right direction.
Sep 26 '11 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: Stu | last post by:
I have the following code which I am having difficulty getting to work. I think it may be a problem with the $got query that is being run as if I set that to a set value then my site seems to run...
2
by: lev | last post by:
CREATE TABLE . ( NULL , , (44) ) ID is non-unique. I want to select all IDs where the last entry for that ID is of type 11.
8
by: Neeper | last post by:
I'm trying to pull the last 10 records from a transactions from a table using this query: SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10 But I want to display the rows in...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
5
by: Mike MacSween | last post by:
This as the row source for a combo: SELECT qryRole.RoleID, qryRole.Role FROM qryRole WHERE (((qryRole.RoleID) Not In (SELECT RoleID FROM qryRoleEvent INNER JOIN qryEvent ON qryRoleEvent.EventID...
7
by: Alex L Pavluck | last post by:
Hello. I have my data setup as follows: Main MEMNAME NAME TYPE LABEL Variable MEMNAME VARNAME NUMBER Format MEMNAME FMTVALUE NUMBER
2
by: Billy | last post by:
This string is supposed to provide all records from an MDB database that match the courier and date specified in the query. I Response.Write the query and I get a date as 1/27/2007. The date...
4
by: Belgarath | last post by:
Hello all, I'm a newbie in Access... And before starting to explore this software, I would like to know if it's possible to automate everyday queries result in Excel . If it's possible can...
21
by: bruno_guedesav | last post by:
I've made a function to fetch all results as an array of result- arrays. Getting the result arrays is easy, via mysql_fetch_array, and function itself is quite simple, as follows: function...
7
by: kent christensen | last post by:
So i have all my queries displaying results in my form. I was wondering how i can stop the queries from popping up when i run them via a button on the form?
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.