Results 1 to 5 of 5
  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86

    Tracking historical billings (no unique identifier) for records

    I am creating a functionality in my database that pulls a list of billings by project order # and sums all the billings within a certain date range to find the total billings within that range for that particular project.




    I have a report that pulls a list of billings for various projects. This report has no unique identifier for the billing, all it has is the following information: project, date of bill, amount of bill.

    so for example it could like this this:
    tblBillings
    project1 | 4/3/2019 | $100.00
    project1 | 4/3/2019 | $100.00
    project1 | 4/3/2019 | $100.00
    project1 | 4/3/2019 | $25.00
    project1 | 4/1/2019 | $50.00

    As you can see, there is no good standard of 'uniqueness' in this report. I will be, on a weekly basis, be updating this data by deleting all records in the tblBillings table and putting in a fresh batch because billing entries that already exist require no future updates (for any particular billing, the amount, date and project will never change - the data is static). New reports will have all the entries from previous reports + newly registered billings.

    My question is, for the feature I have in mind, what is the best method of implementation? Should I set up a one to many relationship between the 'project' and the associated billings in tblBillings with no integrity checks? Or should I keep the table severed completely? Is there a easier/better way to accomplish the same goal?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can add an autonum field anytime and boom, Unique ID.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This doesn't make sense to me.
    Quote Originally Posted by TerraEarth View Post
    I am creating a functionality in my database that pulls a list of billings by project order # and sums all the billings within a certain date range to find the total billings within that range for that particular project.
    OK, got that...

    Quote Originally Posted by TerraEarth View Post
    As you can see, there is no good standard of 'uniqueness' in this report.
    Why do you think you need a "good standard of uniqueness"? Isn't the project and date enough to get the data for your report?
    (Even though I have an Autonumber type field as the PK field)


    Quote Originally Posted by TerraEarth View Post
    I will be, on a weekly basis, be updating this data by deleting all records in the tblBillings table and putting in a fresh batch because billing entries that already exist require no future updates (for any particular billing, the amount, date and project will never change - the data is static). New reports will have all the entries from previous reports + newly registered billings.
    This I really don't understand. You are deleting records in the table weekly and adding new records, but expect to be able to include the deleted records in a future report??


    Maybe you could post an image of the relationship window?

    It sounds like you are deleting, then adding records to table "tblBillings" for a weekly report. Is there a different table that holds all of the billing records?
    Why aren't you using a query on a different (not "tblBillings") table to get the data for a project and date range instead of copying the records to a separate table, then viewing the report?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I am as confused as Steve.

    Easiest is to not delete records - apply filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by TerraEarth View Post
    As you can see, there is no good standard of 'uniqueness' in this report. I will be, on a weekly basis, be updating this data by deleting all records in the tblBillings table and putting in a fresh batch because billing entries that already exist require no future updates (for any particular billing, the amount, date and project will never change - the data is static). New reports will have all the entries from previous reports + newly registered billings.
    Either you are deleting entries for older billings in billings details table and preserving only entries for those billings in billings header table (with fields for project, date and total sum), or you are deleting all billing information. When 2nd case, then what you deleted is gone forever at moment you move to another record.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-08-2017, 08:56 AM
  2. Replies: 4
    Last Post: 07-16-2015, 04:49 PM
  3. Custom Unique Identifier
    By sstrode in forum Forms
    Replies: 2
    Last Post: 09-17-2014, 05:10 PM
  4. Adding a unique identifier to each record
    By Jessica240 in forum Queries
    Replies: 28
    Last Post: 07-15-2014, 01:42 PM
  5. Using Social# as unique identifier
    By NEHicks in forum Database Design
    Replies: 3
    Last Post: 05-27-2011, 09:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums