How do I do this in one select query
Query one - or Base Table from fields above - 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]
-
-
FROM qryASMCur;
ASM is same as Salesmen into [qryDaysASM]
Summarized into a query summarizing Days by Profit Center, ASM#, ASMName, Period, Week, Date - SELECT DISTINCT qryASMCur.[Profit Center], qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMCur.CalDate
-
-
FROM qryASMCur
-
-
GROUP BY qryASMCur.[Profit Center], qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMCur.CalDate;
Then is summarized again into [qryASMDaysCT] - SELECT qryDaysASM.[Profit Center], qryDaysASM.AccountManager, qryDaysASM.ASMName, qryDaysASM.Period, qryDaysASM.Week, Count(qryDaysASM.CalDate) AS Days
-
-
FROM qryDaysASM
-
-
GROUP BY qryDaysASM.[Profit Center], qryDaysASM.AccountManager, qryDaysASM.ASMName, qryDaysASM.Period, qryDaysASM.Week;
Then finally joined into query [zqryBUCurWkv1] - 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
-
-
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)
-
-
GROUP BY qryASMCur.REGION, qryASMCur.Area, qryASMCur.[Profit Center], qryASMCur.NAME, qryASMCur.AccountManager, qryASMCur.ASMName, qryASMCur.Period, qryASMCur.Week, qryASMDaysCT.Days;
1 1078 Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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.
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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?
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |