[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Date / Quarter Question

Posted on 2004-11-24
9
Medium Priority
?
619 Views
Last Modified: 2008-03-17
Hello:

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.
0
Comment
Question by:CEGE
  • 4
  • 4
9 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 12665025
Yu can format any date into quarter using

Format(YourDate,"q")

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
0
 
LVL 1

Author Comment

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

Expert Comment

by:shanesuebsahakarn
ID: 12665519
Give us an example of the data in your table and the output that you would like to see from it.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 1

Author Comment

by:CEGE
ID: 12672754
Sure:

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.

thanks.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12673032
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:

Quarter_Date:format([tables]![MyTable]![Vtas_date2],"q")

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

Author Comment

by:CEGE
ID: 12673474
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?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12673574
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:

[forms]![MyForm]![CompanyCombo]

0
 
LVL 1

Author Comment

by:CEGE
ID: 12673704
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?
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 1600 total points
ID: 12680065
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

SO:

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
[forms]![yourForm]![Quartercomboentry]

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

834 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