S
Steve Griffin
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
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