Re: By Year/Quarter

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Fri, 24 Nov 2006 08:20:02 -0800, chickalina
<chickalina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

thinking along the lines of Excel (which is how the numbers are tracked right
now). You would have the years listed on the left and the Quarters going
across the top.

Should I have 6 tables?

VendorID
Year ID
Quarter1ID
Quarter2ID
Quarter3ID
Quarter4ID

Every vendor with nothing would need to have a blank row for current year in
the subform when you open it up so information can be entered.

Excel is a spreadsheet, a good one.
Access is a relational database.

THEY ARE DIFFERENT.

Applying spreadsheet logic to a relational database will, guaranteed,
get you a faulty design - just as it is doing in your case!

In a database, "fields are expensive, records are cheap". You should
NEVER store data (quarters, say) in fieldnames.

What I would suggest for your database is two tables:

Vendors
VendorID
VendorName
<other info about the vendor as an entity>

VendorSales
VendorID <link to Vendors>
SaleDate <e.g. #6/30/2006# for the 2nd quarter of 2006>
SaleAmount <the sales during that quarter, NOT CUMULATIVE>

That is - store the basic INFORMATION (the amount of sales in each
quarter). You can easily create a Totals Query or a Report which will
group by Vendor, sort by SaleDate, and display years and quarters with
the cumulative amount up to and including that date.


John W. Vinson[MVP]
.

Relevant Pages

  • Re: By Year/Quarter
    ... I want to make this as easy as possible for the data entry person to put the ... the subform should only show current year plus the next 4. ... Applying spreadsheet logic to a relational database will, guaranteed, ... <other info about the vendor as an entity> ...
    (microsoft.public.access.tablesdbdesign)
  • Re: The wisdom of the object mentors (Was: Searching OO Associations with RDBMS Persistence Models)
    ... Relational database systems can be used in situations where there is a large amount of complex data to be managed, searched, queried and reported. ... Clearly some companies have made committments to one vendor or the other, in which case the decision is moot; but in that case the decision is non-technical. ... from the largest MIS Enterprise system to the lowliest control system for a microwave oven. ... The only software that does not benefit from OO lanaguages is software that is so small as to be a single subroutine or small suite of functions that can be understood by a single person in an hour or two. ...
    (comp.object)
  • Re: The wisdom of the object mentors (Was: Searching OO Associations with RDBMS Persistence Models)
    ... Why and when do you use a Relational Database? ... Why and when do you use an OO language? ... means that most of the special features provided by the vendor should ...
    (comp.object)
  • Re: JOIN not returning desired value, please help!
    ... > it into a situation where vendor is included. ... > totals table to consider vendor. ... but my goal is to be able to show the vendor's sales compared ... > I'm writing a query that summarizes some data and compares it to other ...
    (microsoft.public.sqlserver.programming)
  • Find all and Sum
    ... I have created a worksheet of new/lost and sales moved from one vendor to ... Date Customer Name Type Vendor Premium Commission SalesPerson ... Date Customer Name Type Reason Vendor Premium Commission SalesPerson ...
    (microsoft.public.excel)