Problems Changing Param Query to reference a Form

[Christineeve]

Hello,
(I have inherited this database. Things are not done properly. I'm tasked with "makeing it work." I redesign whenever there is a chance to fix it.)

I have a query that I'm trying to remove the parameter prompt asking for a date (six times). The designer of this database is, his wisdom, created this query based on at least six other queries. The date prompt occurs at nearly every query.

I need to change this parameter prompt to reference a start and end date on a form. Pulling the date from the form is sort of working*. But the dollar amount being returned is wrong. As follows:

Original Query

SELECT
[COLOR=blue][Fo Date] AS PurchaseDate, *The Parameter to remove. [/color blue]
FoRecords.StoreName, 
FoRecords.Plan,
FoRecords.[A/C#], 
BarLimits.SeqNum, 
BarLimits.Limit,
BarLimits.[Exp Date], 
BarLimits.[W/T], 
BarLimits.[Index #], 
FoRecords.Center, 
FoRecords.Acct, 
Sum(FoRecords.Amount) AS SumOfAmount, 
FoRecords.RateType, FoRecords.[Purch&Sold]

FROM 
BarLimits INNER JOIN FoRecords ON (BarLimits.StoreName = FoRecords.StoreName) AND 

(BarLimits.[Purch&Sold] = FoRecords.[Purch&Sold])

GROUP BY
[Fo Date], FoRecords.StoreName, 
FoRecords.Plan, 
FoRecords.[A/C#], 
BarLimits.SeqNum, 
BarLimits.Limit, 
BarLimits.[Exp Date], 
BarLimits.[W/T], 
BarLimits.[Index #], 
FoRecords.Center, 
FoRecords.Acct, 
FoRecords.RateType, 
FoRecords.[Purch&Sold];

**Fields removed to make it shorter.
Desired Result for SumOfAmount:
TradeDate StoreName SumOfAmount
09-Sep-08 Store #9848 66,005,000.00

New Query:

SELECT
FoRecords.PurchaseDate,
FoRecords.StoreName, 
FoRecords.Plan,
FoRecords.[A/C#],
BarLimits.SeqNum,
BarLimits.Limit,
BarLimits.[Exp Date],
BarLimits.[W/T],
BarLimits.[Index #],
FoRecords.Center, 
FoRecords.Acct, 
Sum(FoRecords.Amount) AS SumOfAmount,
FoRecords.RateType,
FoRecords.[Purch&Sold]

FROM BarLimits INNER JOIN FoRecords ON (BarLimits.StoreName = FoRecords.StoreName) AND 

(BarLimits.[Purch&Sold] = FoRecords.[Purch&Sold])

GROUP BY
FoRecords.PurchaseDate, 
FoRecords.StoreName, 
FoRecords.Plan, 
FoRecords.[A/C#], 
BarLimits.SeqNum, 
BarLimits.Limit, 
BarLimits.[Exp Date], 
BarLimits.[W/T], 
BarLimits.[Index #], 
FoRecords.Center, 
FoRecords.Acct, 
FoRecords.RateType, 
FoRecords.[Purch&Sold]

[COLOR=blue] 
HAVING (((FoRecords.PurchaseDate)=#9/9/2008#));
[/color blue]

Result (Incorrect SumofAmount)
**Several fields removed to keep it shorter.
TradeDate StoreName SumOfAmount
09-Sep-08 Store #9848 2,690,000.00

When I backtrack through the queries used to obtain this result, the SumOfAmount IS 2,690,000.00. So I cannot figure out how the heck it becomes 66,005,000.00!!

How could the "Having" clause in my SQL statement make such a big deal? I realize that Having is necessary when you're working with Group by queries. I'm stumped.

Your advice would be appreciated.

 

[Christineeve]

OOps. Sorry I thought I spell check. However, TradeDate is a type. It should be FoDate. That is:

FoDate StoreName SumOfAmount
09-Sep-08 Store #9848 2,690,000.00

 

[Christineeve]

Ok. I have worked on this for weeks, but posting here must have helped because I did figure out one of my issues was on how I was making my join. I've fixed that.

Now, I need to detemrine why this query will not return the records between the start and end date.
Here is the SQL. It is taking the dates from a form and two textboxes, also we need to collect the store name as well.

SELECT
[qryFoRecords(total)_cl].SaleDate, [qryFoRecords(total)_cl].StoreName, [qryFoRecords(total)_cl].SumOfAmount

INTO tblSelectStoreTempTable

FROM [qryFoRecords(total)_cl]

WHERE ((([qryFoRecords(total)_cl].SaleDate)
Between [Forms]![frmFFSClientPrintOpts]![txtStartDate] And 
[Forms]![frmFFSClientPrintOpts]![txtEndDate])
AND (([qryFoRecords(total)_cl].StoreName)=[forms]![frmFFSClientPrintOpts]![cboStoreNames]));

The date that does return is the txtEndDate. The store name returns also, those items are correct. However, the query won't return all the rows with all the dates between txtStartDate and txtEndDate for that specific store.

Can you see anything in the query that I am doing incorrectly? I'm thinking somehow my syntax is incorrect.

Thanks for taking time to look at my question.

 

[traingamer]

However, the query won't return all the rows with all the dates between txtStartDate and txtEndDate for that specific store.

If one of your subqueries is only selecting records for a single date (as shown in your example)

HAVING (((FoRecords.PurchaseDate)=#9/9/2008#));

then that is the only date you'll get.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Christineeve]

Traingamer,
Thank you for responding. I appreciate it! The issue you were referring to was an issue regarding the "SumofAmounts." I've resolved that, yay!

I'm working on this issue, it is very similar to this post:

http://www.tek-tips.com/viewthread.cfm?qid=1530128&page=1

I used the suggestions from the example above, but did not have the results I need.

I still only receive all the rows in my critera with just that startdate. It's the first date I enter in the form (txtStartDate). In my example, I input 5/5/2008 for the start date and 9/9/2008 for the end date. Only records with 5/5/2008 are returned.

SELECT
[qryFoRecords(total)_cl].SaleDate, [qryFoRecords(total)_cl].StoreName, [qryFoRecords(total)_cl].SumOfAmount

INTO tblSelectStoreTempTable

FROM [qryFoRecords(total)_cl]

WHERE ((([qryFoRecords(total)_cl].SaleDate)
Between DateValue([Forms]![frmFFSClientPrintOpts]![txtStartDate]) And 
DateValue([Forms]![frmFFSClientPrintOpts]![txtEndDate]))
AND (([qryFoRecords(total)_cl].StoreName)=[forms]![frmFFSClientPrintOpts]![cboStoreNames]));

What do you think? I'm missing something...Thanks!

 

[traingamer]

I'm missing something

So are we.

The query you show appears to be ok on the surface, but what about the (apparent) subquery qryFoRecords(total)_cl? What are the criteria for that query? Is it actually selecting the records you think it is?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Christineeve]

First, thank you very much Greg for looking at my question. I know it's hard to help someone when that someone is not sure even how to ask the question.

You're right, something was overlooked. I needed to post the next level up query. This query is called, then the query above is called, queries follow that...it's a mess, it's what I'm stuck with.

Original Query with the Parameter [Purchase Date] that pops up. This needs to come from a form. I need to be able to input a start and end date. When I do that my SumOfAmount is incorrect.

Thank you again!

Paramter Query with Correct SumOfAmount:
PurchDate BankName SumOfAmount
08/05/2008 Store #9495 66,005,000.00

SELECT [Purchase Date] AS PurchDate,
qryFoRecords.PurchDate,
qryFoRecords.StoreName, 
qryFoRecords.Plan, 
qryFoRecords.[A/C#], 
BarLimits.SeqNum, 
BarLimits.Limit, 
BarLimits.[Exp Date], 
BarLimits.[W/T], 
BarLimits.[Index #],
qryFoRecords.Center, 
qryFoRecords.Acct, 
Sum(qryFoRecords.Amount) AS SumOfAmount, qryFoRecords.RateType, 
qryFoRecords.[Purch&Sold]

FROM
BarLimits INNER JOIN qryFoRecords ON (BarLimits.[Purch&Sold] = qryFoRecords.[Purch&Sold]) AND (BarLimits.StoreName = qryFoRecords.StoreName)

GROUP BY
qryFoRecords.PurchDate, 
qryFoRecords.StoreName, 
qryFoRecords.Plan, 
qryFoRecords.[A/C#], 
BarLimits.SeqNum, 
BarLimits.Limit, 
BarLimits.[Exp Date], 
BarLimits.[W/T], 
BarLimits.[Index #],
qryFoRecords.Center,
qryFoRecords.Acct, 
qryFoRecords.RateType,
qryFoRecords.[Purch&Sold];

New Query with Wrong SumOfAmount:
PurchDate BankName SumOfAmount
05-Aug-08 Store #9495 (112,800,000.00)

SELECT 
FoRecords.PurchDate, 
FoRecords.BankName,
FoRecords.Plan,
FoRecords.[A/C#],
BarLimits.SeqNum,
BarLimits.Limit,
BarLimits.[Exp Date],
BarLimits.[W/T],
BarLimits.[Index #],
FoRecords.Center,
FoRecords.Acct,
Sum(FoRecords.Amount) AS SumOfAmount,
FoRecords.RateType, FoRecords.[Purch&Sold]

FROM
BarLimits INNER JOIN FoRecords ON (BarLimits.[Purch&Sold] = FoRecords.[Purch&Sold]) AND (BarLimits.BankName = FoRecords.BankName)

GROUP BY 
FoRecords.PurchDate, 
FoRecords.BankName, 
FoRecords.Plan, 
FoRecords.[A/C#], 
BarLimits.SeqNum, 
BarLimits.Limit, 
BarLimits.[Exp Date], 
BarLimits.[W/T], 
BarLimits.[Index #], 
FoRecords.Center, 
FoRecords.Acct, 
FoRecords.RateType, 
FoRecords.[Purch&Sold]
HAVING (((FoRecords.PurchDate) Between [Forms]![frmFFSClientPrintOpts]![txtStartDate] And [Forms]![frmFFSClientPrintOpts].[txtEndDate]));

 

[Christineeve]

This can be closed. I wanted to just post a closure on this message. I found out that the parameter being used by the database query is useless. The parameter is only being used as a place holder.

There is no way to get this query to work with the parameter passed in from a form. It fundamentally changes the query and query output. How do I know, because the results are always different if I run it from a parameter passed in from the pop up and a paramter entered into it from a form.

Thus, unless the entire thing is redesigned, there is no way for me to "make this work."

Another unforunate example of trying to apply better database developing principals to a poorly designed database without being permitted to redesign it.

Sorry for venting. It's just frustrating to be hired to do things correctly to be told that it has to be done (quickly) incorrectly to meet expectations. Argh.