Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33

    Unhappy Generate a Joint Table for a many to many relationship

    Hey guys,

    I m trying to set up a database for a some road measurements.
    I have assigned my measurements to places on the road that are defined by : kilometric points that i call KP, Lane (left and right), and Direction (north south) and these places belong to zones (defined by KP begin and end, direction and lane). And other table that refer to these zones. I want to relate the KPs to the according zone but i dont really know how but i have some ideas:
    -Junction table where I correspond each KP to its corresponding zone by hand : 1/10 would take days
    -A code where I say that if the KP is superior to KP_begin and inferior to KP_end it belongs to that zone (problem is that zones are ascending North Bound (ex 219 to 232) and descending South Bound (232-219).
    -To each zone i define the possible KP and then related it to my KP from the measurement.

    Any help is appreciated I m very new to Access ;.;

    CoZak

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think it would be helpful to show examples with your starting data and your expected results.
    Show enough data to cover all possible outcomes .
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    You make measurements in measurement point, which is defined with KP, Lane, and Direction. It means, that whenever one of three changes, it will be a different measurement point. I.e. when you have at certain KP 2 lanes in both directions, you have 4 different measurement points at same KP.

    Then you have zones, which are defined with KP range, Lane and direction. This means every measurement point from example in previous paragraph must belong to different zone. Is this how you planned it? When yes, then you have to relate measurement points (MP) with zones - you can't relate KP's with zones! To relate KP's with zones, zones must be defined by KP range only!

    In case you go with zones linked to measurement points, there will be another problem. Let's take a case where zone starts from KP = 200 km and ends at KP = 300 km. At KP = 200 km are in your direction 2 lanes, your measurement point there is on lane 2. At KP = 240 are 3 lanes in same direction. At KP = 245 are 2 lanes in same direction. At KP = 280 and at KP = 300 kp is 1 lane. How do you define the zones for lanes 2 and 3?

  4. #4
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33
    Click image for larger version. 

Name:	tbl Zones.jpg 
Views:	31 
Size:	180.6 KB 
ID:	34200Click image for larger version. 

Name:	big table.jpg 
Views:	32 
Size:	240.8 KB 
ID:	34201Click image for larger version. 

Name:	tbl KP.jpg 
Views:	31 
Size:	145.7 KB 
ID:	34202
    Here you can see the tables i ve created from that one big table that I imported from Excel
    The final goal is to be able to add new measurements without adding the same zone and meteorological data every time etc

  5. #5
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33
    I planned it in a way that several measurements can belong to 1 zone
    And yes there are 4 different measurement points for the same KP (2 lanes), that s why I created a table with all possible KP (271km x 2 lanes x 2 directions = 1848 Measurement IDs)
    I m trying to link these IDs with zone IDs and then have other table with info about the zones:
    -meteorological
    -posology
    -date of application etc

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    I added something to my previous post, so look it up!

    My advice is, create an additional table for Measurement Points. All measurements info is linked to Measurement Point. And define zones based on KP's only, so all measurement points at certain KP belong to same zone - it makes your life a lot easier. KP's are needed only to define Measurement Points and Zones, and to get reports grouped by/for KP (and are read from Measurement Points table whenever needed).

    About join table. With adviced design it is enough to relate KP's with zones. You can do this using join table, but as you hardly redefine zones freguently, it will be much better to calculate the zone into KP table by code, whenever the zone is redefined. So there will be no calculation of zones in queries, whenever you want do analyze your data.
    And yes there are 4 different measurement points for the same KP (2 lanes), that s why I created a table with all possible KP (271km x 2 lanes x 2 directions = 1848 Measurement IDs)
    MeasurementID must belong to measurements table, and determines a measurement of certain type in certain measurement point at certain time moment. You can have several measurements of same type in same measurement point at different time moments.

  7. #7
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33
    Quote Originally Posted by ArviLaanemets View Post
    And define zones based on KP's only
    I cant do that because 230-240 left NorthBound is not the same as 230-240 right NorthBound

    Quote Originally Posted by ArviLaanemets; [COLOR=#333333
    How do you define the zones for lanes 2 and 3?[/COLOR]
    For each zone I have
    KP begin
    KP end
    Direction
    Lane

    All these define the zone_ID so i put them all as compound key
    I dont know if it was the right choice thought

    Click image for larger version. 

Name:	zones.jpg 
Views:	31 
Size:	146.0 KB 
ID:	34203




  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by CoZak View Post
    I cant do that because 230-240 left NorthBound is not the same as 230-240 right NorthBound

    For each zone I have
    KP begin
    KP end
    Direction
    Lane

    All these define the zone_ID so i put them all as compound key
    I dont know if it was the right choice thought


    As ZoneID is an autonumber, it is unique so that alone should be your PK field.
    However worth indexing the other fields to speed up searches

    Suggest you post a stripped down copy of your database to make it easier to offer a solution

    BTW what does posology mean?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33
    Hi ridders,
    Posology is a fancy word expressing a quantity per volume/mass, i think dosology exists too, but it is mostly used in Pharmaceuticals

    I dont know what you mean by me posting a stripped down copy of your database :/

  10. #10
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It worked.
    I'll look at it properly later today if others don't do so first.
    Two immediate comments
    1. Lots of tables have composite PK fields. Occasionally I use these but normally for no more than two fields.
    I expect you could replace most if not all with a single autonumber field for PK.
    2.You haven't set up relationships between your tables which would help work out the links and the potential gaps where junction tables may be needed.

    Also, please can you indicate which tables/queries you want readers to look at
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33
    I deleted most because they didnt work
    I recreate some but the one to one relationship doesnt work, the small plus doesnt on tables RL_QD measurements which is very weird
    Now my 1st goal is to make a valid relationship from table KP to table Zone
    But i think i have managed smth with the last query called "Last Query Seems to Work"
    File relatedRoad_Marking_Database_4_0.zip

  13. #13
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Observations;

    1) As Colin alluded to, I don't see a need for the compound primary keys. The Autonumber field should be all you need to identify each row.

    2) I don't see a need for the Zone field in tbl_Zones, as this information can be derived from the Begin and End fields, with one exception. This is one of the rows from tbl_Zones;

    Click image for larger version. 

Name:	Capture21.PNG 
Views:	25 
Size:	4.2 KB 
ID:	34210

    Note that the end field is 002+150, which doesn't correspond with what is listed in the Zone field. Is this just a typo, or some type of anomaly that can exist in the data?

    3) You have KP points ranging from 240 - 470, with different direction and lane data. However, in your Zones table, the lowest beginning value seems to be 288. How are KP points below 288 supposed to relate to a Zone?

    4) Do you have an idea about what the data input flow is going to look like? For instance, if the process is going to be that the users will first choose a Zone, then input the related KP data, then a form/sub form with a master/child link should handle the assignment of the correct foreign key. On the other hand, if data input needs to be some other process, then another method for determining the proper Zone will need to be established. That may mean a custom function, or something else.

  14. #14
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33
    Quote Originally Posted by Beetle View Post
    Observations;

    Is this just a typo, or some type of anomaly that can exist in the data?
    That s a typo from me because is used the Left() and mid() formula in except and as you very well pointed out there is are 2 extra spaces in row 18
    Then i ll might delete the Zones from table Zones later

    3)It s just that I dont have data form KP below 288 yet but it ll come. We ll define new zones later

    4)Ideally i d like the user not to have to imput the Zone put just the KP direction and Lane, and then automatically link the KP+direction+Lane to the appropriate Zone (that is imported from an excel table)
    Ideally i d also like the user to enter KP Direction and Zone once and then input many measurements before inputting new KP+direction+lane again

  15. #15
    CoZak is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    33

    Progress

    Hey guys,

    Thanks to you i ve made some good progress as you can see now the relationship table is way smoother
    Click image for larger version. 

Name:	relationships.jpg 
Views:	14 
Size:	116.8 KB 
ID:	34247
    But I still have some issues:
    -The junction table I have to link tbl_Elexar and tbl_Paint_Batch is done using a query where values <#18/05/2018 are assigned the value 1 and another query where values >=#18/05/2018# are assigned the value 2, but as the number of batches will increase i d like to be able to refer to the cells in tbl_Paint_Batch, for example if I change the last application date on tbl_Paint_Batch i d like the query to run with the new date and not to have to remember to chance it manually. Any ideas? Does the range() function work as in excel?

    -Second problem is that i cant manage to update the same column with different values and different conditions, I m obliged to create new conditions and values with another update query. Here I am converting left lane to LC, "right lane" to "RC" etc and i d like to have only one update query.

    Thanks for the help!
    Related is the new dtb where you can see the progress!Road_Marking_Database_8_4.zip

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-23-2018, 09:35 AM
  2. Automatically generate a record in a table
    By scallebe in forum Access
    Replies: 13
    Last Post: 03-23-2018, 02:28 PM
  3. Replies: 1
    Last Post: 05-18-2014, 10:44 AM
  4. Replies: 5
    Last Post: 12-23-2011, 05:44 PM
  5. Generate a List of Items from "Many" Relationship
    By Hammer Mark in forum Reports
    Replies: 0
    Last Post: 04-20-2009, 07:30 AM

Tags for this Thread

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