Re: first attempt at a report - HELP!

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 12:41:50 +0800

Hi Miranda.

The dates have to come from somewhere:
1. Create a new table to hold the dates.
2. In table design, create a field named (say) TheDate, Field Type of
Date/Time.
3. Save the table as (say) tblDate.
4. Enter all the necessary dates into this table. (Post back if you want a
piece of code to create all the date entries for you.)

Now you need a query to provide these dates to the report:
1. Create a new query containing both tblDate and your other table.
2. In the upper pane of the query design window, drag tblDate.TheDate onto
the date field in your other query. Access creates a line between the 2
tables.
3. Double-click this line. Access gives you 3 options. Choose:
        All records from tblDate, and any matches from ...
4. Drag TheDate from tblDate into the query. Make sure your FromDate and
ToDate get applied to this field, not to the date field from your other
table.
5. Save the query.

Now to use the query in the report.
1. Open the report in Design view.
2. Open the Properties box (View menu).
    Ensure the Title of the Properties box says "Report".
3. On the Data tab, set the RecordSource to the name of the new query.
4. Open the Sorting And Grouping dialog.
5. Specify TheDate as the field, and in the lower pane of the dialog, set
Group Header to Yes. Grouping on each value gives you a heading for each
date. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Miranda" <mirandapbourke@hotmail.com> wrote in message
news:eDq2TDR8DHA.1596@TK2MSFTNGP10.phx.gbl...
> hi guys, i'm v. new to reports. I have a report that shows some equipment
> usage details (equipName, numberUsed, DateUsed). I have a form which loads
> the report - on this form someone enters two dates: FromDate and ToDate
i.e.
> the period of equipment usage which should be shown on the report (based
on
> DateUsed). I want my report to be broken up into sections based on everyda
y
> between the two dates entered on my form. So even if no equipment was used
> on a particular day in between those dates, i want a blank section to be
> displayed...i hope this makes sense!
>
> i hope someone can help me!
>
> thanks in advance,
> miranda

Relevant Pages

  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Show all rows...Please help...
    ... If you want to report every hour and date, regardless of whether there are records or not, the data has to come from somewhere, so you will need to create a pair of tables that supply all the work dates, and and all the work hours. ... You can then outer-join that to your sales query, and get a record for every hour. ... Create a table with one field named TheDate, ... Create another query using qryDateHour and qrySalesByDateHour as source "tables." ...
    (microsoft.public.access.queries)
  • Re: Interesting form design problem: help!
    ... open in design view, click View> Sorting and Grouping (or click the toolbar ... records in a report". ... I thought it might be easier if I use a query instead then I can filter ... When I go through the wizard I choose these fields. ...
    (microsoft.public.access.forms)
  • Re: do I need more indexes?
    ... Each field in tReview is a rating of that criteria for a sample ... an annual aggregate report is needed. ... transfer all of the data to a new design. ... query; a crosstab query based on the union query ...
    (microsoft.public.access.tablesdbdesign)
  • Re: do I need more indexes?
    ... Each case in tReview is a row, ... an annual aggregate report is needed. ... transfer all of the data to a new design. ... query; a crosstab query based on the union query ...
    (microsoft.public.access.tablesdbdesign)