By Year/Quarter

On Fri, 24 Nov 2006 11:04:01 -0800, chickalina
wrote:

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.

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"?



"John Vinson" wrote:

On Fri, 24 Nov 2006 11:04:01 -0800, chickalina
wrote:

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.

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]

On Fri, 24 Nov 2006 16:12:02 -0800, chickalina
wrote:

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"?

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 Vinson" wrote:

On Fri, 24 Nov 2006 16:12:02 -0800, chickalina
wrote:

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"?

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]

On Fri, 24 Nov 2006 18:23:02 -0800, chickalina
wrote:

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...

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

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.

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.

"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.
"John Vinson" wrote:

On Fri, 24 Nov 2006 18:23:02 -0800, chickalina
wrote:

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...

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

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.

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.

"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]

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

"Pat Hartman(MVP)" wrote:

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.
"chickalina" wrote in message
news
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.

"Pat Hartman(MVP)" wrote:

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.

"chickalina" wrote in message
...
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.

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.

"John Vinson" wrote:

On Fri, 24 Nov 2006 18:23:02 -0800, chickalina
wrote:

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...

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

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.

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.

"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]