Date Driven DB Design Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kimberlyb - 18 Jul 2007 18:20 GMT

I am rewriting a database I started incorrectly and I'm stuck on trying to
move it to a "date driven" DB. Basically I am creating a DB to track
projects (maintenance items), actual revenue (invoiced), forecasted revenue
(sales target - which is everchanging based on purchase orders received), and
an "outlook proforma" (one time forecast that is not updated for trending).
That way when I write queries it can be from a date period instead of having
to write a query for every scenario we want to interrogate. Also, for ease
of data entry, I need data that is captured monthly (like a crosstab) however
I'm not sure how to assign a date to a record which would allow me to have
one record per project. I don't want the end user to have to select date
parameters. Can anyone help me???

Thanks

Kimberly

Reply to this Message

Duane Hookom - 18 Jul 2007 19:50 GMT

I'm not sure I understand your question. Three suggestions seem clear to me
is normalize, normalize, normalize. Having one record per project seems like
a huge block to creating a functional and flexible application.

Signature

Duane Hookom
Microsoft Access MVP

> I am rewriting a database I started incorrectly and I'm stuck on trying to
> move it to a "date driven" DB. Basically I am creating a DB to track
[quoted text clipped - 11 lines]
>
> Kimberly

Reply to this Message

kimberlyb - 18 Jul 2007 19:58 GMT

Its really hard to explain in writing how we collect data. Currently, we are
collecting in an excel spreadsheet where there is one row per project and a
group of columns that represent data for that project. For instance, Jan-07
ASP(avg. selling price), Jan-07 cost, Jan-07 quantity, etc. for every month
once the project has started. The company would like to keep the same "Front
End" the same so it is seamless for the end user to move from excel to
access. I have written the DB to collect this information in fields however
it limits the reporting flexibility. How can I keep the front end the same
for the end user and add the reporting needs. As you can tell I'm very new
at DB design and this particular project has grown into a monster with the
"needs" of the company. Thanks for your quick response.

Kimberly

> I'm not sure I understand your question. Three suggestions seem clear to me
> is normalize, normalize, normalize. Having one record per project seems like
[quoted text clipped - 15 lines]
> >
> > Kimberly

Reply to this Message

Duane Hookom - 18 Jul 2007 20:44 GMT

I have seen some huge problems created by keeping the "look" of a previous
version. "look" should not drive table structures.

However, if you have all your information captured okay in your
un-normalized table, you can create a union query to normalize the data for
reporting purposes.

Signature

Duane Hookom
Microsoft Access MVP

> Its really hard to explain in writing how we collect data. Currently, we are
> collecting in an excel spreadsheet where there is one row per project and a
[quoted text clipped - 29 lines]
> > >
> > > Kimberly

Reply to this Message

Jamie Collins - 20 Jul 2007 11:43 GMT

On Jul 18, 6:20 pm, kimberlyb <kimber...@discussions.microsoft.com>
wrote:

> I am rewriting a database I started incorrectly and I'm stuck on trying to
> move it to a "date driven" DB.

If you already 'speak' SQL, download the free PDF of "Developing Time-
Oriented Database Applications in SQL" by Richard T. Snodgrass:

http://www.cs.arizona.edu/people/rts/publications.html

Jamie.

--

Reply to this Message