Newbie questions

I am trying to design a database for my business. I do custom
installations of electronics. For each job:

o I have a customer;
o I generate a quote or quotes (they may be iterations of the
same quote, or there might be an initial quote, then a year
later a phase II quote, and so on);
o I have several thousand parts or components. Each component
could come from multiple suppliers, each with their own
prices, delivery methods, payment terms, etc.
o I generate invoices (it would be nice if this would
integrate with Quickbooks Contractor Premium)
o there is usually a drawing or drawings for the house
which might be referenced in the quote (install a
framus at location 3.2.c)

I would like:

o my parts table to be made up of separate files or
tables. A supplier may change his prices for a particular
part, so I need to update the part record for that
supplier; a manufacturer may alter their product line,
so I need to add or delete parts, and some suppliers
may run out of old stock or start offering the new stock
independently of the other suppliers. Some
suppliers may provide me with an Excel file or a
tab delimited file, while others may have the pricing
on a web page which I will have to cut and paste.
I think I could limit the number of suppliers for a part
to 5, out of the 15 suppliers. But do I have a field for
each supplier and enter a price for the 5 cheapest,
or is there a supplier_ID/price field that occurs up to 5
times in the table.
o to generate a quote, and then be able to optimize
the quote based on pricing from different suppliers,
but I also need to take into consideration freight
costs (supplier A might save me $10, but supplier
B might not charge freight on orders over $1,000,
so if the order is for more than $1,000, and the
difference in cost is less than the cost of freight,
use supplier B)
o I typically generate a quote for the whole job,
but invoice in phases. It would be nice to go
through the quote, check off which parts are being
installed now, and generate the invoice for that
work, then come back later, invoice for a few more
parts, etc. Maybe there would be a field for invoice
number, and I would check the parts being used,
and it would generate invoice X for those parts. Then
when I want to generate the next invoice, I check off
which parts are being used/installed, and it would
generate invoice X+1. Similarly, I would need to
generate purchase orders in the same fashion.
o an inventory capability. Even though I don't stock
much inventory, it would be nice to know that I have
a widget on the truck and a framus in the storeroom,
so I don't need to order either of them.

Could someone point me to some examples that might be close to this?
I've looked at the order fulfillment example that comes with Access,
and I could modify this, but some of the things I listed above might
require something more than just tweaking that application.

Thanks,

Steve

Steve Griffin wrote:

I think you should decide whether you want to be an electronics
installer or a database designer.
While what you want is certainly doable, its a fairly major project, and
one which would need a good overall planning and design phase before you
could even get a start, even if you didnt implement all the features in
the initial phase.
Personally I would be looking for a complete commercial solution, to
actually help you in your job, rather than play with many attempts based
on simple examples.
That said, database design is interesting stuff, but you will need to
read up on design and normalisation concepts before you tackle this.

--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Design Engineer J & K Micro Systems
Microcomputer solutions for industrial control
Note reply address is invalid, convert address above to machine form.

On Tue, 11 Jan 2005 08:23:30 +1000, Adrian Jansen
wrote:

Steve Griffin wrote:

I think you should decide whether you want to be an electronics
installer or a database designer.

I think you should learn to read. I didn't ask you for any career
advice mate. By the way, you seem to dabble in both realms
(electronics and database software), why can't I?

Steve Griffin wrote:
On Tue, 11 Jan 2005 08:23:30 +1000, Adrian Jansen
wrote:


Steve Griffin wrote:

I think you should decide whether you want to be an electronics
installer or a database designer.


I think you should learn to read. I didn't ask you for any career
advice mate. By the way, you seem to dabble in both realms
(electronics and database software), why can't I?

Sorry if my suggestions seemed offensive, I didnt mean it that way.

What I was getting at is that as a self-confessed newbie, you could
spend a lot of time learning database stuff and debugging a system, when
all you really want is to have it running and helping you in your job,
not costing you time and money fixing problems when a something goes wrong.

Yes I do both database and electronics design, but I dont rely on my
database skills to run my business itself. I could, but I find I can
spend more productive time elsewhere.

More constructively, you have outlined what sort of things you want, and
I can see that there is a fairly big effort in converting them into a
working design. And no I dont know of an existing database 'template'
you could start from. Once you have the concepts of design and
normalisation well understood, which you can get with any good textbook,
or even studying the standard Northwind example thoroughly, then what
you want is relatively straightforward, although not simple. Also I
would poke around on the web site http://www.mvps.org/access/ There is a
huge amount of tips and tricks there.

Specific questions like "How do I do xyz in Access" are well answered
here and on the other Access groups, but very general design questions
are much more difficult, not to answer, but to put enough time in to get
a reasonable result.

--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Design Engineer J & K Micro Systems
Microcomputer solutions for industrial control
Note reply address is invalid, convert address above to machine form.

Sorry if my suggestions seemed offensive,
Not offensive, but not exactly on topic.

What I was getting at is that as a self-confessed newbie

Just to clarify a little, I'm not new to software. I started
programming in 1971. I received a BS in CS in '77.

I spent 10 years from '77 to '87 programming in a realtime scientific
environment. My electronics experience was limited to things from
board installs all the way down to chip level replacements, such as
EEPROMs. Then I got into management and didn't do much programming
after that. Since my experience is in the realtime world, and some of
it predates PCs, RDBs, etc., I'm not well versed in this area.

On the hardware side, I'm self-taught as far as the things I need to
know for this business, so I don't have the formal training that I had
in the software world, and I only started this business 10 years ago.

But I understand the concepts of designing systems. I don't have the
$5K for an "off the shelf" system. So I'm trying the Usenet to see if
I can make any headway.

I have created a single supplier version and populated the products
table from an Excel spreadsheet for one of my suppliers.

I think maybe I should break this down into individual questions.
Would there be a better place to post those questions, such as
microsoft.public.access.gettingstarted?

I think maybe I should break this down into individual questions.
Would there be a better place to post those questions, such as
microsoft.public.access.gettingstarted?

That's always the best thing to do here. The .gettingstarted newsgroup is a
good place for some general, low-level questions. Microsoft.public.access is
a good place for general questions of all levels. For questions related to
specific database object issues, it's best to pick the most appropriate
newsgroup (such as .forms, .queries, .reports, etc.). If you get the wrong
group along the way someone will be glad to point you in the right
direction.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

If you were to spend 2 hours every weekday on this for 12 months, Steve,
then you would have exceeded the cost of the $5,000 off-the-shelf package if
you were to value your time at as little as $10 per hour. There are many
good reasons for learning to develop applications yourself rather than
buying them, but saving money is definitely not one of them.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

"Steve Griffin" wrote in message
...
Sorry if my suggestions seemed offensive,
Not offensive, but not exactly on topic.

What I was getting at is that as a self-confessed newbie

Just to clarify a little, I'm not new to software. I started
programming in 1971. I received a BS in CS in '77.

I spent 10 years from '77 to '87 programming in a realtime scientific
environment. My electronics experience was limited to things from
board installs all the way down to chip level replacements, such as
EEPROMs. Then I got into management and didn't do much programming
after that. Since my experience is in the realtime world, and some of
it predates PCs, RDBs, etc., I'm not well versed in this area.

On the hardware side, I'm self-taught as far as the things I need to
know for this business, so I don't have the formal training that I had
in the software world, and I only started this business 10 years ago.

But I understand the concepts of designing systems. I don't have the
$5K for an "off the shelf" system. So I'm trying the Usenet to see if
I can make any headway.

I have created a single supplier version and populated the products
table from an Excel spreadsheet for one of my suppliers.

I think maybe I should break this down into individual questions.
Would there be a better place to post those questions, such as
microsoft.public.access.gettingstarted?

Ok, thanks for the clarification. Sometimes when a new question bobs
up, its very hard to tell the level of experience of the writer.

However you will find that database design is very different from linear
programming. Even the basic concepts are different. Databases are
built around set theory and relations. So even though they both run on
th same computer, the whole thought processes going into the design are
done in an entirely different way. To make it even more complex, things
like Access use both database engines to run SQL queries, and linear
programming in VBA to do procedural stuff that is easier that in SQL, so
you have both to cope with. Makes a very rich and powerful system, but
takes some getting the hang of.

To get down to specifics, your query about multiple suppliers. This is
best handled as a separate table, one for the suppliers, and what is
called a junction table, containing the cross references between the
suppliers and the parts, one record for each reference.

I found one of the best examples of how to do that, and handle multiple
situations was the "At your survey" database done by Duane Hookom.
Nothing to do with parts, but the basic concepts are the same.

See http://www.rogersaccesslibrary.com/d...uanehookom.htm.

--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Design Engineer J & K Micro Systems
Microcomputer solutions for industrial control
Note reply address is invalid, convert address above to machine form.