Using DSUM With A Query Name As Your Domain, And Having That Query Nested

Jul 6, 2005

The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.

For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.

I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.

HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.

I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")

I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.

PLEASE HELP! :(

View Replies


ADVERTISEMENT

Domain Aggregate Functions In Query Expressions

Apr 3, 2007

Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:

tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary

tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want

So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.

View 2 Replies View Related

Forms :: Getting Value From A Query For Textbox - Domain Function?

Sep 9, 2014

I intended to get a Value from a Query for a textbox, so I tried to use the Domainfunction DFirst.

Code:
=DFirst("[SAPANLAGE]";"[Query_Anlage_42]")

but now the box just shows #error and blinks like crazy.

View 13 Replies View Related

Nested Query Help Needed

Apr 26, 2006

Hello everyone,

I’m looking for some help with a “nested query”

I have four fields that typically have a quantity amount in them.

I also have a field with a date.

I would like to say something like this

If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank


Thanks a lot

Corey

View 2 Replies View Related

Nested Iif Statement! Not Sure Why This Query Will Not Run...help!

Aug 12, 2007

I'm wanting to run an update query but first I want to see the results by using a select query to make sure that I'm getting the correct results.

See the following query statement:

SELECT ([Goodrec - All] INNER JOIN WH_ACCTCOMMON ON [Goodrec - All].ACCTNO = WH_ACCTCOMMON.ACCTNBR) INNER JOIN [Trial-GL-Reference] ON WH_ACCTCOMMON.CURRMIACCTTYPCD = [Trial-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode], [Goodrec - All].CURRBAL = IIf(Not IsNull([chgoffamt]) And Not IsNull([partsold]),CCur([grossbal])-CCur([chgoffamt])-CCur([partsold]),IIf(Not IsNull([CHGOFFAMT]),CCur([grossbal])-CCur([CHGOFFAMT]),IIf(Not IsNull([partsold]),CCur([grossbal])-CCur([partsold]),[grossbal]))), [Goodrec - All].PCTOWNED = IIf(Not IsNull([origamt]) And Not IsNull([partsold]), CCur([origamt])-CCur([partsold]/CCur([origamt]), IIf(IsNull([partsold]), [grossbal]));


This is the error that I receive each time I attempt to run it and I'm not sure what I'm missing.

Error message:

MICROSOFT OFFICE ACCESS
Syntax error(missing operator) in query expression '([Goorec-ALL] Inner Join WH_ACCTCOMMON ON [Goodrec-All].ACCTNO=WH_ACCTCOMMON.ACCTNBR) inner join [TRIAL-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode]'


Any help with this issue would be greatly appreciated.

Thanks.

Govmate!

View 3 Replies View Related

Nested Query Confusion

Sep 23, 2007

Dear Access Expert

I have been doing some experimentation with Queries and I have discovered something unusual.

If I have one query and I use the totals feature (only using group by) I get all the records based on the inputed criteria

If I then use that same query as an underlying recordset for a second query and I use the totals feature (only using group by) in the second query... I only get the first record of each type. See query results below..

I am trying to achieve the results of the second query in the first query (DON'T WANT NESTED Queries) and I don't really understand why the results of the queries are different when everything else is the same other than one being nested.


Example below

First Query Results (using totals with group by)

1 a
1 a
1 a
2 a
2 a
2 a
3 a
3 a
3 a
4 a
4 a
5 a

Second Query Results with the First query as its recordset (using Group By)

1 a
2 a
3 a
4 a
5 a

View 3 Replies View Related

Re-write Nested Query With SQL

Feb 24, 2005

Hi folks,

I have a query which queries another query. I need to re-write the whole thing in SQL, but struggling with incorporating the nested query.

here's the main query:

SELECT tblProductSubCategory.SubCatDesc AS Products, qrySummaryStage3.[Early Cash Sales], qrySummaryStage3.[Late Cash Sales], qrySummaryStage3.[Cash Variance], qrySummaryStage3.[Cash Variance %], qrySummaryStage3.[Early Credit Sales], qrySummaryStage3.[Late Credit Sales], qrySummaryStage3.[Credit Variance], qrySummaryStage3.[Credit Variance %], qrySummaryStage3.[Early Total], qrySummaryStage3.[Late Total], qrySummaryStage3.[Variance Total], qrySummaryStage3.[Total Variance %], tblBudgetNew.Amount AS [Late Budget Amount], [Late Total]-[Late Budget Amount] AS [Budget Variance], qrySummaryStage3.[Early Cash Margin %], qrySummaryStage3.[Late Cash Margin %], qrySummaryStage3.[Early Credit Margin %], qrySummaryStage3.[Late Credit Margin %]
FROM (qrySummaryStage3 INNER JOIN tblBudgetNew ON qrySummaryStage3.SubCatID = tblBudgetNew.SubCatID) INNER JOIN tblProductSubCategory ON qrySummaryStage3.SubCatID = tblProductSubCategory.SubCatID
WHERE (((Month([date]))=1) AND ((Year([date]))=2005) AND ((tblBudgetNew.Site)=[Enter Site ID]) AND ((tblBudgetNew.Type)="Monthly Breakdown"));


and here's the nested query , called "qrySummaryStage3":

SELECT tblProductSubCategory.SubCatID, tblReportTemp1.[Early Cash Sales], tblReportTemp1.[Late Cash Sales], [Late Cash Sales]-[Early Cash Sales] AS [Cash Variance], IIf(nz([early cash sales],0)=0 Or [Early Cash Sales]=0,0,[Cash Variance]/[Early Cash Sales]) AS [Cash Variance %], tblReportTemp1.[Early Credit Sales], tblReportTemp1.[Late Credit Sales], [Late Credit Sales]-[Early Credit Sales] AS [Credit Variance], IIf(nz([Early Credit Sales],0)=0 Or [early Credit Sales]=0,0,[Credit Variance]/[Early Credit Sales]) AS [Credit Variance %], [Early Cash Sales]+[Early Credit Sales] AS [Early Total], [Late Cash Sales]+[Late Credit Sales] AS [Late Total], [Late Total]-[Early Total] AS [Variance Total], IIf([Early Total]=0,0,[Variance Total]/[Early Total]) AS [Total Variance %], IIf([Early Cash Sales]=0,0,([Early Cash Sales]-[Early Cash Cost])/[Early Cash Sales]) AS [Early Cash Margin %], IIf([Late Cash Sales]=0,0,([Late Cash Sales]-[Late Cash Cost])/[Late Cash Sales]) AS [Late Cash Margin %], IIf([Early Credit Sales]=0,0,([Early Credit Sales]-[Early Credit Cost])/[Early Credit Sales]) AS [Early Credit Margin %], IIf([Late Credit Sales]=0,0,([Late Credit Sales]-[Late Credit Cost])/[Late Credit Sales]) AS [Late Credit Margin %]
FROM tblReportTemp1 RIGHT JOIN tblProductSubCategory ON tblReportTemp1.ProductSubCatId = tblProductSubCategory.SubCatID;


Thanks in advance!

View 4 Replies View Related

Help With Dsum/Query In Form

Feb 21, 2008

Just a quick one,

I have a TextBox in my form, that currently has this DSum in it:
=DSum("QtyAdd","Orders","ProdID")-DSum("QtySub","Output","ProdID")

(ProdID is another TextBox in the form, QtyAdd is from the Orders table, and QtySub is from the Output table)

I'm also wanting to query it with more WHERE statements but finding I can only do this once with a DSum.

I'm wanting to effectively do this:
=DSum("QtyAdd","Orders","ProdID AND StockItem = Yes AND Arrival = Yes")-DSum("QtySub","Output","ProdID AND Dispatch = Yes")

So, WHERE the field StockItem and Arrival = Yes in the Orders table and WHERE Dispatch = Yes in the Output table.

Does anyone know how I can achieve this?

I was thinking an SQL query but don't know how to display a Query into a TextBox.

Thanks.

View 8 Replies View Related

Update Query And DSum

May 22, 2006

I understand that it is not good practice to store calculated values in a table, but I am having a difficult time doing otherwise. I have a form with the field "volume". I have a table that stores monthly volumes for each product type. In my AfterUpdate of my "effectivemonth" field I have a DSum query go to the Volumes table and sum the volumes for the particular product based on the effective month. Each month all of the volumes change and I have to go into every record via my form one at a time and trigger the AfterUpdate event in order to get the field updated with the new volumes.
These volumes 1) have to be visible on my form at all times and 2) are used as the basis for most of my queries, pulling from the "volume" field in my table.

I am looking for a way to update the entire database volumes with one command button. Is this possible? I've thrown in my current AfterUpdate code to give you an idea of what I am doing now.

If Not IsNull(Me.fcstloc) Then

Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")
Me.prodvolume = DLookup("[volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "")
If Me!otherloc = "Supplies forecast" Then
Me.prodvolume = DSum("[volume]", "qryMonthlySupplies", "[product] = '" & Forms!Projects!currentpn & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")

End If
End If

Any ideas would be helpful.

Thanks

View 1 Replies View Related

DSUM / Running Sum In Query

Sep 5, 2006

I have been trying to get this all night. This was my last attempt. I know it is all wrong and messed up but after 10 hrs I go for help. There is no date field to work with.

Expr2: DSum("[Amount]","[List]","[Item]=" & [Item] & "and [FinItem]>0")+0

But it ended up like this.

Item ------Location-------Amount--Expr2----Expr3
210090------030005 -------267470--error ----859103.74417
220170------ GYM -------409000--error ----859103.74417
220170------ 060010-------83297 ---error ----859103.74417
220170------ 050010-------17762 --error ----859103.74417
3429B -------060010-------0.74417--error ----859103.74417
3429B -------060010-------3 ----error ----859103.74417
7342900-----060010--------5369 ---error ----859103.74417
7342900-----060050--------76202 --error ----859103.74417


I need it to look like this.Where it will do a running sum based on the "Item" Groups.

Item---------Location------Amount----Expr2------Expr3
210090------ 030005-------267470---267470---859103.74417
220170------ GYM --------409000---409000---859103.74417
220170------060010-------83297----492297---859103.74417
220170------050010-------17762----510059---859103.74417
3429B-------060010------- 0.74417---0.74417---859103.74417
3429B-------060010------- 3-------3.74417---859103.74417
7342900-----060010-------5369----5369-----859103.74417
7342900-----060050-------76202---81571----859103.74417





Please Help.

View 3 Replies View Related

Dsum Function In A Query

Mar 19, 2007

hello
i have a query summing sales data by month.
what i need to do is be able to list in this query the sales summed for same period last year.
I have some other criteria but this is the just of it.
trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.

example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.

I can not get the dsum function to work???
any
ideas????

i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either.
If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.

View 1 Replies View Related

Queries :: Can't DSUM In Query With Yes / No

Nov 17, 2014

I'm attempting to do a DSUM in a query against a linked table that has a field name of "VOID". The field name shows up as a yes/no when looking at the structure and when looking at the data, it is either 0 or -1. I've tried just about everything I can think of but this is the structure I have:

Tons: DSum("Net","dbo_tblSALESTKT","JOB='" & Left([JobNumber],6) & "' AND Ticket_Date=#" & [forms]![frmForemanInfo]![CurrentDate] & "# AND [dbo_tblSALESTKT]![VOID]=0")

If I remove the VOID part, it will sum correctly but when adding in the VOID=0 or false or No or anything, the query doesn't show me a sum anymore. what I'm doing wrong.

View 2 Replies View Related

Inner Join Results From Nested Query.

Jul 29, 2007

Hi, I have a query that looks like:

SELECT * FROM ((TBL_Properties INNER JOIN TBL_Property_Landlords ON TBL_Properties.Property_ID=TBL_Property_Landlords. Property_ID) INNER JOIN TBL_Landlords ON TBL_Property_Landlords.Landlord_ID=TBL_Landlords.L andlord_ID) INNER JOIN TBL_Agents ON TBL_Properties.Agent_ID=TBL_Agents.Agent_ID;

so far. But I need to join the results from another query which will be calculating SUM(Pay). Is it possible to INNER JOIN to a: (SELECT ....) nested query?

Sorry if this is hard to understand.

Thanks.

View 3 Replies View Related

Update Query (with Nested Sql) Not Working

Sep 23, 2007

hi guys,
i have this sql that filters 2 tables to give me the highest number against a record and then i want to update the 1st table. but it dosnt seam to work it brings back the correct data but the sql has generated a non-updatable query.

any ideas?

UPDATE [SELECT ARCtblErrorInfo.Uber, Max(ARCtblHistory.Status) AS MaxOfStatus
FROM ARCtblErrorInfo LEFT JOIN ARCtblHistory ON ARCtblErrorInfo.Uber = ARCtblHistory.Uber
WHERE (((ARCtblErrorInfo.Status)=121))
GROUP BY ARCtblErrorInfo.Uber]. AS test INNER JOIN ARCtblErrorInfo ON test.Uber = ARCtblErrorInfo.Uber SET ARCtblErrorInfo.Status = [test].[MaxOfStatus];

View 1 Replies View Related

Queries :: How To Create Nested Query

Nov 14, 2013

I have done smoe baisc queries to sort, filter and report on data.How do I create a nested query where I don't repeat all the information across the query results line? Is there a way I can build a nested query to give all the SMCode codes associated with each appointment in one column separated by a coma?I have a table with appointments (APPTS):

RecID
ADate
ATime
Durantion
PVID
Desc
Case#

Then I have a table with Orders related to these appointments (ORDERS):

RecID
Case#
SMCode
SMDate

Here's my query:

SELECT DISTINCTROW Appts.RecID, Appts.ADate, Appts.ATime, Appts.Durantion, Appts.PVID, Appts.Desc, Orders.SMCode
FROM Appts INNER JOIN Orders ON Appts.[Case#] = Orders.[Case#];

My results look like this:

RecID ADate ATime Duration PVID Desc SMCode
RecID ADate ATime Duration PVID Desc SMCode
RecID ADate ATime Duration PVID Desc SMCode

I need my results to look like this:
RecID ADate ATime Duration PVID Desc SMCode,SMCode,SMCode...

View 3 Replies View Related

Queries :: Getting Parenthesis Error In Nested IIF Query

Apr 15, 2015

I have different product groups and they can have one of 3 rental rates(5,4,3%) I have written a nested query but I keep getting parenthesis errors, the last one I got told me I must enclose iif functions in parentheses. I know its probably a small thing but I just cant see it. By the way the server is German so ; need to be used instead of ,.

IIf([view_India]![Sub_Group] In (1730;1810;2870);5;iif Left([view_India]![Sub_Group];2) In (01;02;21;24;29)
;5;iif([view_India]![Sub_Group] In (3310;3330;3340;3360);3;4))))

View 2 Replies View Related

Modules & VBA :: How To Implement Variable In Nested Query

Jun 1, 2014

I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.

SELECT "rubriek, SUM(verkoopprijs) as prijzen
FROM voorwerp
(inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp)
inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau
where rubriek in (select rubrieknummer
from rubriek
where rubriek = variable name
)
group by rubriek"

Do I have to treat it as a normal select query or is there something speical that I have to do ?

View 5 Replies View Related

Queries :: Make A Nested If Then Statement In A Query Field

Apr 27, 2014

I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:

Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))

I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.

View 6 Replies View Related

Queries :: Nested IIF - Returning Calculated Results In A Query

Oct 30, 2013

What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.

I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.

My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]

I want to create a calculated field for [Projected Action 2] that says:

If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays

[Code] ....

I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.

View 7 Replies View Related

General :: Add Query Controlled From Combo Box On Nested Form

Sep 5, 2013

I am having an issue with a database application I created. I needed to add another query that could be controlled from combo box on a nested form.

The combo box is for dates for entries in a table. Which then filters the query based on the combo box selection. Easy enough stuff.

The problem I am having is that when I try to use the combo box as a criteria for the query using;

[Forms]![MasterForm]![NavigationSubform].[Form].[comboBox]

Access closes and wont let me use the reference to the combo box.

What has happened? Corrupt database? How do I fix it?

View 9 Replies View Related

Queries :: Complicated Query (nested Subqueries) For A Trend Graph

Apr 17, 2014

Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)

There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)

ResolveDate (the date said investigation was brought to a conclusion)

There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)

So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)

I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)

Updated (i.e. issues not yet resolved but updated each day)

Unchanged (i.e. issues not yet resolved and not updated each day)

Outstanding (i.e. all unresolved issues as of each day)

This is the SQL I've put together to get that table of information on which to base my chart :

Code:
SELECT [tblBalances].[BalanceDate] AS AsOfDate,
(SELECT COUNT([tblIssues].[IssueID])
FROM [tblIssues]
WHERE [tblIssues].[Flag] = True
AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,

[Code] .....

The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.

The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.

I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)

I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)

I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :

Code:
(SELECT TOP 1 [tblComments].[UpdatedWhen]
FROM [tblComments]
WHERE [tblComments].[IssueID] = [tblIssues].[IssueID]
AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate]
ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen

But how to do this, nor if it is even feasible in Access to begin with.

View 2 Replies View Related

Reports :: Record Source From Report Based On Nested Query?

Jul 24, 2015

I have a report that is based on nested (I think thats the phrase) query's.

Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)

This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.

However I then wanted to just pick one with exact matching ID's I select on a form.

I could not see an easy way to apply this without making another set of nested querys which seems a little excessive

Anyway, an easy way for the printed report to do this is a simple filter added after, works great.

I can't see a way to do the same for an update query.

I was wondering if I could get the record source of this report and add to my table. I have tried with

' Dim db As DAO.Database
' Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

And dozens of variations over some hours but a variety of errors mainly "too few parameters."

View 3 Replies View Related

Modules & VBA :: Using DSUM To Calculate Sum From Either A Table Or Query To Display On Form

Aug 24, 2013

I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:

Code:
Me.txtTotalEarnings = DSum("Price", "KatiesPeriodTakings", "AppDate" >= CDate([Forms]! _&
[frmKatiesTakings]![txtStartDate]) And "AppDate" _&
<= CDate([Forms]![frmKatiesTakings]![txtEndDate]))

But I keep getting a mismatch error

The Query is called KatiesPeriodTakings
The field I want totalled is called Price
The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form

View 5 Replies View Related

Queries :: Nested Query - Left Outer Join Not Returning All Records

Oct 1, 2014

I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..

Code:

Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)

View 1 Replies View Related

How To Sum Domain Aggregate Controls?

Oct 14, 2005

I have a subform (in continuous record view) which displays products on an order items form. Each record has a DMin control to find the lowest price for its product (each product can have many suppliers and therefore many prices). I need to put a control in the subform footer containing the sum of the minimum prices.

Access doesn't seem to want to sum calculated controls, so could someone please point me in the direction of how to go about doing this or achieving the same result through a different method?

View 10 Replies View Related

After 2003 Domain, Only One User At A Time

Aug 8, 2005

We have a database created in access 2000 laying on a server that two clients connect to using access 2000 from WinXP Pro. We have just moved the database from our old win2000 server to a 2003 server. The clients now logon to a 2003 domain instead of the old 2000.

Since we moved to 2003 only one user can open the database at a time, when the second user tries to open nothing happens. There's no error message, there's just nothing happening! When the first user close access the other user can open the db.

What's going on????

View 6 Replies View Related







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