Ok, let's see... my business is on the internet so every order comes to me via an email that has the following information: Order Number Date Billing name, address, phone no., and email address Shipping Name, address, and phone Items ordered Item No, Description, Qty, price, and line total Shipping method shipping total Subtotal Discounts Tax Grand Total Method of payment Card No. and exp date (if credit card) or transaction no (if Paypal) Right now, I use Excel spreadsheets. For each order, I have to enter all the information for the invoice. Then I update my inventory, then I enter the numbers into a sales spreadsheet, then I enter the amount that will be deposited into my checking account. And then I have to enter the name and address info again for the shipping label. It's really time consuming and repetitive. I want to enter this information one time and have it update my inventory sheet and sales sheet along with printing out an invoice for my customer. As for what I've built so far in Access. I have the following tables: Orders Order Details Receipts Inventory Invoices Customers Would it help if I send you this (messy) .mdb file to look at? ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Rhonda
Sugar Bearz ~ Fine Quality Children's Gifts & Clothing www.sugarbearz.com -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Liveson Sent: Tuesday, April 11, 2006 1:55 AM To: [email protected] Subject: Re: [ms_access] questions Rhonda Sorry that I've just seen your message now. As I'm writing it's 7:35am beginning of a new day. Well, do you mind listing down the fields of each table? That way I may be able to point out any errors if there are any. What I had in mind is that the Receipts table should have fields like the following: ReceiptNo, InvoiceNo, ReceiptDate, Amount, ChequeNo, etc just to track receipts The Orders table to have fields like these OrderID, CustomerID, OrderDate, ItemNo, Shipped, ShippingDate, Amount, etc However, this depends entirely on your needs. If you feel you can do without some of the fields or tables you can drop them, after all you know the fields you had in your spreadsheet. Regards Liveson ----- Original Message ----- From: "Rhonda Jenkins" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Tuesday, 11 April, 2006 01:22 Subject: RE: [ms_access] questions Ok, I have these tables created. I tried to do what you said as far as creating the primary and foreign keys and relationships. I'm not confident it's set up "correctly" however. What should I do now? ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Rhonda Sugar Bearz ~ Fine Quality Children's Gifts & Clothing www.sugarbearz.com -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Liveson Sent: Monday, April 10, 2006 12:43 PM To: [email protected] Subject: Re: [ms_access] questions Rhonda The order in which the tables are created does not really matter. You can start with any table. - Create a new database - In the database window and on tables tab click New and then Design View - Type the field name in the Field Name column, select data type in the Data Type column and description in the Description column. If this is the primary key (unique key in the table that must not be duplicated) then click on the key icon on the toolbar - Create the other fields. - Save and exit table design - Do the same for the remaining tables Once all the tables have been created, select Relationships from the Tools menu. A relationship window will appear. Add all the tables to the window. Drag the primary key of each table to the corresponding foreign key in the other tables (a field in one table which is not a primary key in this table but is a primary key in another table is called a foreign key). Check Enforce Referential Integrity and also check particularly Cascade Update Related Fields. Depending on your preference you may also check Cascade Delete Related Records. I hope this can keep you going for the next hour or so!!! Regards Liveson ----- Original Message ----- From: "Rhonda Jenkins" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Monday, 10 April, 2006 17:52 Subject: RE: [ms_access] questions Hi Liveson Don't assume anything. I am familiar with the concept of databases but I have not used Access before other than to play around with the templates - trying to change them to suit my needs and ending up pulling my hair out in frustration. I *basically* understand what you told me. Here is what I am planning to do. Please tell me if this is correct. I should start a new database and create the five tables as you stated below. Should I start with the inventory table (Items with primary key ItemNo) first? Then move on to the customers table? Or do I have to build all five tables before I can do anything? ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Rhonda Sugar Bearz ~ Fine Quality Children's Gifts & Clothing www.sugarbearz.com -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Liveson Sent: Monday, April 10, 2006 11:43 AM To: [email protected] Subject: Re: [ms_access] questions Rhonda I assume you are a bit familiar with relational database management system which MS Access is all about. From your description I feel you should have the following tables with their primary and foreign keys: Customers with primary key CustomerID Orders with primary key OrderID and foreign keys, CustomerID and ItemNo Invoices with primary key InvoiceNo and foreign keys OrderID Items with primary key ItemNo Receipts with primary key ReceiptNo and foreign keys InvoiceNo and optionally CustomerID (it may not be necessary to have CustomerID field in this table because this table is related to Customers table through Invoices and Orders tables). These 5 basic tables can get you started. From your spreadsheet you should know which fields to add to each table. Create relationships of these tables based on primary and foreign keys. It is a good practice to enforce referential integrity at this point. Thereafter you can build your forms, queries, reports, etc but first the table structure should be solid. Once you have these properly set up let the group members know so that they can guide along the way. HTH Liveson ----- Original Message ----- From: "Rhonda Jenkins" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Monday, 10 April, 2006 16:18 Subject: RE: [ms_access] questions I run a small internet retail store. Thus far I have been keeping my records (orders, inventory, invoices) in separate Excel spreadsheets. I want to start keeping my records in Access so that everything can be connected and I won't have to open several spreadsheets and enter the same information repeatedly. I just don't know where to start. I have found templates for customer order databases, inventory databases, etc. Wouldn't all of this need to be in one database to do what I want to do? In a nutshell, I want to be able to enter a customers name and address, the items they ordered, method of payment, etc. one time and have it spit out an invoice, and update my inventory records, update my expense sheet for shipping costs, etc. Is this possible? Where do I start? ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Rhonda Sugar Bearz ~ Fine Quality Children's Gifts & Clothing www.sugarbearz.com [Non-text portions of this message have been removed] Yahoo! Groups Links _____ YAHOO! GROUPS LINKS * Visit your group "ms_access <http://groups.yahoo.com/group/ms_access> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . _____ [Non-text portions of this message have been removed] Yahoo! Groups Links _____ YAHOO! GROUPS LINKS * Visit your group "ms_access <http://groups.yahoo.com/group/ms_access> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . _____ [Non-text portions of this message have been removed] Yahoo! Groups Links SPONSORED LINKS Microsoft <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Microsoft+ access+database&w2=Database+development+software&w3=Database+management+soft ware&w4=Database+software&w5=Inventory+database+software&w6=Membership+datab ase+software&c=6&s=190&.sig=hc7Za8QzEj1t7F8GW0yzhA> access database Database <http://groups.yahoo.com/gads?t=ms&k=Database+development+software&w1=Micros oft+access+database&w2=Database+development+software&w3=Database+management+ software&w4=Database+software&w5=Inventory+database+software&w6=Membership+d atabase+software&c=6&s=190&.sig=i5H8GCn7HzmglwpvhJg19w> development software Database <http://groups.yahoo.com/gads?t=ms&k=Database+management+software&w1=Microso ft+access+database&w2=Database+development+software&w3=Database+management+s oftware&w4=Database+software&w5=Inventory+database+software&w6=Membership+da tabase+software&c=6&s=190&.sig=kzlRmjQuUTEFLTjkEd6-kg> management software Database <http://groups.yahoo.com/gads?t=ms&k=Database+software&w1=Microsoft+access+d atabase&w2=Database+development+software&w3=Database+management+software&w4= Database+software&w5=Inventory+database+software&w6=Membership+database+soft ware&c=6&s=190&.sig=CoCmr_EubT5Xd83NiQjcpg> software Inventory <http://groups.yahoo.com/gads?t=ms&k=Inventory+database+software&w1=Microsof t+access+database&w2=Database+development+software&w3=Database+management+so ftware&w4=Database+software&w5=Inventory+database+software&w6=Membership+dat abase+software&c=6&s=190&.sig=DHnS8Uu1ImyGVg1J4k0BMw> database software Membership <http://groups.yahoo.com/gads?t=ms&k=Membership+database+software&w1=Microso ft+access+database&w2=Database+development+software&w3=Database+management+s oftware&w4=Database+software&w5=Inventory+database+software&w6=Membership+da tabase+software&c=6&s=190&.sig=uEGHlFpqbscHu8mCHe7tzg> database software _____ YAHOO! GROUPS LINKS * Visit your group "ms_access <http://groups.yahoo.com/group/ms_access> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . _____ [Non-text portions of this message have been removed] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
