nested query hell

i'm trying to use a nested query (previous post under counting problem but no 
solution found).  Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre, 
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, dbo_Appt.ApptTypeID, 
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date.  What I want 
though is a count with different where clauses for each distinct apptdate

For grouping [Pre] a count of apptID with an apptdate between 0-30days prior 
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than the 
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a the 
temporary value of distinct date to compare.  All help is appreciated.  thx. 
Ian


0
Utf
1/21/2008 12:38:00 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
588 Views

Similar Articles

[PageSpeed] 25

update -- the following sql gives me the distinct date and has the subquery I 
think i need but all the counts are 0.  How do I carry the 1st column result 
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
 (SELECT Count(ApptID) FROM dbo_appt WHERE apptdate  Between distdate-30 And 
distdate) AS Pre
FROM dbo_Appt;


"Ian" wrote:

> i'm trying to use a nested query (previous post under counting problem but no 
> solution found).  Here's what I've done so far:
> 
> SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre, 
> Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, dbo_Appt.ApptTypeID, 
> dbo_Appt.OfficeID
> FROM dbo_Appt
> GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;
> 
> With this I'm getting a count of apptID for each distinct date.  What I want 
> though is a count with different where clauses for each distinct apptdate
> 
> For grouping [Pre] a count of apptID with an apptdate between 0-30days prior 
> to the distinct [apptdate] and an apptstatus of 3 or 4
> 
> For grouping [Post] a count of apptID with an apptdate greater than the 
> distinct [apptdate] and an apptstatus of 0.
> 
> I have no idea how to create these nested queries and how to create a the 
> temporary value of distinct date to compare.  All help is appreciated.  thx. 
> Ian
> 
> 
0
Utf
1/21/2008 1:36:00 AM
Hi Ian,

I wonder if it would be to your advantange
to use a passthrough and use SQL Server
syntax....

Nevertheless, try

SELECT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate <  D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

good luck,

gary


"Ian" wrote:
> update -- the following sql gives me the distinct date and has the 
> subquery I
> think i need but all the counts are 0.  How do I carry the 1st column 
> result
> (distinct date) into the WHERE clause of the subquery?
>
> SELECT DISTINCT ApptDate AS DISTDATE,
> (SELECT Count(ApptID) FROM dbo_appt WHERE apptdate  Between distdate-30 
> And
> distdate) AS Pre
> FROM dbo_Appt;
>
>
> "Ian" wrote:
>
>> i'm trying to use a nested query (previous post under counting problem 
>> but no
>> solution found).  Here's what I've done so far:
>>
>> SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
>> Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, dbo_Appt.ApptTypeID,
>> dbo_Appt.OfficeID
>> FROM dbo_Appt
>> GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;
>>
>> With this I'm getting a count of apptID for each distinct date.  What I 
>> want
>> though is a count with different where clauses for each distinct apptdate
>>
>> For grouping [Pre] a count of apptID with an apptdate between 0-30days 
>> prior
>> to the distinct [apptdate] and an apptstatus of 3 or 4
>>
>> For grouping [Post] a count of apptID with an apptdate greater than the
>> distinct [apptdate] and an apptstatus of 0.
>>
>> I have no idea how to create these nested queries and how to create a the
>> temporary value of distinct date to compare.  All help is appreciated. 
>> thx.
>> Ian
>>
>> 


0
Gary
1/21/2008 11:38:05 AM
sorry..either have to wrap subquery in irrelevant
aggregate (say MAX) if use GROUP BY

SELECT
D.ApptDate AS DISTDATE,
MAX(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate <  D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

or, just use your DISTINCT

SELECT DISTINCT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate <  D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D;


"Gary Walter" wrote erroneously
>
> I wonder if it would be to your advantange
> to use a passthrough and use SQL Server
> syntax....
>
> Nevertheless, try
>
> SELECT
> D.ApptDate AS DISTDATE,
> (SELECT
> Count(Q.ApptID)
> FROM
> dbo_appt AS Q
> WHERE
> Q.apptdate>= D.ApptDate - 30
> AND
> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
> FROM dbo_Appt AS D
> GROUP BY
> D.ApptDate;
>
> good luck,
>
> gary
>
>
> "Ian" wrote:
>> update -- the following sql gives me the distinct date and has the 
>> subquery I
>> think i need but all the counts are 0.  How do I carry the 1st column 
>> result
>> (distinct date) into the WHERE clause of the subquery?
>>
>> SELECT DISTINCT ApptDate AS DISTDATE,
>> (SELECT Count(ApptID) FROM dbo_appt WHERE apptdate  Between distdate-30 
>> And
>> distdate) AS Pre
>> FROM dbo_Appt;
>>
>>
>> "Ian" wrote:
>>
>>> i'm trying to use a nested query (previous post under counting problem 
>>> but no
>>> solution found).  Here's what I've done so far:
>>>
>>> SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
>>> Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, 
>>> dbo_Appt.ApptTypeID,
>>> dbo_Appt.OfficeID
>>> FROM dbo_Appt
>>> GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;
>>>
>>> With this I'm getting a count of apptID for each distinct date.  What I 
>>> want
>>> though is a count with different where clauses for each distinct 
>>> apptdate
>>>
>>> For grouping [Pre] a count of apptID with an apptdate between 0-30days 
>>> prior
>>> to the distinct [apptdate] and an apptstatus of 3 or 4
>>>
>>> For grouping [Post] a count of apptID with an apptdate greater than the
>>> distinct [apptdate] and an apptstatus of 0.
>>>
>>> I have no idea how to create these nested queries and how to create a 
>>> the
>>> temporary value of distinct date to compare.  All help is appreciated. 
>>> thx.
>>> Ian
>>>
>>>
>
> 


0
Gary
1/21/2008 11:42:43 AM
I put the following statment in and set it to work and left it for 1.5hours 
and it said it was about 1/3 through.  Any ideas what I've so wrong to bog it 
down?

SELECT DISTINCT D.ApptDate AS DISTDATE,
(SELECT Count(Q.ApptID) 
FROM dbo_appt as Q
WHERE Q.apptdate>=D.ApptDate-30
And Q.apptDate < D.apptDate +1) AS Pre
FROM dbo_Appt As D;

"Gary Walter" wrote:

> sorry..either have to wrap subquery in irrelevant
> aggregate (say MAX) if use GROUP BY
> 
> SELECT
> D.ApptDate AS DISTDATE,
> MAX(SELECT
> Count(Q.ApptID)
> FROM
> dbo_appt AS Q
> WHERE
> Q.apptdate>= D.ApptDate - 30
> AND
> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
> FROM dbo_Appt AS D
> GROUP BY
> D.ApptDate;
> 
> or, just use your DISTINCT
> 
> SELECT DISTINCT
> D.ApptDate AS DISTDATE,
> (SELECT
> Count(Q.ApptID)
> FROM
> dbo_appt AS Q
> WHERE
> Q.apptdate>= D.ApptDate - 30
> AND
> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
> FROM dbo_Appt AS D;
> 
> 
> "Gary Walter" wrote erroneously
> >
> > I wonder if it would be to your advantange
> > to use a passthrough and use SQL Server
> > syntax....
> >
> > Nevertheless, try
> >
> > SELECT
> > D.ApptDate AS DISTDATE,
> > (SELECT
> > Count(Q.ApptID)
> > FROM
> > dbo_appt AS Q
> > WHERE
> > Q.apptdate>= D.ApptDate - 30
> > AND
> > Q.ApptDate <  D.ApptDate + 1 ) AS Pre
> > FROM dbo_Appt AS D
> > GROUP BY
> > D.ApptDate;
> >
> > good luck,
> >
> > gary
> >
> >
> > "Ian" wrote:
> >> update -- the following sql gives me the distinct date and has the 
> >> subquery I
> >> think i need but all the counts are 0.  How do I carry the 1st column 
> >> result
> >> (distinct date) into the WHERE clause of the subquery?
> >>
> >> SELECT DISTINCT ApptDate AS DISTDATE,
> >> (SELECT Count(ApptID) FROM dbo_appt WHERE apptdate  Between distdate-30 
> >> And
> >> distdate) AS Pre
> >> FROM dbo_Appt;
> >>
> >>
> >> "Ian" wrote:
> >>
> >>> i'm trying to use a nested query (previous post under counting problem 
> >>> but no
> >>> solution found).  Here's what I've done so far:
> >>>
> >>> SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
> >>> Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, 
> >>> dbo_Appt.ApptTypeID,
> >>> dbo_Appt.OfficeID
> >>> FROM dbo_Appt
> >>> GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;
> >>>
> >>> With this I'm getting a count of apptID for each distinct date.  What I 
> >>> want
> >>> though is a count with different where clauses for each distinct 
> >>> apptdate
> >>>
> >>> For grouping [Pre] a count of apptID with an apptdate between 0-30days 
> >>> prior
> >>> to the distinct [apptdate] and an apptstatus of 3 or 4
> >>>
> >>> For grouping [Post] a count of apptID with an apptdate greater than the
> >>> distinct [apptdate] and an apptstatus of 0.
> >>>
> >>> I have no idea how to create these nested queries and how to create a 
> >>> the
> >>> temporary value of distinct date to compare.  All help is appreciated. 
> >>> thx.
> >>> Ian
> >>>
> >>>
> >
> > 
> 
> 
> 
0
Utf
1/21/2008 2:18:00 PM
Hi Ian,

Open up Query Designer (SQL SERVER)
and see what performance you get
(change "dbo_appt" to "dbo.Appt")

it may be you will get okay performance
as a pass-through...

it is really asking alot if your table is huge..

it's like opening a recordset to distinct dates,
then moving through each record getting the
date, rebuilding and running the subquery
based on that records date; made only worse
if no index on ApptDate which Access can use
and because of the Access-to-SQL interface...


"Ian" wrote:
>I put the following statment in and set it to work and left it for 1.5hours
> and it said it was about 1/3 through.  Any ideas what I've so wrong to bog 
> it
> down?
>
> SELECT DISTINCT D.ApptDate AS DISTDATE,
> (SELECT Count(Q.ApptID)
> FROM dbo_appt as Q
> WHERE Q.apptdate>=D.ApptDate-30
> And Q.apptDate < D.apptDate +1) AS Pre
> FROM dbo_Appt As D;
>
> "Gary Walter" wrote:
>
>> sorry..either have to wrap subquery in irrelevant
>> aggregate (say MAX) if use GROUP BY
>>
>> SELECT
>> D.ApptDate AS DISTDATE,
>> MAX(SELECT
>> Count(Q.ApptID)
>> FROM
>> dbo_appt AS Q
>> WHERE
>> Q.apptdate>= D.ApptDate - 30
>> AND
>> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
>> FROM dbo_Appt AS D
>> GROUP BY
>> D.ApptDate;
>>
>> or, just use your DISTINCT
>>
>> SELECT DISTINCT
>> D.ApptDate AS DISTDATE,
>> (SELECT
>> Count(Q.ApptID)
>> FROM
>> dbo_appt AS Q
>> WHERE
>> Q.apptdate>= D.ApptDate - 30
>> AND
>> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
>> FROM dbo_Appt AS D;
>>
>>
>> "Gary Walter" wrote erroneously
>> >
>> > I wonder if it would be to your advantange
>> > to use a passthrough and use SQL Server
>> > syntax....
>> >
>> > Nevertheless, try
>> >
>> > SELECT
>> > D.ApptDate AS DISTDATE,
>> > (SELECT
>> > Count(Q.ApptID)
>> > FROM
>> > dbo_appt AS Q
>> > WHERE
>> > Q.apptdate>= D.ApptDate - 30
>> > AND
>> > Q.ApptDate <  D.ApptDate + 1 ) AS Pre
>> > FROM dbo_Appt AS D
>> > GROUP BY
>> > D.ApptDate;
>> >
>> > good luck,
>> >
>> > gary
>> >
>> >
>> > "Ian" wrote:
>> >> update -- the following sql gives me the distinct date and has the
>> >> subquery I
>> >> think i need but all the counts are 0.  How do I carry the 1st column
>> >> result
>> >> (distinct date) into the WHERE clause of the subquery?
>> >>
>> >> SELECT DISTINCT ApptDate AS DISTDATE,
>> >> (SELECT Count(ApptID) FROM dbo_appt WHERE apptdate  Between 
>> >> distdate-30
>> >> And
>> >> distdate) AS Pre
>> >> FROM dbo_Appt;
>> >>
>> >>
>> >> "Ian" wrote:
>> >>
>> >>> i'm trying to use a nested query (previous post under counting 
>> >>> problem
>> >>> but no
>> >>> solution found).  Here's what I've done so far:
>> >>>
>> >>> SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS 
>> >>> Pre,
>> >>> Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus,
>> >>> dbo_Appt.ApptTypeID,
>> >>> dbo_Appt.OfficeID
>> >>> FROM dbo_Appt
>> >>> GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;
>> >>>
>> >>> With this I'm getting a count of apptID for each distinct date.  What 
>> >>> I
>> >>> want
>> >>> though is a count with different where clauses for each distinct
>> >>> apptdate
>> >>>
>> >>> For grouping [Pre] a count of apptID with an apptdate between 
>> >>> 0-30days
>> >>> prior
>> >>> to the distinct [apptdate] and an apptstatus of 3 or 4
>> >>>
>> >>> For grouping [Post] a count of apptID with an apptdate greater than 
>> >>> the
>> >>> distinct [apptdate] and an apptstatus of 0.
>> >>>
>> >>> I have no idea how to create these nested queries and how to create a
>> >>> the
>> >>> temporary value of distinct date to compare.  All help is 
>> >>> appreciated.
>> >>> thx.
>> >>> Ian
>> >>>
>> >>>
>> >
>> >
>>
>>
>> 


0
Gary
1/21/2008 3:00:58 PM
Hi Ian,

You may have maybe moved on, but...

All my programs connect to SQL Server
via mdb's. If that is your case also, maybe
you would be interested in how I think I
would probably attack this problem (untested).

I would bring the ApptDate and Count for each
date into a "scratch" table (say "tblDateCounts")
into Access. This should be a fairly quick exercise.

Start with a make-table query that just counts ApptID's
for each ApptDate.

SELECT
D.ApptDate As DistDate,
Count(D.ApptID) AS DayCnt
INTO
tblDateCounts
FROM
dbo_Appt AS D
GROUP BY
D.ApptDate
ORDER BY
D.ApptDate;

the above should happen nearly instantly
if ApptDate is indexed...

if so, change it to an append query and
save it (say as "qryapptblDateCounts")

Next, open tblDateCounts in Design Mode
and add a Long field (say "Prev30DaysCnt"),
default 0, plus make DistDate the primary key.

Now (with all data in Access) an update query
to sum previous 30 days of DayCnt's should not
be such a "time-downer," probably even if you
simply resort to the slow domain function DSUM().
It just isn't that many records...say you had 10 years
of data for every date --> 10*365 records (ignoring
leap years).

UPDATE tblDateCounts AS T
SET T.Prev30DaysCnt =
DSUM("DayCnt", "tblDateCounts",
"[DistDate]>=#" & T.DistDate - 30 & "#
AND
[DistDate]<#" & T.DistDate + 1 & "#");

So...

you start a process where you need these values..

first, clear tblDateCounts
          DELETE * FROM tblDateCounts

then run saved append query
(or, to make even quicker, determine a range
of dates you really only need and use these in
WHERE clause of your append query)

then run the update query.

Now all calculations are done...
every place you need them
you "pluck them from the tree"
instead of "planting a tree, waiting
for it to grow, and then bear fruit."

Please respond back if I have not
been clear about something.

good luck,

gary


"Ian" wrote:
>I put the following statment in and set it to work and left it for 1.5hours
> and it said it was about 1/3 through.  Any ideas what I've so wrong to bog 
> it
> down?
>
> SELECT DISTINCT D.ApptDate AS DISTDATE,
> (SELECT Count(Q.ApptID)
> FROM dbo_appt as Q
> WHERE Q.apptdate>=D.ApptDate-30
> And Q.apptDate < D.apptDate +1) AS Pre
> FROM dbo_Appt As D;
>
> "Gary Walter" wrote:
>
>> sorry..either have to wrap subquery in irrelevant
>> aggregate (say MAX) if use GROUP BY
>>
>> SELECT
>> D.ApptDate AS DISTDATE,
>> MAX(SELECT
>> Count(Q.ApptID)
>> FROM
>> dbo_appt AS Q
>> WHERE
>> Q.apptdate>= D.ApptDate - 30
>> AND
>> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
>> FROM dbo_Appt AS D
>> GROUP BY
>> D.ApptDate;
>>
>> or, just use your DISTINCT
>>
>> SELECT DISTINCT
>> D.ApptDate AS DISTDATE,
>> (SELECT
>> Count(Q.ApptID)
>> FROM
>> dbo_appt AS Q
>> WHERE
>> Q.apptdate>= D.ApptDate - 30
>> AND
>> Q.ApptDate <  D.ApptDate + 1 ) AS Pre
>> FROM dbo_Appt AS D;
>>
>>
>> "Gary Walter" wrote erroneously
>> >
>> > I wonder if it would be to your advantange
>> > to use a passthrough and use SQL Server
>> > syntax....
>> >
>> > Nevertheless, try
>> >
>> > SELECT
>> > D.ApptDate AS DISTDATE,
>> > (SELECT
>> > Count(Q.ApptID)
>> > FROM
>> > dbo_appt AS Q
>> > WHERE
>> > Q.apptdate>= D.ApptDate - 30
>> > AND
>> > Q.ApptDate <  D.ApptDate + 1 ) AS Pre
>> > FROM dbo_Appt AS D
>> > GROUP BY
>> > D.ApptDate;
>> >
>> > good luck,
>> >
>> > gary
>> >
>> >
>> > "Ian" wrote:
>> >> update -- the following sql gives me the distinct date and has the
>> >> subquery I
>> >> think i need but all the counts are 0.  How do I carry the 1st column
>> >> result
>> >> (distinct date) into the WHERE clause of the subquery?
>> >>
>> >> SELECT DISTINCT ApptDate AS DISTDATE,
>> >> (SELECT Count(ApptID) FROM dbo_appt WHERE apptdate  Between 
>> >> distdate-30
>> >> And
>> >> distdate) AS Pre
>> >> FROM dbo_Appt;
>> >>
>> >>
>> >> "Ian" wrote:
>> >>
>> >>> i'm trying to use a nested query (previous post under counting 
>> >>> problem
>> >>> but no
>> >>> solution found).  Here's what I've done so far:
>> >>>
>> >>> SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS 
>> >>> Pre,
>> >>> Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus,
>> >>> dbo_Appt.ApptTypeID,
>> >>> dbo_Appt.OfficeID
>> >>> FROM dbo_Appt
>> >>> GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;
>> >>>
>> >>> With this I'm getting a count of apptID for each distinct date.  What 
>> >>> I
>> >>> want
>> >>> though is a count with different where clauses for each distinct
>> >>> apptdate
>> >>>
>> >>> For grouping [Pre] a count of apptID with an apptdate between 
>> >>> 0-30days
>> >>> prior
>> >>> to the distinct [apptdate] and an apptstatus of 3 or 4
>> >>>
>> >>> For grouping [Post] a count of apptID with an apptdate greater than 
>> >>> the
>> >>> distinct [apptdate] and an apptstatus of 0.
>> >>>
>> >>> I have no idea how to create these nested queries and how to create a
>> >>> the
>> >>> temporary value of distinct date to compare.  All help is 
>> >>> appreciated.
>> >>> thx.
>> >>> Ian
>> >>>
>> >>>
>> >
>> >
>>
>>
>> 


0
Gary
1/22/2008 1:39:26 PM
Reply:

Similar Artilces:

IRERR nested formula help please
hi just wondering if someone can help with the following nested ISERR formula. I want to be able to show in J3 that if the value in G3 is "0" then show as "-100%" OR if the value is "NULL" then show as "0" but if the value is >0 then I3/G3*100 it is working to a degree but if value in G3 is NULL then still shows as -100% I want it to show as 0% if NULL and -100% if 0 - hope it makes sense.... currently have following in J3 =IF(ISERR(I3/G3),-100,I3/G3*100) G3 = Order I3 = Profit thanks in advance Depending on how I interpret NULL =IF(ISBLANK(G3)...

Arabic ODBC query
I am trying to read and write arabic data into Access2000 using MFC ODBC (CRecordset) , but I can only see '?' marks. after doing a research on the news groups , I understand that a tweak needs to be made on RFX_Text can anyone guide me what needs to be done. Appreciate a response. thnx P.Ekkoratha ...

how to set up a query to show only user based selections
I have created a table with data types yes/no. I am trying to create a query that ONLY shows when the user has selected yes. I am trying to create a report that only shows the options selected versus showing all of the options. The users select this from a form that has option buttons. Any help will be greatlt appreciated! Submitted via EggHeadCafe - Software Developer Portal of Choice WMI - VB 6.0 Windows Management Instrumentation http://www.eggheadcafe.com/tutorials/aspnet/5fb2b0ca-919a-406b-8566-a9d07f76c868/wmi--vb-60-windows-mana.aspx Yes is stored as a -1 (minus one) so ...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

Multiple Item query
I've read a few posts on this query and it looks like creating a table where I input the item numbers in it and linking it to the query seems to be a good way to get a lot of items in a query. Now what If I have hundreds of numbers I want to look up? do I input all 100 numbers into the table? Or what if it's like 10 numbers but I want create seperate queries for different groups of numbers, should I have 1 table for one group of numbers? What would be the easiest way for a novice to do this? It is easier to fill the table with just the values you need for the query you want to run, t...

display ratios as written in web query
I am using a web query to display data about screen contrasts, all written with the formatting of "700:1" or "3000:1" It displays corectly in the edit web query window, but everything I try to make it display as delivered fails. I tried text, custom formats with @ etc, but it seems to insist on doing the calculation before turning it back to text, or as a ratio it does the division. (The text formatting for this works OK if I type directly into the spread sheet) Is there a way to make a web query display as written? Any help appreciated. Keith ...

Append Query Not Working 05-03-10
My Append query is not working and i am not sure why... Here is my SQL INSERT INTO tblContractPOTracking ( TrackingID ) SELECT tblDocTracking.TrackingID FROM tblDocTracking WHERE (((tblDocTracking.DocumentNumber)="5" Or (tblDocTracking.DocumentNumber)="6" Or (tblDocTracking.DocumentNumber)="7")); Please help! "not working" is a bit vague ... If you want more specific suggestions, please provide more specific description... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and s...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

Query Date Help
I need to create a query that will do the following: these are my fields: resign date, hire date I need to subtract the resign date from hire date but I want it to return the answer as years, months & days ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days is there a way to use the DateDiff function to return years, monts & days? Look at this link on "A More Complete DateDiff Function" http://www.accessmvp.com/djsteele/Diff2Dates.html -- Good Luck BS"D "aldunford" wrote: > I need to create a query that will do the following: > > th...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

question on tables and queries
I've imported 2 tables from excel and ran a query against those two tables to determined the difference. This is something I would like to do each month. I would like to keep the same settings in my database, is there a way I can easily import/update the data in the new table without creating a new one each month? The query would stay the same as I'm comparing the same two files, but the the data will be changing every month. I don't know much about access and would like to know if there is an easier way of doing this besides creating a new table and query each month? I&...

Query Needed 01-04-10
A sample of my data table looks as so: Underlying ID StrikePrice AAPL APVA10C155.00 155 AAPL APVM10P155.00 155 AAPL APVA10C160.00 160 AAPL APVM10P160.00 160 AAPL APVA10C165.00 165 AAPL APVM10P165.00 165 AAPL APVD10C160.00 160 AAPL APVP10P160.00 160 AAPL APVD10C165.00 165 AAPL APVP10P165.00 165 AAPL APVD10C155.00 155 AAPL APVP10P155.00 155 AAPL APVB10C155.00 155 AAPL APVN10P155.00 155 AAPL APVB10C160.00 160 AAPL APVN10P160.00 160 AAPL APVB10C165.00 165 AAPL APVN10P165.00 165 GS GPYA10C155.00 155 GS GPYM10P155.00 155 GS GPYA10C160.00 160 GS GPYM10P160.00 160 ...

Linq Query and Lambda
Hello, I am getting a list of regions as follows: return _context.Regions.Select(r => new RegionModel { Id = r.Id, Name = r.Name }).OrderBy(r => r.Name).AsQueryable(); However I need to get only the Regions which are related with Centers. So I need to check _context.Centers and get all used Regions Ids from each Center.Region.Id and then get all the Regions with those Ids ... Is this possible to do with a lambda expression ... I think it is possible but I am a little bit confused on this. Thank You, Miguel shapper wrote: > Hello...

3 queries into 1
Hello, I am going to try this again starting fresh from my other posts. I am trying to develop an all inclusive query on our inventory. Each fical term we start with new tables. I would like to take our Ending Inventory from our prior fiscal term and combine it with our current inventory balances. In years past I always took our ending inventory and posted them in our purchase order table to have our beginning balances. I thought it would be less time consuming to have a query to combine the 2. Below is a list of data and the query designs. PART # PRODUCT DESCRIPTION END B...

Query in Excell
I would like to create bolean statements in one or more of the columns that result from a query. Basically, I want to test a column for a factor and return a simple 'true' or 'false'. Using this I, hopefully, will eliminate the need to pass the data to Excel and perform the function there. ...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

querying similar databases plan
I'm working with hideously unnormalized databases, most of which have similar structures. (Of course, if they had the same structures, it would be too easy.) I work at a company that does cancer research, so they collect information about their patients over a series of visits. The questions they ask (queries) for parts of the final report are standard across all databases, and then some are specific to a the type of study they're doing. What is the best way to approach this, given that I have to summarize like 20 databases in maybe two weeks, and the column names are not necessari...

Double Results in Query
New to Access I am trying to create a query that returns data from 2 tables. one table can have multiple entries on one day per person and the other table will only have one entry per person. 2 tables are DataEntry - will have multiple entries per day per person CallLog - one entry per person per day SELECT DataEntry.OrderTakenBy, DataEntry.Company, DataEntry.Date, DataEntry. Category, DataEntry.ShortCode, DataEntry.ContactName, DataEntry.PositionTitle, DataEntry.PostDateExt, DataEntry.FirmPreview, DataEntry.AllorProgramCodes, DataEntry.Units, DataEntry.value, DataEntry.Mark...

Querying results for two recent dates
I have a table "DETAILS" in which fields are Name Id TestDate Grade (all fields can be duplicated) Now I want a query which should return me 'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade in Latest Test' It should be noted that every person appears the test many times and i just want the recent two results. The query should return names of only those people who have appeared in either or both of the last two tests I was trying to do this by running a query on a query but the results were ...

SQL query for "capping threshold"
Suppose I want to add all the numbers in one column, with thefollowing caveat:All values should be capped at some high threshold level.As an example, suppose the threshold is 10000 and the numbers in thecolumn are 7500, 8500, 9500, 10500, 11500 and 12500. The query shouldreturn 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).Any help will be appreciated.Thanks,Bhat SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValueFROM tblCapped;Insert the right field and table names. Also I think that your calculation below has one too many zeros in it.-- Jerry Whittle, Microsof...

Update Query question 06-26-07
Hello, I am trying to write what I assume would be an update query. I have 2 fields, acct_num and brnch_num in my table. I need to combine these 2 fields into a new field called cust_num. So, if: brnch_num acct_num 123 45678 123 12 12 1 Then I would need the cust_num to read: 12345678, 12300012, 01200001 I'm not sure how to get the zeroes into the cust number so that that number is always 8 diguts. Any help would be greatly appreciated! I wouldn't use an update query for this. Keep the data separat...

Query Not Returning Correct Amount of Records
I have 6 tables that I have built 6 different queries on. Individually, these queries return the correct amount of records in the tables. Which is 80 records in all tables involved. Then I built a query that collects data from the 6 queries and this query returns 75 records. What am I doing wrong? Thanks Don As a guess, your query is not correctly structured for the data you have. For instance, if one of the 6 tables does not have a matching record that could cause you to "lose" a record or two in the query if you have set up a join to that table and have n...

Pivot Table view of Union Query
Dear All, I can see what I need at the Pivot Table view of a Union Query. However, this cannot be correctly exported to Excel. Although there is a function of "Export to Microsoft Excel", however, all "detail data" in Access was gone - leaving only the count of the data in Excel. How can I show all these detail data in the data area of the Pivot Table? Alternatively, is it possible to copy the Pivot Table in Access and then paste in Excel? I cannot see any Select Rows commands in the pull down menu. Thank you very much. Hong -- Message posted via AccessMonster.com h...

Query based Subform will not allow editing
I have a maintenance DB that has energy lockout points associated with pieces of equipment so we can safely do maintenance. I have an 'edit existing lockout' form, based on a query, that lists in a subform the various points required for a certain piece of equipment. My system used to work, but now I can edit the main, but the subform has locked me out. All I get is a doorbell tone when I attempt to enter info. I can enter the req'd info in the tables, but but my form went snafu. Any ideas? Thanks in advance Does your query allow editing? Check the asterisk in the record...

Acces import data to Query
I am having a little problem understanding a concept. Obviously data needs to be inputted or imported into tables for the database to contain relevant records. What process am I looking at if I want to import data to create a query->calculation->report style process. What do I mean, say I had a database that had basketball team records and I had imported the results of the teams and the individual players latest games. When Team A versus Team B the coaches announce their teams to play. If there was only 1 game I could create a form for user to submit the teams but when mul...