If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Masonic Emblems Where can I get online ? Thanks | Vanman | General Discussion | 3 | September 23rd, 2004 08:26 PM |
FAQ - frequently asked questions - please read before posting - June posting unofficial | Charles Kenyon | Tables | 4 | June 28th, 2004 02:58 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - May | Charles Kenyon | Formatting Long Documents | 3 | May 24th, 2004 05:54 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - April | Charles Kenyon | Formatting Long Documents | 0 | April 26th, 2004 03:33 PM |