Results 1 to 12 of 12
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    One-to-one Recursive Relationship

    I've hit a confusing subject and I'm wondering if someone can help me out here.



    Background:

    I have a [Jobs] and [PayApplications] table. A "Job" has many [PayApplications]. Pay applications are submitted on a monthly basis for each job. A job might last up to two years so have couple dozen pay applications. A job might not have a pay application on some months. I started out designing this db with the [PayApplications] table having an [AppNumber] field that worked to sort the pay applications. Each "PayApplication" would store currency values for work completed that month, and that's what we bill for. (Actually it's a collection of a lot dollar amounts, I'm simplifying) So for example billing in January could be $50k, February $30k, March $60k, total billing to date $140k.

    For my purposes a report on A [PayApplication] needs to display summations for ALL previous pay applications. (See standard AIA Form G703 for an example report that I will need to be generating, maybe this will clarify some things)

    Given the data model as described my queries were having to collect and summarize a relatively large amount of data. So I got to thinking, for each [PayApplication] would it be better to store work completed TO DATE? So given the example above we would have January $50k, Feb $80k, March $140k. This way when I need to display the summations of ALL previous [PayApplications] I only have to look up the ONE previous pay application instead of having to do summation queries on ALL the previous pay applications. Work completed this month is simply = (completed to date this app) - (completed to date previous app)


    The Problem:

    I thought that a recursive relationship between pay apps would be appropriate here instead of having an [AppNumber] field. I'm having trouble deciding on how to most appropriately apply the relationships and integrity. So each PayApp succeeds another PayApp UNLESS it's the first PayApp for the job. On the other hand each PayApp precedes another UNLESS it's the latest pay app for a job. Either would be a one-to-one relationship.

    For the recursive relationship I would need a PreviousPayAppID OR NextPayAppID field. These fields would need to be indexed w/ NO duplicates EXCEPT I need to somehow allow a null value for the first and latest PayApps??? But to allow for null it seems I need to set these to indexed w/ duplicates allowed. This opens up the possibility of PayApps having multiple previous or following apps. To enforce any kind of data integrity my fields can't allow nulls as far as I can tell.

    Questions:

    1. Am I over complicating everything and should I forget about recursion for this?
    2. Should my [PayApps] table maintain a PreviousPayAppID field, a NextPayAppID field, or both? I figure Data Macros will come in handy here.
    3. If the answer to #2 is not both, how do I query the first or latest PayApp in the series?


    Here is a simplified model:

    Click image for larger version. 

Name:	3.png 
Views:	37 
Size:	27.4 KB 
ID:	29868

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access doesn't handle recursion within queries - you need to use vba. However I cannot see why you would want to use recursion anyway. You have a previous/next based on a month value.

    how do I query the first or latest PayApp in the series?
    by reference to the month/date. You can easily determine the first based on the minimum date and the latest the maximum date. The series using a group by query

    Based on your description, I don't see the need for the payapps table at all.

    If you want to know the previous value you can use a subquery and reference the same table but for the previous month or the last month before. For example

    The previous month

    Code:
    SELECT *
    FROM PayAppValues C INNER JOIN PayAppValues P ON C.PayAppID=P.PayAppID
    WHERE P.CompletedToDate=dateadd("m",-1,C.CompletedToDate) C.CompletedToDate=(SELECT Max(CompletedToDate) FROM PayAppValues WHERE PayAppID=C.PayAppID)

    or the last month

    Code:
    SELECT *
    FROM PayAppValues C INNER JOIN PayAppValues P ON C.PayAppID=P.PayAppID
    WHERE P.CompletedToDate=(SELECT Max(CompletedToDate) FROM PayAppValues WHERE PayAppID=C.PayAppID AND CompletedToDate<C.CompletedToDate) AND C.CompletedToDate=(SELECT Max(CompletedToDate) FROM PayAppValues WHERE PayAppID=C.PayAppID)
    Note the use of Aliases for the same table to differentiate between the different 'versions'. You can use anything you like for the Alias, but I've used C (for current) and P (for Previous)

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    Based on your description, I don't see the need for the payapps table at all.
    *MIND BLOWN* ... I need to think on this for a while. Thank you.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    @Ajax and anyone else,
    The problem that I'm seeing is that the PayApp_Values table could wind up being 100k+ rows a single "Schedule of Values" report might pull 200 records from this table. If I don't have the PayApps table, and the PayAppID foreign key to sort/search by, how would I quickly query the data I need from that large table?

    For arguments sake let's say I do not have the PayApps table. For the Schedule of Values report I have to pull together all the JobItems and ItemValues for a job, and latest TWO PayApp_Values (the big table) for each of those ItemValue. Given the table structure above what would be the most efficient/quickest way to collect the data?

    I'm working on some code to generate a couple years worth of random data to see what the performance will be like.
    Last edited by kd2017; 08-15-2017 at 03:36 PM.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    100k records is not that many - I have tables with 3-4million records. For good performance you need to ensure all fields which are used for criteria, sorting or joining are indexed and your tables properly normalised (your tables are not as you have shown them). Also avoid using lookup or multivalue fields in your table design. Aside from the other issues around data visibility, they can affect performance. Don't lose the use of an index by using LIKE *sometext - the initial * negates the use of an index.

    It can't always be done but where possible avoid subqueries per the example I provided - and certainly avoid domain functions. If you can do without using the query builder there are more flexible joins you can use rather than use a criteria. for example the first example I provided could be written as (aircode and not tested)

    Code:
    SELECT *
    FROM (PayAppValues C INNER JOIN PayAppValues P ON C.PayAppID=P.PayAppID AND P.CompletedToDate=dateadd("m",-1,C.CompletedToDate)) INNER JOIN (SELECT PayAppID, Max(CompletedToDate) MaxDate FROM PayAppValues GROUP BY PayAppID) Q ON P.PayAppID=Q.PayAppID AND C.CompletedToDate=Q.MaxDate
    Perhaps you still need the payapps table - it would just be joined to the values table. but you only need the PayAppID field so what would be the point unless it is going to hold other data such as a payment date? - but you appear to be holding that in the values table (so your data is not normalised). If you put the completed date in the payapps table, you don't need the values table and would join the itemvalue table to the payapp table instead.

    I don't know your business or what is right for you, only you can decide. Maybe the table structure is right, maybe not.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    100k records is not that many
    As I'm learning! I over estimated the number of rows I would be needing. I've populated the the tables with a few years worth of "jobs" and random data, the App_Values is only up to 70k records. So far Access seems to be handling all my summation queries more than quickly enough. And beyond this point old job data could start to be archived, so not much to worry about.

    (your tables are not as you have shown them).
    Can you show me where I screwed up the normalization? (I'm still working on the basics)

    The tables as shown were thrown together as a simplified version of the real deal for the purpose of the original question about recursive references. There are no Date fields in the tables presented above, CompletedToDate field is a currency field that holds how much I can bill for an item on that particular PayApplication. For example an item has a total value of $100k, in December I have "completed to date" $20k worth of that item to bill for, in January if have completed another $5k so in total $25k to date.

    Perhaps you still need the payapps table - it would just be joined to the values table. but you only need the PayAppID field so what would be the point unless it is going to hold other data such as a payment date?
    PayApps get ApplicationDate, PeriodTo date, DateDue, comments, status fields, etc. Payment data gets its own set of tables.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can you show me where I screwed up the normalization? (I'm still working on the basics)
    already explained - you are storing the completedtodate multiple times - unless you only pay for one item in any given day - in which case it should be in the itemvalues table. And if you do pay multiple items on any given day, it should be in the payapps table - which would be linked to the itemsvalues table. Either way, you don't need the payappvalue table.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    already explained - you are storing the completedtodate multiple times - unless you only pay for one item in any given day - in which case it should be in the itemvalues table. And if you do pay multiple items on any given day, it should be in the payapps table - which would be linked to the itemsvalues table. Either way, you don't need the payappvalue table.
    I'm still not quite following. A single PayApp will bill for many ItemValues, a single ItemValue will be billed for over many PayApps. The PayApp_Values table is my cross reference table for the many-to-many relationship, each CompletedToDate could be a different value.

    I'll elaborate on my process and can you tell me if I'm still not doing it correctly?

    A job is a commercial construction project. A job has many items (ie stairs, elevators, etc.) and each item's $ value can be composed of several smaller $ values (ie labor, materials, etc). We bill once a month to the customer for progress payments, "pay applications". An item can take several months to complete, so we can only bill for a portion of that item's value each month (ie we only installed half the stairs in Jan, we can only invoice for half the $ value). But there will be many different items worked on each month, so a pay application needs to be able to bill for progress on all the different item values. So the PayApp_Values table would say I'm billing for $x of y item's value on month z pay application.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If that is the case cost of stairs $1000 (in the item values table), bill 50% this month, balance next month type of thing, then you do need both payapp and payapp values tables - but the value billed (50% and balance) would be in the value table and the completedtodate field in the payapp table (together with an invoice number or similar)- and you don't need the join between Jobs and payapps - you navigate the other way.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    If that is the case cost of stairs $1000 (in the item values table), bill 50% this month, balance next month type of thing, then you do need both payapp and payapp values tables - but the value billed (50% and balance) would be in the value table and the completedtodate field in the payapp table (together with an invoice number or similar)- and you don't need the join between Jobs and payapps - you navigate the other way.
    I'm having trouble visualizing what your saying and how it would work for me, if it's not too much trouble could you do a quick sketch of the table structure?

    One thing I'd like to point out is that I need to maintain a completion history for each item, or at the very least I need to know the previous completion for each individual item.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've used your table names (or close approximations) and the same for the field names so not sure what to add. And said which relationship to remove.

    Your later description does not really tie back to your earlier description and I don't know your business. So I may end up sending you down the wrong path.

    The basic objective of normalisation is to not repeat data, or to put it another way, to store it once. Get a load of post it notes, write one bit of data on each one (not family and primary keys, that comes later), then on a wall, start grouping them together in such a way you do not have to repeat it. Whilst putting all this together, think about what you are going to do with the data - how are you going to input it? manually? from excel or some other document? how are you going to extract it for invoices/reports etc. - Do you need document headers? what do you need to know about suppliers? employees? customers? public holiday dates? Do you need to email data or print and post? whatever.

    When done, each group will represent a table so you can now add your family and primary keys - at this point you may need to also create some join tables for many to many relationships.

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I haven't deviated from my prior descriptions. I think we are trying to achieve different things. There is no redundant information in the payapp_value table.

    We've steered off the original topic quite a bit. Since this wasn't part of the original topic I've ended piece mealing information, I apologise if that's caused confusion.

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

Similar Threads

  1. Recursive total
    By Romulus in forum Reports
    Replies: 1
    Last Post: 10-23-2015, 03:39 AM
  2. Is a recursive query possible?
    By nigelbloomy in forum Queries
    Replies: 3
    Last Post: 09-10-2015, 04:04 PM
  3. Recursive data
    By jvrsoto in forum Access
    Replies: 4
    Last Post: 12-20-2011, 10:56 AM
  4. Recursive Report!
    By Rawb in forum Reports
    Replies: 7
    Last Post: 01-26-2011, 04:00 PM
  5. Recursive Tables!
    By Rawb in forum Database Design
    Replies: 1
    Last Post: 01-26-2011, 02:46 PM

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