By Year/Quarter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that each record needs to be tracked by year/qtr. Ex: Vendor
taxes.

I need to do the following:

- Track by Quarter for every year forevermore.,, 2006 Q1, 2006Q2, etc. so
the year-Qtr could be the unique identifier. Autonum is not needed (or is it?)
- The subform for each vendor form needs to show only 5 years out.
- When a change occurs in Q2 of 2007, subsequent years need to change, but
back years remain what they were.

Can this be done?

Thanks.
Thanks.
 
Year and quarter are sufficient for the PK(as long as you don't use Year and
Quarter as the field names). You don't need an autonumber. I tend to use
autonumbers rather than multi-field primary keys to simplify the joins and
make it easier to use combos if the table has child tables. If you use an
autonumber as the PK, you will still need to have a unique index on the year
and quarter columns to enforce the business rule that only a single record
can exist for each quarter for each year.

Use criteria to control the quarters shown on forms and reports.

I don't know what you mean by "change". The value of one record should not
be dependent on the value of a previous record. Changing a value in Q1
should have no impact on any other record.
 
I have multiple vendors that will require tracking by quarter... will I need
a separate table for each vendor for year/quarter? When I put the subform on
the page and start typing in one vendor and put number in for 2006 and 2007 ,
I'll go to the next vendor and it starts in 2008 instead of starting it's own
2006.

What I mean by change is that if a change is made in Q2 of 2007, I want the
change to affect every quarter after that so they don't have to be changed
individually, but I want everything prior to that to remain the same so we
can track the changes.

Thanks.
 
Also, here is what I did for the table
YEARID - Autonum - (used an append to start years at 2006) - PK
Q1
Q2
Q3
Q4

Would this be easier? This is what I used when I had that problem with
incrementing between vendors.
 
You would not use a separate table for each vendor. That would become a
nightmare. Your table's primary key (or unique index) should be VendorID,
ReportingYear, ReportingQuarter. Or you could use VendorID and
QuarterEndDate. Using a real date field rather than just a year and quarter
number will make doing crosstabs and pivot tables/charts simpler. There
should be four rows per year, NOT four columns. You will find that
normalizing the structure will result in simplified reporting.

I still don't know what change you're talking about. The only other column
in this table would be an amount field. If you are thinking of storing
Vendor information in this table, think again. There should be a separate
table that defines the attributes of a vendor. That way there would be only
a single place to record a vendor's name or address for example. So there
would be 1 row in the vendor table and many rows in this table.
 
What I mean by change is that if a change is made in Q2 of 2007, I want the
change to affect every quarter after that so they don't have to be changed
individually, but I want everything prior to that to remain the same so we
can track the changes.
Click to expand...

If the value you're looking at is a running sum, then *it should not
be stored in your table at all*.

Instead, store the value for each quarter and calculate the running
sum in a Query or on a Report.

John W. Vinson[MVP]
 
Won't what you wrote below mean that you have to put the year and date in
manually instead of having it automatically increment?
 
I don't understand what you mean by the numbers should be stored as rows. I'm
thinking along the lines of Excel (which is how the numbers are tracked right
now). You would have the years listed on the left and the Quarters going
across the top.

Should I have 6 tables?

VendorID
Year ID
Quarter1ID
Quarter2ID
Quarter3ID
Quarter4ID

Every vendor with nothing would need to have a blank row for current year in
the subform when you open it up so information can be entered.
 
thinking along the lines of Excel (which is how the numbers are tracked right
now). You would have the years listed on the left and the Quarters going
across the top.

Should I have 6 tables?

VendorID
Year ID
Quarter1ID
Quarter2ID
Quarter3ID
Quarter4ID

Every vendor with nothing would need to have a blank row for current year in
the subform when you open it up so information can be entered.
Click to expand...

Excel is a spreadsheet, a good one.
Access is a relational database.

THEY ARE DIFFERENT.

Applying spreadsheet logic to a relational database will, guaranteed,
get you a faulty design - just as it is doing in your case!

In a database, "fields are expensive, records are cheap". You should
NEVER store data (quarters, say) in fieldnames.

What I would suggest for your database is two tables:

Vendors
VendorID
VendorName
<other info about the vendor as an entity>

VendorSales
VendorID <link to Vendors>
SaleDate <e.g. #6/30/2006# for the 2nd quarter of 2006>
SaleAmount <the sales during that quarter, NOT CUMULATIVE>

That is - store the basic INFORMATION (the amount of sales in each
quarter). You can easily create a Totals Query or a Report which will
group by Vendor, sort by SaleDate, and display years and quarters with
the cumulative amount up to and including that date.


John W. Vinson[MVP]
 
Thanks for the reply, but I don’t think I’m clear:

I want to make this as easy as possible for the data entry person to put the
numbers in… I just want them to have 5 fields to maintain… Q1, Q2, Q3, Q4,
and Reason as follows in a subform:

Q1 Q2 Q3 Q4 Year End Reason (combo box)
2006 20 20 20 20 80 Decline
2007 10 10 10 10 40 Decline

The form/report would calculate year end.

Subsequently, the subform should only show current year plus the next 4.
They are 5 year plans.
 
Thanks for the reply, but I don’t think I’m clear:

I want to make this as easy as possible for the data entry person to put the
numbers in… I just want them to have 5 fields to maintain… Q1, Q2, Q3, Q4,
and Reason as follows in a subform:

Q1 Q2 Q3 Q4 Year End Reason (combo box)
2006 20 20 20 20 80 Decline
2007 10 10 10 10 40 Decline

The form/report would calculate year end.

Subsequently, the subform should only show current year plus the next 4.
They are 5 year plans.
Click to expand...

Ok, if you're comfortable using an improperly normalized, redundant
design, and will never want to ask embarrasing questions like "What
year and quarter did client 4124 have 33", why not just create a Table
with fields

YearNum, Q1, Q2, Q3, Q4, Reason

and base a continuous form on the data?

You can easily create a Report with a Query adding up the four
quarters as a calculated field.

Maybe you would do better to do this all in Excel, since you seem
throughly committed to the spreadsheet mindset, and aren't willing to
consider working WITH Access instead of struggling against it!

John W. Vinson[MVP]
 
I must apologize for trying to fit Access into the Excel mindset and being
dense.

It just doesn't make sense the way you suggested. I will try it that way...
it would be a one (Vendor) to many (quarters) relationship, yes? but the data
entry person will have to put the number of the year manually instead of
using an autonum starting at 2006 set to increment?They would also have to
type in the dates that correlate to the end of a quarter? Do I have this
right?

I created the rest of the database in Access, which I am in the process of
fine tuning... this is the only part I'm having trouble with. Maybe because I
read a Many-to-Many article and thought that's what I needed since Vendors
will have many quarters, but each quarter will also have many Years.

Or do I just "not get it"?
 
I must apologize for trying to fit Access into the Excel mindset and being
dense.

It just doesn't make sense the way you suggested. I will try it that way...
it would be a one (Vendor) to many (quarters) relationship, yes? but the data
entry person will have to put the number of the year manually instead of
using an autonum starting at 2006 set to increment?They would also have to
type in the dates that correlate to the end of a quarter? Do I have this
right?

I created the rest of the database in Access, which I am in the process of
fine tuning... this is the only part I'm having trouble with. Maybe because I
read a Many-to-Many article and thought that's what I needed since Vendors
will have many quarters, but each quarter will also have many Years.

Or do I just "not get it"?
Click to expand...

Perhaps I'm not "getting it" myself, and am jumping to conclusions
that are inappropriate for your business model.

What information does this table record? What do the numbers in each
quarter represent? What Entity (real-life person, thing, or event)
does each record in the table represent?

I would treat years and quarters as a special case of a Date. 4th
Quarter 2005 is followed by 1st Quarter 2006 which is followed by 2nd
Quarter 2006 etc. - and you can very easily display an Access
Date/TIme value as quarter and year: store #1/1/2006# in a table field
and set the Format property of a textbox to

"\Qq/yyyy"

and you'll see Q1/2006 on your form or report.

An Autonumber is absolutely NOT appropriate here. Autonumbers are not
controllable, and their *ONLY* purpose is to create a unique arbitrary
identifier. If you want to automate data entry, you could set the
DefaultValue property of a field to (say) Year(Date()) to make all
records start out with the current year - but without understanding
just what you're trying to accomplish, that may not be desirable.

John W. Vinson[MVP]
 
John,
I appreciate your patience... I really do. I want to be a programmer.
Most stuff I've learned in the past couple of weeks, I've learned from the
internet and Access Help. This is just killing me....

The "parent" table is for tax ideas...
IdeaID
Idea Description
Category
etc..

the "child" table will represent the amount of reserve set aside by quarter
ReserveID
(however I need to set up the form for the quarters by year)
etc...

There will be two views:
Next year by quarter on the form itself
Next year plus 4 (5 year strategic plan) in a subform

The year end calculations will be done on the form and on the reports.

Then of course (something on my wish list), if you make a change in Q3 of
2007, the following quarters will change but the previous ones keep the old
number. When a change is made I want to create a button for the date and the
reason for the change. This can probably be an autonum.
 
John,
I appreciate your patience... I really do. I want to be a programmer.
Most stuff I've learned in the past couple of weeks, I've learned from the
internet and Access Help. This is just killing me....

The "parent" table is for tax ideas...
IdeaID
Idea Description
Category
etc..
Click to expand...
the "child" table will represent the amount of reserve set aside by quarter
ReserveID
(however I need to set up the form for the quarters by year)
etc...
Click to expand...

How are these two tables related - if at all? If it's truly a child
table, then there should be a "foreign key" linking the parent table
to the child table; is that IdeaID? Are these tax ideas for a client,
or just for yourself? What are the CONTENTS!! of the child table?
There will be two views:
Next year by quarter on the form itself
Next year plus 4 (5 year strategic plan) in a subform
Click to expand...

DON'T confuse data STORAGE with data PRESENTATION.

Data should be stored *logically* based on the relationships between
the information in the tables.

If the "next year" and the "next four years" are the same *kind* of
information, then they should all be in one Table. They may or may not
be presented on a form and a subform, vs. one contiuous form - the
form/subform certainly makes your life a lot more complicated.
The year end calculations will be done on the form and on the reports.
Click to expand...

Good. That's the right place.
Then of course (something on my wish list), if you make a change in Q3 of
2007, the following quarters will change but the previous ones keep the old
number. When a change is made I want to create a button for the date and the
reason for the change. This can probably be an autonum.
Click to expand...

"This" can be an autonum? WHat? The button? the date? the reason? No,
*none* of these can be autonumbers. I suspect you may be using the
term "autonum" differently than Access does. An Autonum is just a Long
Integer which increments; if you delete a record, or even hit <Esc> in
the course of entering the record, that number will be used up
forever, leaving a gap. Autonumber are JUST 1, 2, 4, 5, 6, 12, 13, 14,
.... on and on until you quit entering records. They have no meaning.

If the value (you STILL haven't said what "the value" represents,
other than that it has something which I don't understand to do with
taxes) in a given quarter depends on what the value was in some
previous quarter, then your table is still not properly normalized. A
field value should depend on the Primary Key (year and quarter??) of
that record, and on *nothing else*.

We're still not fully understanding one another I fear, but I'm game
to keep trying!

John W. Vinson[MVP]
 
The values that will be entered are amounts of money (by quarter) set aside
as a reserve to pay taxes. These will all be stored by quarter. On the form I
will sum up for the year end.

The relationship between the parent and the child is the Idea ID... IdeaID
will be the "one" and ReserveID will be the "many".

How would you suggest showing the 5 year plan? not in a subform? Right now,
I have an Ideas form with tabs, the first tab being the idea and some basic
information, the second tab is rating the idea, and the third tab would show
the next year by quarter and the four years after that as a year total.

I'll learn to explain myself in Access speak eventually.
Thanks.
 
Pat,
John is trying to help me set up the table that will store the information
by quarter. How would you set up the table to do this? I think that I'm not
explaining myself correctly, and really frustrating John... I'm also
frustrating myself.
M
 
OK...
I created a table as follows:

tbl_Quarter
IdeaID (number-primary key)
QtrEndDate (date-primary key)
Value (number-field)
Reason (text with combo box to pull down for the reason for a change in value)

I have an administrative form that you can enter the quarter ending date and
the value for that quarter and it records it for each record separately. What
I am trying to do is this with the data:

- on a subform on a tab, a snapshot of the next year by quarter and the
following four years by year total only.

- On a report show the idea with the yearly totals (it's a strategic 5 year
plan). But they need this to look like the spreadsheet they have now. A
prompt would have to be given for the starting year for the report and spit
out the 5 year strategic plan.

Does this make sense? I'm sorry if it doesn't but I've been combing the
internet for 3 days trying to figure this out and I'm at my wits end.
Thanks.
 
Back
Top