Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships (2003)

    Hi,

    I'm in the process (still!) of converting an unwieldy Excel workbook into an Access database, and I've hit a problem and could do with some advice. Please remember that I've never built a database before, so while I think it's mostly been built OK, it could need work!

    What I have is bus service data coming in from a number of different local authorities. I've put this into a table called Services, which lists the owner of the service (on of a list stored in another table and linked, although I think that's irrelevant for this question) and the service code. Often, where a service goes from one authority area into another, I get duplications because both authorities carry the data, and I need to suppress one version. In Excel, I had one line for the service, and then different columns for each provider. I then annotated the service code in the appropriate columns by enclosing them in square brackets to show which was suppressed. That was visual, and worked very well. I'm having trouble, though, creating similar relationships in Access.

    To start with, I put a "yes/no" field in the Services table for "suppress?". That's OK as far as it goes, but it doesn't tell me what it has been suppressed in favour of, which I also need to know. I then put a field in the services table to list that, but while I can populate it manually, I can't make it a relationship back into the same table which would be the ideal, I think. Next I tried creating a Suppression table, which contained the ID of the service to be suppressed, and the ID of the service that is has been suppressed in favour of. However, that requires both fields to link to the ID field of the Services table, and when I tried that I got Services and Services_1, which appears to be a separate copy of the Services table. Or is it? I wasn't entirely clear whether it was a real copy, or a pretend copy just for the purposes of making relationships.

    Help!

    If it helps, what I need from this are two main outputs. In one instance, I need a service by service listing which just says "suppressed, yes or no?". Obviously, for that purpose the single yes/no field is sufficient. However, I also need to be able to create reports to send on to my clients to show what is suppressed, and where something is suppressed, what it has been suppressed in favour of.

    I can post the database as it currently stands if that would help, but I didn't think it was necessary at this stage.

    All help greatly appreciated! Thanks very much

    Regards,

    Stuart

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships (2003)

    The ideal situation would be not to have duplicate records at all, but a three-table setup:
    tblAuthorities lists the authorities
    tblServices lists the services
    tblServiceAuthorities lists which services fall under which authorities. Each record in this table is a unique combination of a service and an authority. See screenshot below.
    In this setup there is no need to suppress anything.

    BTW Services_1 is a "pretend copy" for the purpose of creating a relationship only, it's not a real copy of the table.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2003)

    OK, I can see what you've done but I can't see how I can immediately apply it to what I've got, because I need to show all services that I am supplied.

    For instance, in Essex data I might have two services, Stansted Transit #1 [ST1] and Trustline #1 [TL1], while in Hertfordshire's data I have Stansted Transit #1 [STR1] and Stevenange Link #1 [SL1] . I can't change the supply, so I need to list all four services but identify that the Stansted Transit #1 is supplied by Essex and not Herts. Essex and Herts go into tblAuthorities, but where do the services go? I assume that they go into tblServices, but does that mean that the join in tblAuthoritiesServices pulls in every service, or only those that aren't being suppressed? If the latter, how do I identify that ST1 and STR1 are one and the same, but that STR1 is suppressed?

    Thanks

    Stuart

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships (2003)

    If you need to keep the duplicates in the Services table, I would use a "SameAs" field instead of a Suppressed field. If the ID field is text, SameAs should be text too (with the same field size) and if ID is a number, SameAs should be a number too.
    In the Hertfordshire Stansted Transit record, enter the ID of the Essex Stansted Transit record in the SameAs field. This denotes that the Hertforsdshire record is suppressed. Leave the SameAs field blank in the Essex Stansted Transit record. This indicates that this is the primary, non-suppressed entry.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2003)

    Thanks - that all makes sense. I was hoping that I could make a relationship so that I could easily generate a report, but I guess it's no harder to do a report with a manual entry.

    Would you possibly be able to spend a short while to have a look at the attached image, and tell me if the structure looks sensible or if there is anything better I should do? As I said, this is my first database and it's reasonably complicated (for me!)

    You can see the Local Authority table, and also the Services table that we've been talking about. Each service also has a mode (e.g. bus or coach) which are stored in a Mode table, and some individual services can be put onto the same printed timetable (e.g. 1 and 1A) so there is a Composites table to manage this. The Composite ID in this case would repeat for each service to be linked, hence I need another ID to be the primary key, I think?

    The remaining tables link the authorities to a CIF (data source - service data for multiple authorities may be in a single data supply) and the operator codes that are in those individual data sources to the operator codes that we use in the combined data set, called DIVA.

    I understand if you haven't got the time, but if you have then it would be great. THanks.

    Stuart

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships (2003)

    I don't understand why the Local Authorities table is linked to CIF Providers on another field than the primary key. Normally, you use the primary key for this (as in the link to Services).

    If you want to print some timetables together, I'd use a field similar to SameAs, to indicate that the timetable for service A is to be printed as part of the timetable of service B.

    I hope you don't mind my saying so, but aren't you reinventing the wheel? I'm sure this kind of problem has been solved before, by public transport companies and/or by local authorities. It might pay to investigate if there is ready-made or easily customisable existing software.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2003)

    Hans,

    The reason for linking CIF Providers to Local Authorities is that the local authority table lists the local authority (the primary key) and then lists the CIF in which the data for that local authority resides. In many cases, these are the one and same (e.g. Herts data is supplied by Herts), but in other cases there are multiple authorities in one CIF - for example, Essex provides data both for Essex and Southend. The CIFs are listed in the CIF table (with CIF as the primary key), the authorities in the authority table (with Authority as the primary key) and then there is a separate field in the authority table to show what CIF they are part of. I had understood that I should then create a one to many relationship from CIF Providers to Local Authorities, since more than one authority would have the same CIF provider. Is this wrong? Perhaps the diagram is confusing, but I can't seem to get the 1 and infinity symbols on the links the same way that you and Access help seem to have!

    As for reinventing the wheel, no. It's not a timetabling system, but rather a management tool to help us manage twelve or more separate timetable sources coming into a different system provided by a contractor. It started out doing simple jobs in Excel and using VBA macros, but it's grown over time to the point where it really, really needs to be a database!

    Thanks once again.

    Stuart

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships (2003)

    The "one" and "infinity" symbols indicate that "Enforce Referential Integrity" has been turned on for the relationship. If you double click a join line in the Relationships window, you can tick the appropriate check box. If you get an error message, either the fields are not set up corrrectly, or there are existing data that violate referential integrity, for example records on the "many" side of the relationship with foreign key values that do not correspond to a primary key value on the "one" side.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •