New to MS Access 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-1213911%22%20slang%3D%22en-US%22%3ENew%20to%20MS%20Access%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213911%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20help%20me%3F%26nbsp%3B%20I%20am%20new%20to%20Access%20and%20databases%20in%20general.%26nbsp%3B%20I%20am%20making%20what%20I%20thought%20was%20a%20simple%20customer%20invoicing%20database%20for%20a%20very%20small%20business.%26nbsp%3B%20I%20need%20to%20invoice%20for%20services%20that%20have%20different%20fees%20depending%20on%20the%20physical%20location%20they%20are%20provided.%26nbsp%3B%20Can%20anyone%20recommend%20how%20to%20structure%20the%20tables%20and%20relationships%3F%26nbsp%3B%20Much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1213911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1214131%22%20slang%3D%22en-US%22%3ERe%3A%20New%20to%20MS%20Access%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1214131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576192%22%20target%3D%22_blank%22%3E%40MSUser0930%3C%2FA%3E%26nbsp%3BWhat%20you%20describe%20is%20actually%20pretty%20complex.%20Financial%20oriented%20relational%20database%20applications%20tend%20to%20require%20good%20understanding%20of%20the%20accounting%20behind%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20some%20initial%20thoughts.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E1)%20Quickbooks%3F%20Would%20you%20rather%20invest%20time%20and%20resources%20into%20re-inventing%20the%20tool%2C%20or%20would%20you%20be%20able%20to%20invest%20in%20a%20tool%20that%20already%20does%20pretty%20much%20everything%20most%20people%20need%20in%20this%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20You%20need%20main%20or%20primary%20tables%20for%20customers%2C%20products%20sold%2C%20or%20services%20provided%20(whichever%20your%20organization%20provides%2C%20or%20both)%2C%20transactions%20involving%20customers%20and%20your%20products%20or%20services%2C%20details%20of%20those%20transactions%2C%20invoices%20for%20transactions%2C%20invoice%20details%20for%20those%20invoices%2C%20and%20invoice%20payments.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20In%20addition%2C%20you%20probably%20need%20a%20table%20that%20relates%20customers%20to%20their%20respective%20fee%20structure.%20This%20would%20be%20a%20table%20usually%20called%20a%20junction%20table%2C%20in%20which%20you%20record%20the%20foreign%20key%20for%20the%20customer%2C%20the%20foreign%20key%20for%20the%20product%20or%20service%2C%20the%20effective%20date%20and%20the%20fee%20as%20of%20that%20date.%20This%20allows%20you%20to%20change%20fees%20from%20time%20to%20time%20without%20wrecking%20older%20invoices.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20there%20will%20be%20additional%20tables%2C%20but%20without%20detailed%20survey%20and%20analysis%20of%20your%20organization%2C%20it%20might%20be%20hard%20to%20suss%20that%20out.%3C%2FP%3E%3CP%3EMake%20sure%20you%20assign%20proper%20Primary%20Keys%20to%20all%20tables%2C%20and%20that%20you%20properly%20enforce%20Referential%20Integrity%20between%20related%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20your%20tables%20are%20set%20up%2C%20you%20can%20turn%20attention%20to%20the%20interface%20objects--forms%20and%20reports--needed%20to%20use%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Can anyone help me?  I am new to Access and databases in general.  I am making what I thought was a simple customer invoicing database for a very small business.  I need to invoice for services that have different fees depending on the physical location they are provided.  Can anyone recommend how to structure the tables and relationships?  Much appreciated!

1 Reply
Highlighted

@MSUser0930 What you describe is actually pretty complex. Financial oriented relational database applications tend to require good understanding of the accounting behind them.

 

Here are some initial thoughts. 


1) Quickbooks? Would you rather invest time and resources into re-inventing the tool, or would you be able to invest in a tool that already does pretty much everything most people need in this area.

 

2) You need main or primary tables for customers, products sold, or services provided (whichever your organization provides, or both), transactions involving customers and your products or services, details of those transactions, invoices for transactions, invoice details for those invoices, and invoice payments.

 

3) In addition, you probably need a table that relates customers to their respective fee structure. This would be a table usually called a junction table, in which you record the foreign key for the customer, the foreign key for the product or service, the effective date and the fee as of that date. This allows you to change fees from time to time without wrecking older invoices.

 

I suspect there will be additional tables, but without detailed survey and analysis of your organization, it might be hard to suss that out.

Make sure you assign proper Primary Keys to all tables, and that you properly enforce Referential Integrity between related tables.

 

Once your tables are set up, you can turn attention to the interface objects--forms and reports--needed to use them.