Access Date / Quarter Question

Posted on 2004-11-24
Last Modified: 2008-03-17

I am setting up a DB used to keep track of sales and forecasted sales.

I import tables with orders, sales, etc from the system, they all have some sort of date "01/01/2004" value. But in addition I have to recollect some extra info. that someone adds into a separate table for forecasted sales per client per fiscal quarter. (01/01/yyyy-30/03/yyyy = 1) ( I am in Spain so the dates are the other way around).

What is the best way of retrieving the info from input via interface and what is the best way of setting up  the table? I would like for them to type in a sales number and then add a beg. date/end date, but it seems like a pain to create 4 records for each individual client for each year, this year's and next year's.
What would be a better way? Could someone shed some light.
Question by:CEGE
    LVL 26

    Expert Comment

    Yu can format any date into quarter using


    So you can enter any date in American or European format (makes no difference), then use the format command to filter your results for your clients
    LVL 1

    Author Comment

    Ok. But need I make them create 4 records for each client's forecasted sales, isn't there a better way?
    LVL 41

    Expert Comment

    Give us an example of the data in your table and the output that you would like to see from it.
    LVL 1

    Author Comment


    Main Table: Cust Code, Address, Contact, etc
    Job: Cust Code, order number, Price, date opened
    ( This one is theoretical) New table: Cust Code, Sales Objectives, date1, date2 (they would have to create 4 records for each customer per year, eg.

    Código                    Vtas Obj      Vtas_date1      Vtas_date2
    ABC  FARMA      3000      01/01/2004      30/03/2004 = 1st qtr
    ABC  FARMA      6000      01/04/2004      30/06/2004 = 2nd qtr
    ABC  FARMA      9000      01/07/2004      30/09/2004 = 3rd qtr
    ABC  FARMA      12000      01/10/2004      31/12/2004 = 4th qtr

    In the query, I would ask them to enter [date1] and [date 2], then the user would input the starting and ending dates for the qrtrs in question and I could then link the tables by Cust. Code and extract real sales and objective sales.

    The problem is that I am not convinced of the setup of the "New Table".

    Is this enuf info? Please get back to me.

    LVL 26

    Expert Comment

    Your table looks fine.
    In your query, you'd have the date entry field associated with Vtas_Date(1 or 2) as "between [x] and [y]"
    If you add an extra column to your query as:


    This will produce your quarter date automatically when the query is ran
    LVL 1

    Author Comment

    Thanks Danny:

    I see what you're saying. Regarding the table, or rather the input of the info. in a form
    These would be the fields:
    Cust Code            Sales Objectives   "Valid from:"  Date 1  "to" Date 2
    (as combo box)     (as number field)                     (as 00/00/0000 dates they type in)

    What bothers me is that they'll have to select the same company 4 times and type in the objectives 4x and date 1 & date 2, 4 xs. How could I set it up so that instead of field them typing in date 1 and date2, they would choose a quarter (1,2,3,or 4) and then chose a year (2004,2005, etc) That way it would be easy fr them, but then how would I link that info with the standard dates that are asked fr when queried, [date1] = 01/01/04 and [date2] = 30/06/2004?
    LVL 26

    Expert Comment

    If you have a control on a form, you can reference that to the query.

    Have a combo for your company, a text box for startdate and one for enddate (or use a calendar object)
    Then in your query, rather than    BETWEEN [x] and [y]  for your date, change to:

    BETWEEN [forms]![MyForm]![StartDate] and [forms]![MyForm]![EndDate]

    You can do the same for your company in the name field of your query:


    LVL 1

    Author Comment

    I appreciate yr help, and we are almost there, but I am referring to the data collection.
    How could I set it up on the form (feeding the table) so that instead of field them typing in date 1 and date2, they would choose a quarter (1,2,3,or 4) and then chose a year (2004,2005, etc).

    instead of;
    Código                   Vtas Obj     Vtas_date1     Vtas_date2
    ABC  FARMA           3000     01/01/2004     30/03/2004 = 1st qtr

    it would be:
    Código                   Vtas Obj     qtr    year
    ABC  FARMA          1000           1      2004
    ABC  FARMA          2000           2      2004
    ABC  FARMA          3000           3      2004

    In the qry, how would I link for example sales for : 01-01-04 and 30-06-04  with the sales objectives 1 &  2004 and 2 & 2004, when querying using date1 and date2.
    In other words how would Access know that 01-01-04 to 30-06-04 is equal to 1 and 2 of 2004?
    LVL 26

    Accepted Solution

    On your form, have a combo box list the quarter numbers (the wizard can do this - select "I want to type the values in" and enter 1, 2, 3, 4)
    Have anotehr entrybox (a textbox will do) to enter a startdate and another to enter your enddate

    Add an option group with two options: Quarter and Date Range
    Add a command button to run the query.

    In the code for the command button, you need it to do this:

    Check whether user has selected "DateRange" or "Quarter" as their filter
    Run query based on selection


    Select Case me.Frame1    'where Frame1 is the name of the option group

    case 1
    'if Quarter
    docmd.openquery "Name of quarter query"

    Case 2
    'if daterange
    docmd.openquery "Name of daterange query"

    end select

    The for your queries, you reference in teh same way as you did from the previous post -
    the quarter one with

    the date range on with
    Between [forms]![yourForm]![Startdate] and [forms]![yourForm]![enddate]

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now