Access Date / Quarter Question

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.
LVL 1
CEGEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dannywarehamCommented:
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
CEGEAuthor Commented:
Ok. But need I make them create 4 records for each client's forecasted sales, isn't there a better way?
0
shanesuebsahakarnCommented:
Give us an example of the data in your table and the output that you would like to see from it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CEGEAuthor Commented:
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
dannywarehamCommented:
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
CEGEAuthor Commented:
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
dannywarehamCommented:
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
CEGEAuthor Commented:
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
dannywarehamCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.