microsoft access question

Factual Questions
1

I am just learning Access and have hit a wall:

I have a table of broker quotes, each having one of three possible terms (90, 180, or 360 days).

For each term, I need to select the quote with the best margin (ask less bid; a calculated field in my query) and then display the bid and ask price for each selected quote in a report.

I have tackled this in two ways, but can’t get it to work.
method #1
Set up a subquery that uses the MAX() aggregate function. This works fine for finding the largest value, but then I can’t attach that value to a specific record.
method #2
Set up three seperate queries for each contract term and then sort by margin and include the first record in each query in the report. My problem here is that I need to then pull data from three different queries into my report and I can’t figure out how to do that - as far as I can tell a report can only be attached to one single query or table.
Can anyone help me out?

2

Just off the top of my head, you can use your queries to create the table the report is based on. First, run your queries and load the table, then use the table as the recordsource for your report.

3

Since I don’t know what your table’s look like, it’s hard to give a specific answer, but something like this as a query should work:


SELECT Table1.stockname, Table1.data
FROM Table1
WHERE (((Table1.data)=(SELECT max([data]) from [Table1])));

So, if you have a Table1 with data:


stockname    data
MSFT         100
AOL           50
JDSU         150
RHAT          85

The above query should return:


stockname    data
JDSU         150

I think that’s what you want

4

It is true that only a single query can be the source of the report. However, what I think that you are missing is that you build queries on queries. So, you could take the the three queries that you were talking about and use then just like tables in another query. Of course, you will need to do joins and such. Just treat them exactly like tables. This is the easiest way to set up subqueries in your report. Then, just build the final report on the master query.