©GuidedComputerTutorials2008 12-1

Chapter

12Setting up an Invoice SystemTofullyunderstandthevalueofrelationaldatabasesyouneedtocreateadetailedsystem.Inthischapteryouwillsetupaninvoicingsystemforacomputermailordercompany,PCDirect,whichsellscomputerperipheralsthroughthemail.Theinvoicesystemwillthenbecompletedinthefollowingthreechapters.Therearethreemainsectionstothesystem,customers,productsandthesalesinvoice.

Inthecreationofanydatabasesystemyoushoulddosomecarefulplanning.Ingeneraltherearefourstepsthatyoushouldundertake.

1 Decidehowmanytablesyouthinkyoumightneed.

2 Decidehowthetableswillberelatedtooneanother.

3 Listthefieldsineachtabletryingtoavoidhavingthesamefieldinmorethanonetable.DecidewhichfieldswillbethePRIMARYKEYandFOREIGNKEYfieldstolinkthetables.

4 Decidewhatformsand/orreports(orprintouts)arerequired.

InthecaseofPCDirectaninitialTABLERELATIONSHIPdiagrammightbe:

TheCUSTOMERStoINVOICEsectionofthisstructureformsaONETOMANYrelationship.TheonecustomercanhaveMANYinvoicessenttothemovertime,buttherewillalwaysbeONEcustomeroneachinvoice.SothecustomertableistheONEsideoftherelationshipandtheINVOICEistheMANYside.Wecansetarelationshiptolinkthesetwotables.

ThereisaproblemwiththelinkbetweentheINVOICEandthePRODUCTStables.Theoneinvoicecancontainmanyproductsandtheoneproductcanbeincludedinmanyinvoices.ArelationaldatabasecannotcaterforaMANYTOMANYrelationshipasyoucannotsetmultiplePRIMARYorFOREIGNKEYfieldsintheonerelationship.

Afurtherproblemexists,oneinvoicemightcontainasaleof5ofaparticularitem,thenextinvoicemightcontainasaleof2ofthesameitem.Thecompanyneedsawayofaddingthesesalessothatitknowshowmanyitemsithassold.SothisinitialTABLERELATIONSHIPwillneedmodification.

TheeasiestsolutiontotheseproblemsistoaddatablebetweenINVOICEandPRODUCTS.Thistablecanstoreeveryitemsoldbythecompanyasasinglerecordallowingthecompanytokeeptrackofeveryitemsold.ThenewtablecanalsoprovidedatatotheINVOICEtable.

Customers Invoice Products

One to Many Many to Many

©GuidedComputerTutorials200812-2

LearningMicrosoftAccess2007

SoarevisedTABLERELATIONSHIPdiagrambecomes:

TheONEinvoicecanhaveMANYitemssoldwithinit.TheONEproductcanbesoldMANYtimes.ByaddingtheLINEITEMStabletothesystemaseriesofONETOMANYrelationshipsarecreatedandarelationaldatabasesystemcanaccommodatethese.

Thenextstepistodecideonthefieldstobeplacedineachtable.ThefollowingTABLESTRUCTUREdiagramshowsonepossibility.Remember,wedonotwanttostoredatamorethanonce(exceptforPRIMARYKEYorFOREIGNKEYfields).

ThePRIMARYKEYandFOREIGNKEYfieldsneedtobeconsidered.InthepreviousdiagramyouwouldhaveseethatCUSTOMERIDisusedtolinktheCUSTOMERSandINVOICEtables,INVOICENOisusedtolinktheINVOICEandLINEITEMStablesandPRODUCTIDisusedtolinktheLINEITEMSandPRODUCTStable.TheINVOICEtableisnotdirectlylinkedtothePRODUCTStable,itwillobtainvaluesfromthePRODUCTStableviatheLINEITEMStable.

Thefinalconsiderationintheplanningiswhatreportswillberequired.Thefollowingdiagramshowssomeofthereportsthatcouldbemadefromthevarioustables.Wewillcreatesomeofthesereportsinthenextchapter.

Customers Invoice Products

One to Many One to Many Link

Items Sold

One to Many

Customers Invoice Line Items ProductsCustomerID InvoiceNo InvoiceNo ProductIDTitle CustomerID ProductID ProductNameFirstName DateSold QtySold CostPriceLastName RetailPriceOrganisation InstockStreet ReorderNumberSuburb LocationState

PostcodePhone

Fax

Customers Invoice Items Sold ProductsCustomerList Invoice ItemLists ProductListMailingLabels MonthlyTotals

ItemTotals

1

M

1M

M

1

©GuidedComputerTutorials2008 12-3

12SettingupanInvoiceSystem

Loading the Sample File

1 LoadMicrosoftAccessandclickontheOPENiconintheOFFICEBUTTON,orclosethecurrentfileandclickontheOPENiconintheOFFICEBUTTON.

2 AccesstheCHAPTER12folderoftheACCESS2007SUPPORTFILESandopentheCHAPTER12fileasanOPENREAD-ONLYfile.

3 ClickontheOFFICEBUTTONagain,highlightSAVEASandselectACCESS2007DATABASE.

4 AccessyourACCESSSTORAGEfolderandsavethefileasCHAPTER121.

A Looking at the Tables

Thedatabasehas3tablesatthemoment,CUSTOMERDETAILSwhichstoresdataaboutthecompany’scustomers,LINEITEMSwhichstoresdetailsofeachproductsoldandPRODUCTDETAILSwhichstoresalistoftheproductsthatthecompanymarkets.

2 ClosetheCUSTOMERDETAILStable,

1 OpentheCUSTOMERDETAILStable,settheviewtoDESIGNVIEWandnoticethattheCUSTOMERIDfieldhasbeensettothePRIMARYKEYfield.Theotherfieldsstoregeneralinformationaboutthecompany’scustomers.

3 OpentheLINEITEMStable,settheviewtoDESIGNVIEWandnoticethatthereisnoPRIMARYKEYfield.ItjuststoresbasicdetailsPRODUCTIDandQTYsoldthattheinvoicewillneed.

©GuidedComputerTutorials200812-4

LearningMicrosoftAccess2007

NOTE: The LINE ITEMS table will record each line of the invoice. It will have two main purposes:

(A) to list each product sold as a separate record so that the company can calculate monthly sales and carry out stock checks.

(B) to provide product data to the invoice. If you look at the diagram at the centre of page 12-2 you will see that the PRODUCTS table is not directly connected to the INVOICE table so some data, such as Product Name and Retail Price, will need to be linked to the LINE ITEMS table via a relationship to the PRODUCTS table so that the INVOICE can display them.

4 ClosetheLINEITEMStable.

6 ClosethePRODUCTDETAILStable.

B Looking at the Forms

Twoformshavebeenpreparedforyou.TheCUSTOMERSUB-FORMwhichdisplaysthedetailsfromtheCUSTOMERDETAILStableandtheINVOICEMAINFORMwhichyouwillneedtocomplete.

5 OpenthePRODUCTDETAILStable,settheviewtoDESIGNVIEWandnoticethatPRODUCTIDhasbeensettheaPRIMARYKEYfield.ItwillprovidedatatotheLINEITEMStableviathisfield.

1 OpentheCUSTOMERSUB-FORMformandnoticethatfieldshavebeenarrangedinanormaladdressformat.

©GuidedComputerTutorials2008 12-5

12SettingupanInvoiceSystem

2 ClosetheCUSTOMERSUB-FORMform.

4 ClosetheINVOICEMAINFORMform.

Creating the Invoice Table

Aninvoicetableisrequiredtostoredetailseverytimeaninvoiceissent.ThistablesimplyrecordstheINVOICENUMBER,theCUSTOMERIDandtheDATEofthepurchase.Thelinksthatwemaketotheothertableswilldisplayotherdetailssuchasproductnameandretailprice.

3 OpentheINVOICEMAINFORMform.Alogohasbeenpreparedforyou.Yourtaskwillbetocompletetherestofthisformsothatafunctionalinvoiceiscreated.

1 OpentheCREATEtabintheRIBBONandselecttheTABLEDESIGNicon.

2 EntertheFIELDNAME:

InvoiceNo

SetisDATATYPEtoAUTONUMBERandentertheDESCRIPTION:

Provideauniquenumberforeachinvoice.

©GuidedComputerTutorials200812-6

LearningMicrosoftAccess2007

NOTE: The AUTONUMBER field type will tell the program to provide a new number every time an invoice is created so that no two invoices will ever have the same number.

NOTE: i The PRIMARY KEY will index the table so that it is listed in INVOICE NO order.

ii The format (00000) will set each number to five digits, for example, 00001, 00002, etc.

3 ClickonthePRIMARYKEYbuttoninthetoolbartosettheINVOICENofieldasthePRIMARYKEYfield.

4 IntheFIELDPROPERTIESpanesettheFORMATboxto00000.

5 ClickinthesecondFIELDNAMEcellandenterthefieldname:

CustomerID

6 SetDATATYPEtoTEXT,withaFIELDSIZEof10andaDESCRIPTION:

Identitycodeforeachcustomer