Re: By Year/Quarter

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

Thanks for the reply, but I don’t think I’m clear:

I want to make this as easy as possible for the data entry person to put the
numbers in… I just want them to have 5 fields to maintain… Q1, Q2, Q3, Q4,
and Reason as follows in a subform:

Q1 Q2 Q3 Q4 Year End Reason (combo box)
2006 20 20 20 20 80 Decline
2007 10 10 10 10 40 Decline

The form/report would calculate year end.

Subsequently, the subform should only show current year plus the next 4.
They are 5 year plans.



"John Vinson" wrote:

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: Open new record subform for data entry
    ... The other thing you may need to check is that AVInvoicedataentrysubf is the name of the subform control on the main form, as against the name of the form that is being used for the suform - these are not necessarily the same. ... I want to enter in a PO# and have the vendor assigned to that PO Populate the Vendor Field in my Invoice Form so confirm for the data entry person that this Vendor does in fact belong to this PO. ...
    (microsoft.public.access.forms)
  • Re: Continuous Subform / Form - Orphan records
    ... I have to make the data entry as user friendly as possible. ... With the subform I ... > I would again suggest you look into Relational Database Theory & Database ... > Relational Database Design Principles and Database Normalisation and proceed ...
    (microsoft.public.access.forms)
  • Validation to assure there is a subform record
    ... the PO table and a subform based on a LineItems table. ... subform control to add a LineItem record. ... The PO system is tied in with a Vendor database. ... of the vendor information appears in a subform. ...
    (microsoft.public.access.formscoding)
  • Re: BruceM or Douglas J Steele
    ... see all of the categories at once for a particular vendor. ... if you use the subform to select three categories for that vendor, ... You link vendors and categories in the junction ... >> That combo box's row source will be based on tblCategory. ...
    (microsoft.public.access.gettingstarted)
  • Re: display prior and add new records
    ... My subform updates a table set up to track commodity increases. ... SecCommID and once the user selects a Vendor from the list box these fields ... estimated increase %,and date of increase, and reason. ... CommodityID of steel will go up 3%, on the same date, for the same reason) ...
    (microsoft.public.access.forms)