Table Field Names for data relating to Financial periods

[David Saywell]

Hi,

I use Access 2000 to store and report on data extracted from an RMS (Point
of Sale and Back Office). To improve the reports I produce I want to
incorporate a General Ledger Budget Table (primarily Sales & COGS). The
budget data I have been given is in an Excel Spreadsheet and has the
following fields:

GLAccount
July_Budget$
August_Budget$
September_Budget$
October_Budget$
etc.

I am not sure if this is the best way to store the data. The only other way
I can think of would be to have:

tblBudget
GLAccount
Month
Budget$

and have 12 records (one per month) for each GL Account.

If this is the case I need only devise a routine for importing the data from
the spreadsheet to the Access Table (separate forum...!)

Sales & COGS GL Accounts are linked to sub ledger product departments which
enables Actual Sales by product department to be compared to Budget. All
subledger data contains a transaction date.

Any suggestions on table layout and content would be appreciated

Thanks,

David

 

[Jamie Collins]

I use Access 2000 to store and report on data extracted from an RMS (Point
of Sale and Back Office). To improve the reports I produce I want to
incorporate a General Ledger Budget Table (primarily Sales & COGS). The
budget data I have been given is in an Excel Spreadsheet and has the
following fields:

GLAccount
July_Budget$
August_Budget$
September_Budget$
October_Budget$
etc.

I am not sure if this is the best way to store the data. The only other way
I can think of would be to have:

tblBudget
GLAccount
Month
Budget$

and have 12 records (one per month) for each GL Account.

If this is the case I need only devise a routine for importing the data from
the spreadsheet to the Access Table (separate forum...!)

Sales & COGS GL Accounts are linked to sub ledger product departments which
enables Actual Sales by product department to be compared to Budget. All
subledger data contains a transaction date.

Any suggestions on table layout and content would be appreciated

Here are a few quick suggestions.

Names should be sufficient to identify an element. 'GLAccount' doesn't
tell me whether it's an ID, name, description, etc. Tables usually
hold multiples of the same type of entity so a plural noun is most
appropriate. Prefixes such as tbl are redundant. Suffixes such as $
can be misleading e.g. is it Hungarian notation, a BASIC string
variable, an Excel worksheet name? Time is in continuum so use a start
date and end date (you need CHECK constraints to ensure start is
before end, there are is no overlaps, etc); this will enable
comparison with other date/time values (e.g. a transaction's dates).
Something more like:

Budgets
GLAccount_ID
start_date
end_date
budget

Jamie.

--

 

[David Saywell]

Thanks Jamie.

I will proceed on that basis.

David