Struggling with this one... (1 Viewer)

Indigo · Oct 13, 2017

Good afternoon,

I am struggling with this one... I am trying to create a query to support a subreport and am drawing a complete blank. All of the data is contained in one table called "CheatSheet"... The table contains purchasing quotes for supplies. We have created a report whereby the main report shows one quote and we want to compare it to all of the other quotes in the table in a subreport. If the price of all of the other quotes is higher than the quote on the main report, we want the subreport to return no records or null and then I can essentially hide the subreport. If however, there is one quote lower than the quote on the main report, then we want to see all quotes returned.

Our current SQL statement for the subreport looks like this:

Code:

SELECT [CheatSheet].[QuoteID],[CheatSheet].[Vendor],[CheatSheet].[Price],[CheatSheet].[QuoteSubmissionDate],[CheatSheet].[Currency]
FROM [CheatSheet]
HAVING (([CheatSheet].[QuoteID])<>[Forms]![frmRFQ!][QuoteNumber])
ORDER BY [CheatSheet].[QuoteSubmissionDate] DESC;

Any direction you can provide would be appreciated. Thank you.

plog · Oct 13, 2017

You lost me with in all those reports (8 mentions) and quotes (8 mentions). However, I know SQL and see your syntax error:

HAVING is part of an aggregate query, the rest of your query is not. You are either missing a GROUP BY clause, or you need to use WHERE instead of HAVING.

That's syntactically. Logically, to get you a working query I would need sample data. 2 sets:

A. starting data from CheatSheet. I would need enough data to cover all cases.

B. Expected results of A. Show me what data you hope the query produces when you feed it the data in A.

Indigo · Oct 13, 2017

Hi plog,

I don't want the query to return the Quote details from the main report, only all of the other quotes in the table - that is the reason for the HAVING Clause. I took my SQL statement from the SQL view in MS Access.

I'm sorry but I am not sure what you want from me with respect to your A and B above? If I understand you correctly....I think the following is what you want...

Quote # 1 having a price of $250
Quote # 2 having a price of $300
Quote # 3.....$425
Quote # 4.... $225
Quote # 5.... $275

So if [Forms]![frmRFQ!][QuoteNumber]) = Quote 1, I want to see all of the other 4 quotes in the subreport. But if [Forms]![frmRFQ!][QuoteNumber]) = quote 4, then I want the subform to be blank or null.

Does that make sense?

plog · Oct 13, 2017

This isn't a query issue, it's a VBA issue.

You would use a DMin (https://www.techonthenet.com/access/functions/domain/dmin.php) to get the lowest value for your quotes, then if the current one is equal to that you don't show your subreport.

Indigo · Oct 13, 2017

DMin would only return one value....Not exactly what I was looking for and I can use DMin in a query.

I want to return either all records if there is at least one quote lower than my selected quote or no records if all quotes are higher than my selected value.

Mark_ · Oct 13, 2017

Indigo,

How else are you sorting out the quotes? My assumption is by vendor and another status, but the following may help you get on the right track!

Code:

'In the parent report, something similar to this 
If not IsNull( DLookup("[Vendor]", "CheatSheet", "[Vendor] = '" & Report![ReportName]!Vendor "' AND [Price] <= " & Report![ReportName].Price ) Then
   DoCmd.OpernReport SubReport
End If

Not sure which event this would be in, haven't had to do this kind of thing before. Basic logic is to use the DLookup to find if ANY quotes match your criteria, then print the subreport. If none do, do nothing.

That's what I get for trying to answer a question on here while people are asking me questions in real life! Can't finish typing before another answer pops up.

plog · Oct 13, 2017

...then I can essentially hide the subreport.

The simpliest solution is to not essentially hide the subreport, but to actually hide it.

If (CurrentPrice = DMIN()) then SubReport.Visible = False

This is possible with various SQL hacks and many subqueries, but your ultimate aim can be achieved with 1 line of VBA code.

Indigo · Oct 14, 2017

Thanks plog, I will give it a try

Struggling with this one... (1 Viewer)

Indigo

Registered User.

plog

Banishment Pending

Indigo

Registered User.

plog

Banishment Pending

Indigo

Registered User.

Mark_

Longboard on the internet

plog

Banishment Pending

Indigo

Registered User.

Similar threads

Users who are viewing this thread