none
Access relationships RRS feed

  • Question

  • Hello,

    I really need to talk or chat with someone , reading forums is no help and I am sure that my problem has a very simple solution. Please help, this is driving me nuts.

    Saturday, May 23, 2020 6:19 AM

All replies

  • So, what is your problem?

    Imb.

    Saturday, May 23, 2020 7:43 AM
  • It's important to bear in mind that a relational database is a model of that part of the real world with which it is concerned.  Each real world entity type is modelled by a table, and the attributes of each entity type are represented by columns in the table.  Relationships are modelled in the case of a one-to-many, or more rarely a one-to-one relationship type, by referencing the primary key of one table by a foreign key in the referencing table.  In the case of a many-to-many relationship type, this is modelled by a further table which resolves the relationship type into two or more one-to-many relationship types.

    So, when seeking advice on relationships it's important to describe in some detail the real world situation which is being modelled.  The following for instance would be a description of a basic sales order database:

    "Each order can be received from a single customer.  Each customer can make one or more orders.  An order can be made for one or more products, and can be for one or more of each product."

    Notice that this makes no mention of tables or relationships per se, but the tables and relationships which are required to model this are implicit in the description:

    Customers---<Orders---<OrderDetails>----Products

    i.e. there is a one-to-many relationship type between customers and orders, and a many-to-many relationship type between orders and products which is modelled by the OrderDetails table which resolves the relationship type into two one-to-many relationship types.  In addition to the two foreign key columns, OrderDetails will include non-key columns such as Quantity and UnitPrice.

    So, if you can describe the real world situation which you are attempting to model in similar terms that gives us a starting point from which to advise you.  Normally this can be done by exchanges of replies here.  In some cases we might request that you post a redacted copy of your file to a location such as Microsoft's OneDrive, from where we can download it.  Establishing a one-to-one line of communication is more unusual, but not to be ruled out.



    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Saturday, May 23, 2020 12:04 PM Typo corrected.
    Saturday, May 23, 2020 11:56 AM
  • I am attempting to produce a report:

    A Service Ticket for a specific piece of equipment, with a Service Ticket Number and numerous Service Ticket Items.

    Example:

    Car 1 (Auto Number) Own Table

    Ticket #2 (Auto Number) Own Table

    Items (Auto Number) Own Table

    2 Oil Chnage

    3 Tire Rotation


    My result displays

    Car 1 

    Ticket #2 

    Items (Auto Number) Own Table

    2 Oil Chnage

    3 Tire Rotation

    4 Radiator Flush

    5 Brake Replacement

    ETC,,, Every Item that has ever been added to any Service Ticket?

    Saturday, May 23, 2020 1:29 PM
  • You start with tables to model the main entity types, in broad outline like this:

    Vehicles
    ….VehicleID  (PK)
    ….RegistrationNumber

    ServiceItems
    ….ServiceItemID  (PK)
    ….ServiceItem

    ServiceTickets
    ….ServiceTicketID  (PK)
    ….ServiceTicketDate
    ….VehicleID  (FK)

    To model the items on each ticket you have a table which models the many-to-many relationship type between ServiceItems and ServiceTickets:

    ServiceTicketItems
    ….ServiceTicketID  (FK)
    ….ServiceItemID  (FK)

    The primary key of ServiceTicketItems is a composite of the two foreign keys.

    Diagrammatically the model will look like this:

    Vehicles---<ServiceTickets----<ServiceTicketItems>----ServiceItems

    To return the items per service ticket  is then just a case of joining the tables as above, and return the relevant columns from each table in the query's result table.

    BTW, do not use spaces or special characters like the # symbol in object names, they can cause notational problems.  Either use CamelCase, as I've done above, or represent a space by an underscore character, e.g.  Service_Items.

    For data input use a service ticket form, in single form view, within which is embedded a service ticket items subform.  This is essentially the same as would be used for entering order details into a subform within an orders form, of which you'll find an example in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    In this little demo file the section on 'entering data via a form/subforms' incudes a simple orders form and subform.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, May 23, 2020 4:41 PM Typo corrected.
    Saturday, May 23, 2020 4:40 PM
  • Here is a quick example of a simple vehicle maintenance database based on your description. It appears you are not using ACCESS as a relational tool. Each vehicle may have > 1 Ticket and each Ticket may have > 1 Item.  Each item may be a tire rotation or oil change or whatever.

    But each car or ticket does NOT have its own table. There are 3 tables. The ItemTable has a Foreign Key (TicketID) that is the Primary Key in the TicketTable. Likewise, the TicketTable has a Foreign Key (VehicleID) that is the Primary Key in the VehicleTable. This is how a relational database works. VehicleDesription is a user input field (If needed), TicketNo is a user input field and ItemDescription is a user input field.

    Make sure each Foreign Key has its table "Indexed" property set to Yes (Duplicates OK). Each Primary Key has its "Indexed" property set to Yes (No Duplicates).

    Maybe this can at least get you started in the right direction.


    Saturday, May 23, 2020 4:50 PM
  • Make sure each Foreign Key has its table "Indexed" property set to Yes (Duplicates OK). Each Foreign Key has its "Indexed" property set to Yes (No Duplicates).

    Hi Lawrence,

    I assume you mean:      Each Primary Key has its "Indexed" property set to Yes (No Duplicates).

    Imb.

    Saturday, May 23, 2020 5:31 PM
  • Yes, thank you and good catch. I changed it.
    Saturday, May 23, 2020 6:06 PM
  • I set the tables as per the example and still no resolve, is there anyway I can upload the DB so you can take a stab at it, I am certian you will have it fixed in less then 5 minutes.
    Saturday, May 23, 2020 8:36 PM
  • You may not be grouping the data in your report correctly. Have you created Groupings for Cars, Tickets and Items? The Grouping order should be Cars, then Tickets and finally Items. So the Items for Ticket #2 for Car #1 should be Item #2 Oil Change and Item #3 Tire Rotation only.

    In report design mode, create the 3 Groupings and see if that works. You can also create Group Footers so you can count or sum values etc.

    Right-click any blank area in the report design and click "Sorting and Grouping" if it is not already displayed at the bottom of the design window. Click "Add A Group" and select the Car Number field (whatever that field is). Do the same for the Ticket Number field and the Item Number field.

    Make sure all the field data for each grouping is inside the grouping header. All Car table fields under the Car Grouping and so on.

    You can upload the database to your OneDrive account and then post the link here. You didn't say which ACCESS version you are using, so be careful. You should tell us what version first. I'll be happy to take a look.

    Saturday, May 23, 2020 9:05 PM
  • I have uploaded a simple file illustrating how to model this as VehicleServices.accdb to my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    The image below is of the user interface:



    and that below is of a simple report opened from the above form:



     


    Ken Sheridan, Stafford, England

    Saturday, May 23, 2020 11:09 PM
  • I have attempted all of the suggested methods and nothing is working, do you know of anyone whom will fix this issue so I can contiinue with the project?
    Sunday, May 24, 2020 7:15 PM
  • You've been sent a working database as an example on which to base your own, and you've been offered the opportunity to upload your file to OneDrive (or similar) from where it can be downloaded for us to take a look at it.  What more do you expect?

    Ken Sheridan, Stafford, England

    Sunday, May 24, 2020 8:33 PM
  • If you download Kens model from the link he provides and add a Vehicle RegistrationNumber Grouping to the report, you will get the result you are looking for. Each "Item" under each Ticket under each vehicle. I downloaded the model and added a vehicle RegistrationNumber Grouping myself and it worked fine. Of course, you will need to customize the user input form and the report to suit your own requirements. Just make sure to move the vehicle Grouping to the position before the ticket grouping. And Kens from England, so he spells Tire as Tyre (hehehe).

    Sunday, May 24, 2020 8:43 PM
  • ..............and add a Vehicle RegistrationNumber Grouping to the report

    I'd assumed that each ticket relates to only one vehicle, in which case grouping on the RegistrationNumber would be superfluous.  If I'm wrong and a ticket can include multiple vehicles, then the additional group level would be required, and the line:

         MoveLayout = False

    would also need to be moved from the ServiceTicketID header's Format event procedure to the RegistrationNumber header's Format event procedure to cause the first detail line to print on the same line as the header.


    Ken Sheridan, Stafford, England

    Sunday, May 24, 2020 9:10 PM
  • I assumed each vehicle could have multiple tickets, each with a date (and other information) and each ticket could have multiple service items. You could bring the vehicle in for an oil change one time and a ti(y)re rotation and tune-up at another time etc. In addition, each service item could be performed by a different person, so an employee table could be in order as well.
    Sunday, May 24, 2020 10:08 PM
  • I assumed each vehicle could have multiple tickets............
    That's inherent I my model too.  Because ServiceTicketID functionally determines VehicleID, a second group level would do nothing however.  To return all the tickets for each car together in the report, it would be ordered by VehicleID  or RegistrationNumber (and optionally by ServiceTicketDate) before the ServiceTicketID group.


    Ken Sheridan, Stafford, England

    Monday, May 25, 2020 10:34 AM
  • Ken:

    Yes I see that. Helpwithdata has been given instruction on how to use the relational model and given access to an example of how to implement it. Good enough.

    Monday, May 25, 2020 2:51 PM
  • Thank you all for your guidance, but really, would any Professional issue a customer a lame ticket such as the one above?

    No Customer, Technician, Date in, Date out, Cost, Man Hours, Next Service due information,

    Just a consolidated mess, which looks more like a hand written note from a Good Ole Shade Tree Mechnaic.

    You guys are idiots.

    Go play with your Strar Trek figures in your parents basement.

    Monday, May 25, 2020 3:31 PM
  • Speaking just for myself, you are more than welcome and I, for one, was happy to help. I am so glad we were able to get you on the right track.
    Monday, May 25, 2020 3:54 PM
  • Thank you all for your guidance, but really, would any Professional issue a customer a lame ticket such as the one above?


    What we've given you is just the basic methodology for you to flesh out.  It's not our role to do your job for you.  If you can't do something as basic as this you might want to think of hiring someone who can.

    Ken Sheridan, Stafford, England

    Monday, May 25, 2020 4:05 PM