By Year/Quarter

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.

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

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.

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

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.

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.

On Wed, 22 Nov 2006 14:35:01 -0800, chickalina
wrote:

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.

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?

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

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.


"chickalina" wrote:

Won't what you wrote below mean that you have to put the year and date in
manually instead of having it automatically increment?

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

On Fri, 24 Nov 2006 08:20:02 -0800, chickalina
wrote:

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.

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.



"John Vinson" wrote:

On Fri, 24 Nov 2006 08:20:02 -0800, chickalina
wrote:

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.

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]