New; going from flat to relational

[Tim Fierro]

Hello,

I have had many years using flat file databases (File Express from way back)
but am now at a company where a relational database is needed and would
carry us into the future. Since I know some basics on databases, have VB
Pro programming experience over the years, and I know most of what we need
to carry as far as data; I have decided to create a database for our needs
instead of finding an off the shelf program that may not be what we want or
need.

But this will be my first adventure into MS Access. All small attempts in
the past had me resorting to flat file for convenience and data did not
'need' to be relational. The data at this company I just started for will
need to be relational as it will be an inventory type of program that needs
to take us into the future with job costing, inventory, and later data
retrieval of task involved per job..

I am reading all I can in the newsgroups, web sites, and little hints of
information to get a good basis in the design before I proceed. I figured
after a month of really looking at what we need, I should be able to design
and then start to write something that will get this company out of using a
spreadsheet for inventory and job costing; and into a database more suited
for the purpose.

A couple of questions I have right now that would help me understand a few
things are;

1) Are sub forms automatically created when you use the wizard to Create a
new database with tables? In the Northwind example, there is a data entry
screen/report that shows a Subform that I assume allows you enter invoices
or add receivables; I was wondering if this type of Subform would be created
automatically linked when I create the database and tables, or I will need
to consider creating the Subform later when I create the data entry screens.

This whole part of Subforms is something that a Flat File database doesn't
have and looks like a major staple for this new database I will make. I
need a way to track/view all items purchased, all items purchased through a
certain vendor, all tasks used in a job, all parts used in a task, all parts
used in a job, etc... A Flat File just doesn't offer the support to have a
table with only data like this; thus the relational part I suppose. :-)
2) Does Access have basic If/Then and Add/Subtract/Total/Sum type of
parameters to be used in Queries and reporting? I would think so, but
figured I would ask.

3) Considering #1 above, I will be needing an entry screen to put down all
items used per job. I think I will have to make an entry screen that will
have data from different tables, have the end user enter the data, and the
existing table(s) will get updated with this new data. Is this correct that
multiple tables can be updated utilizing a form that has these tables as
inputs?

Those questions are just at the top of my head while evaluating the database
design. Any answers would be appreciated.

Thank you,

Tim

[WindAndWaves]

"Tim Fierro" <ti*@duffertech.com> wrote in message news:7v********************@comcast.com...

Hello,

I have had many years using flat file databases (File Express from way back)
but am now at a company where a relational database is needed and would
carry us into the future. Since I know some basics on databases, have VB
Pro programming experience over the years, and I know most of what we need
to carry as far as data; I have decided to create a database for our needs
instead of finding an off the shelf program that may not be what we want or
need.
it will definitely be more efficient and cheaper to find something (and I am sure you can) of the shelf

it will definitely be more fun to do it yourself.

But this will be my first adventure into MS Access. All small attempts in
the past had me resorting to flat file for convenience and data did not
'need' to be relational. The data at this company I just started for will
need to be relational as it will be an inventory type of program that needs
to take us into the future with job costing, inventory, and later data
retrieval of task involved per job..
Almost any company in the world needs an inventory type database, so there must be one out there... even if you sell live snakes,
frozen bodies or ex presidents.
I am reading all I can in the newsgroups, web sites, and little hints of
information to get a good basis in the design before I proceed. I figured
after a month of really looking at what we need, I should be able to design
and then start to write something that will get this company out of using a
spreadsheet for inventory and job costing; and into a database more suited
for the purpose.

A couple of questions I have right now that would help me understand a few
things are;

1) Are sub forms automatically created when you use the wizard to Create a
new database with tables? In the Northwind example, there is a data entry
screen/report that shows a Subform that I assume allows you enter invoices
or add receivables; I was wondering if this type of Subform would be created
automatically linked when I create the database and tables, or I will need
to consider creating the Subform later when I create the data entry screens.
It is probably easiest just to make your own forms as you go. BUT your key to success is good datastructure. You can post your
datastructure here if you like.
This whole part of Subforms is something that a Flat File database doesn't
have and looks like a major staple for this new database I will make. I
need a way to track/view all items purchased, all items purchased through a
certain vendor, all tasks used in a job, all parts used in a task, all parts
used in a job, etc... A Flat File just doesn't offer the support to have a
table with only data like this; thus the relational part I suppose. :-)
2) Does Access have basic If/Then and Add/Subtract/Total/Sum type of
parameters to be used in Queries and reporting? I would think so, but
figured I would ask.
What do you think? I use iif a lot in queries iif(test, value for true, value for false)
3) Considering #1 above, I will be needing an entry screen to put down all
items used per job. I think I will have to make an entry screen that will
have data from different tables, have the end user enter the data, and the
existing table(s) will get updated with this new data. Is this correct that
multiple tables can be updated utilizing a form that has these tables as
inputs?
In principle yes, but this is one of the hardest things, in my opinion. To translate an efficient table structure into a form that
is userfriendly (combining data from multiple sources).
Those questions are just at the top of my head while evaluating the database
design. Any answers would be appreciated.

Thank you,

Tim

Sorry for the short answers, I have limited time, please ask more if needed.