SQL or Query Help

[jaxhere]

I'm a bit new to Access and need help setting up a report based on a query.

I have a table with the following fields

• Date
• DocumentNumber
• Client
• ClientIDNo
• SaleAmount

Some of the DocumentNumber data is strictly numeric, some have a prefix "F"

What I want to do is the following:
For each unique date, extract the lowest and highest DocumentNumber and calculate a total of the SaleAmount fields which have that same date,but which do not have the prefix "F".
For each date extract all the individual records which have the prefix "F"


The end result would be a table with all records ordered by date showing:

For the non-prefixed doc numbers, one record for each date:

Date, From(Low unprefixed doc number), To(High unprefixed doc number), Client, ClientIDNo, Total (SaleAmount for the Date)​

For each docnumber prefixed "F", each record (could be several with the same date):

Date, prefixed doc number, Blank Field, Client, ClientIDNo, SaleAmount from that record​

I'm supposing I need to set up a query using SQL and then, from that query produce my report. What I'm having most trouble with right now is the SQL statements to set up the query.

Can some one give me an idea of how to write the SQL for the query?

 

[jaxhere]

Maybe I started with a problem which was badly stated, so I'll try to break in down into separate steps.

To start, how do I set up a query to report the lowest invoice number, and the highest invoice number with the same date, and show me a total of the amounts for that day.

Suppose my initial table contains the following fields:
Date, Invoice Number, Customer Name, InvoiceValue.

My query table should have the following fields:
Date, StartInvoice, LastInvoice,TotalofInvoiceValuesforDay.

 

[Ziggy1]

I always try to do things without writing the SQL, so take a look at my example to see if it produces the result you want.

Run the query qrySales for the final results

 

[jaxhere]

Not quite what I need

Ziggy, your solution doesn't seem to produce what I need.
I've modified your proposal to show what I want in a sample table called tblResults and also a report based on that table. In the zip file there's also a text file with a bit more information about what this is all about. I've also added a couple of queries which get me part of the way there, but I'm lost about how to bring it all together -- most particularly summing the invoices on each day.

 

[jaxhere]

Making progress- can now combine different data types

I've made some progress in my project which others might find interesting if you need to produce a report which included two different types of documents, one of which you need to report in detail, and the other which you need a daily summary which shows the beginning and ending number for each day.

I'm still having some challenges, and I've described these in the attached txt file. Any help or pointers on these would be most appreciated.

 

[Ziggy1]

Hi Jack

I'll look at it again on the weekend, I got a few queries of my own I am working on. I haven't looked at your latest DB, but a Subreport might be another option?

 

[Ziggy1]

Answer to 1) in attachment

First off you don't need qry1SalesNum & qry2SalesF-prefix in the macro they are just select queries.

You don't have to worry about duplicating or repeating because the Macro is re-creating it every time.... I don't think you triggering it anywhere, you would want to change the switchboard to run the macro