none
Query from postal adress with range of housenumbers RRS feed

  • Question

  • Hello,

    I have the following question:

    I want to store in a table streetname, postalcode, city and the person who in this street concerned the newspaper.

    This is clear to me because i can put in the where clause Like "3236 VV" of = "3246 VV" for postal code.

    On some streets there are more deliverers like relationship that each have a part of the street. I thought having to define two additional columns with the housenumbers from X and to Y, eg. Number from 19 to 99.

    How can I get the query to find out who delivered at a specific address and number. The number must fall between the two columns X and Y.

    E.g. Lanestreet 129.

    The table has Lanestreet col X 100 to col Y 499 is related to a person Z.

    On Street i can put select on where Street = "Lanestreet", bus how i can the query selected that this record must be select why the housenumber is 129 .


     I do not see how I can fix this. Like your help.

    Thursday, February 4, 2016 1:54 PM

Answers

  • In another table e.g. table "Relations" i put the relations who read the newspaper. Based on the address/city must be searched automatically in the table RELAdersboek which newsboy the newspaper delivered on that address. When i (in this e.g.) have a new relation X that lives on 'Langeweg 203 Groteplaats' then i know that i can search e.g. "WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf)>=203) AND ((RelAdresboek.GrensTm)<=203));", but this results me NOT RELID 15. How can i fix this?? I hope that the situation now is put clearly explained and hope someone can help me on the right track. Except that I want to define the limits in a street, I also want to define or only the even, or only the odd, or all numbers must be delivered by the paperboy. That makes it even more complicated. Anyone have an idea?

    Hallo Henk,

    You can try:

    "WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf) <= 203)  AND ((RelAdresboek.GrensTm) >= 203));".

    In the RELAdersboek you can add one more field to indicate whether the number range is mixed (both even and uneven) or not. With a mixed range you wil get only one record. When the result is two record, yiou have both an even and an uneven range, and depending on parity of Grensvanaf you know which range is the right one. Or make this additional field for three values: M(ixed) - E(ven) - On(even).

    Groeten, Imb.

    • Marked as answer by HenkK64 Friday, February 5, 2016 12:45 PM
    Thursday, February 4, 2016 9:22 PM

All replies

  • The table has Lanestreet col X 100 to col Y 499 is related to a person Z.

    On Street i can put select on where Street = "Lanestreet", bus how i can the query selected that this record must be select why the housenumber is 129 .


     I do not see how I can fix this. Like your help.

    Hallo Henk,

    You can find the correct Z with this part of the query:

        ... " WHERE Straat = 'Lanestreet' AND " & huisnummer & " >= X AND " & huisnummer " <= Y"

    or

        ... " WHERE Straat = 'Lanestreet' AND " & huisnummer   & " BETWEEN X AND Y"

    One way or the other you substitute "huisnummer" with the real value of the huisnummer.

    Groeten, Imb.

    Thursday, February 4, 2016 2:10 PM
  • if per your post: a table has columns X =100 and Y = 499 (which are number fields) such that the table is:

    Lanestreet   100   499

    then you can do a query with criteria:

    Lanestreet   >=100  <=499

    being on the same line they are AND criteria

    so the result will be any record that meets that criteria which would include: Lanestreet 129

    Thursday, February 4, 2016 3:24 PM
  • Thanks for all replay, but the cause is not likeyou answers. I willtry to explainit more clearly.

    I created a table "RELAdersboek". I define in this table which newsboy deliverers the newspaper in a street or a part of a street.

    In another table e.g. table "Relations" i put the relations who read the newspaper. Based on the address/city must be searched automatically in the table RELAdersboek which newsboy the newspaper delivered on that address. When i (in this e.g.) have a new relation X that lives on 'Langeweg 203 Groteplaats' then i know that i can search e.g. "WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf)>=203) AND ((RelAdresboek.GrensTm)<=203));", but this results me NOT RELID 15. How can i fix this?? I hope that the situation now is put clearly explained and hope someone can help me on the right track. Except that I want to define the limits in a street, I also want to define or only the even, or only the odd, or all numbers must be delivered by the paperboy. That makes it even more complicated. Anyone have an idea?

    • Marked as answer by HenkK64 Friday, February 5, 2016 12:44 PM
    • Unmarked as answer by HenkK64 Friday, February 5, 2016 12:44 PM
    Thursday, February 4, 2016 9:03 PM
  • In another table e.g. table "Relations" i put the relations who read the newspaper. Based on the address/city must be searched automatically in the table RELAdersboek which newsboy the newspaper delivered on that address. When i (in this e.g.) have a new relation X that lives on 'Langeweg 203 Groteplaats' then i know that i can search e.g. "WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf)>=203) AND ((RelAdresboek.GrensTm)<=203));", but this results me NOT RELID 15. How can i fix this?? I hope that the situation now is put clearly explained and hope someone can help me on the right track. Except that I want to define the limits in a street, I also want to define or only the even, or only the odd, or all numbers must be delivered by the paperboy. That makes it even more complicated. Anyone have an idea?

    Hallo Henk,

    You can try:

    "WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf) <= 203)  AND ((RelAdresboek.GrensTm) >= 203));".

    In the RELAdersboek you can add one more field to indicate whether the number range is mixed (both even and uneven) or not. With a mixed range you wil get only one record. When the result is two record, yiou have both an even and an uneven range, and depending on parity of Grensvanaf you know which range is the right one. Or make this additional field for three values: M(ixed) - E(ven) - On(even).

    Groeten, Imb.

    • Marked as answer by HenkK64 Friday, February 5, 2016 12:45 PM
    Thursday, February 4, 2016 9:22 PM
  • Hallo Imb-hb,

    Actually, it was simple, but I do not Yesterday saw the solution. You've helped me a lot. For completeness, I've included my current table and the test query, writing the variable 'house number' as a test to enter at the start of the query. This will become later invoked automatically without the user noticing anything here. User only see the correct paper boy who delivered the papers to the entered address.

    To define a table of addresses where the user is free throughout the street, or eg. t,o grant the odd / even house numbers and / or selection of numbers on a paper boy, I thought in the table to create a text field 'Select Numbers ', with the choice of: "M", "Everything", "0", "Even", "1", "Odd". In the query I rate than if the number is even or odd, and let them select only when the table indicates that only odd or even numbers are assigned to a paperboy. I have included screenshots. This works well. You may have a better solution? In any case, thank you very much for your help!

    Groeten Henk

     

    SELECT RelAdresboek.RELAdres, RelAdresboek.RELPostkode, RelAdresboek.RELWoonplaats, RelAdresboek.RELLand, RelAdresboek.GrensVanaf, RelAdresboek.GrensTm, RelAdresboek.REL_ID, RelAdresboek.SelectNumbers
    FROM RelAdresboek
    WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf)<=[Housenumber]) AND ((RelAdresboek.GrensTm)>=[Housenumber]) AND ((RelAdresboek.SelectNumbers)=IIf([SelectNumbers]<>"M",Val([Housenumber]) Mod 2,"M")));

    Friday, February 5, 2016 12:44 PM
  • This works well. You may have a better solution? In any case, thank you very much for your help

     

    SELECT RelAdresboek.RELAdres, RelAdresboek.RELPostkode, RelAdresboek.RELWoonplaats, RelAdresboek.RELLand, RelAdresboek.GrensVanaf, RelAdresboek.GrensTm, RelAdresboek.REL_ID, RelAdresboek.SelectNumbers
    FROM RelAdresboek
    WHERE (((RelAdresboek.RELAdres)="Langeweg") AND ((RelAdresboek.RELWoonplaats)="Groteplaats") AND ((RelAdresboek.RELLand)="Nederland") AND ((RelAdresboek.GrensVanaf)<=[Housenumber]) AND ((RelAdresboek.GrensTm)>=[Housenumber]) AND ((RelAdresboek.SelectNumbers)=IIf([SelectNumbers]<>"M",Val([Housenumber]) Mod 2,"M")));

    Hallo Henk,

    The WHERE-part is quite elaborate, and far longer then I normally have. Probably your newspaper readers have the same fields.

    In that case I would use a Straat_tbl, with fields Straat_id, Adres, Woonplaats, Land.

    The RELadresboek then has the fields: RELadresboek_id, Straat_id (FK), GrensVanaf, GrensTm,  RELId, SelectNumbers

    And the newsreader table would contain  Reader_id, Straat_id, Huisnummer.

    In this way the database is a bit further normalized, it reduces the amount of typing, and the most important it improves data consistency.

    When a newsboy serves only part of a street, and this is consistent with a changing Postcode, you could even refer the a (part of a) Postcode database.

    Groeten, Imb.

    Friday, February 5, 2016 1:29 PM