multiple table form based on a query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Deus402
    #1

    multiple table form based on a query

    Here is my table design:

    tblEmployers
    EmployerID autonum (primary key)
    EmployerName text

    tblLocations
    LocationID autonum (primary key)
    EmployerID longint (foreign key)
    LocationAdress text
    LocationCity text
    Location ...
    ...
    ...

    what i am trying to do is have a form where you can enter a new
    location, and when you type in the employer name it will either look up
    the EmployerID in the tblEmployers table and insert it into the
    tblLocations table, or if it doesn't exist, create a new entry in the
    tblEmployers Table and insert the new EmployerID into the tblLocations
    table. I am not sure exactly how to do this... right now i have a
    query that just includes all fields from both tables, and a form based
    on that query. in the tblLocations.Em ployerID field i have the control
    source set to tblLocations_Em ployerID and the row source set to
    tblEmployers.Em ployerID. this allows me to enter a new employer,
    putting the right data in both tables EmployerID, but it will create a
    new entry in the tblEmployers table even if the EmployerName already
    exists.

    Am i on the right track, or is there a better way to do this?

    Tags: None
    • lesperancer@natpro.com
      #2
      Re: multiple table form based on a query

      create a form based on employers

      create a continuous form based on locations

      open the employers form and drag the locations form to it... creating a
      subform, linked by employerId

      now you can add a employer and one-or-more locations
      or you can search for any existing employer and add/delete locations

      Deus402 wrote:
      Here is my table design:
      >
      tblEmployers
      EmployerID autonum (primary key)
      EmployerName text
      >
      tblLocations
      LocationID autonum (primary key)
      EmployerID longint (foreign key)
      LocationAdress text
      LocationCity text
      Location ...
      ...
      ...
      >
      what i am trying to do is have a form where you can enter a new
      location, and when you type in the employer name it will either look up
      the EmployerID in the tblEmployers table and insert it into the
      tblLocations table, or if it doesn't exist, create a new entry in the
      tblEmployers Table and insert the new EmployerID into the tblLocations
      table. I am not sure exactly how to do this... right now i have a
      query that just includes all fields from both tables, and a form based
      on that query. in the tblLocations.Em ployerID field i have the control
      source set to tblLocations_Em ployerID and the row source set to
      tblEmployers.Em ployerID. this allows me to enter a new employer,
      putting the right data in both tables EmployerID, but it will create a
      new entry in the tblEmployers table even if the EmployerName already
      exists.
      >
      Am i on the right track, or is there a better way to do this?

        Comment

        • Deus402
          #3
          Re: multiple table form based on a query

          thats not exactly what i am looking for...

          what i am looking for is the ability to enter all the data for one
          location and employer in one shot, when a new employer is added, but
          prevent the user from entering employer as new if it already exists.
          even with your subform design, you can still enter a employer name that
          already exists and it creates a new key for it. should i just chnge my
          table so the primary key is the employer name? it seems kind of silly
          to have a table with only one column. maybe i should just have the
          employer name in the locations table and not worry about normalization?


          the ultimate goal here is to have a link on the main switchboard for
          new data entry, because most employers will only have one location when
          they are newly entered. i have a separate form for adding a location to
          an existing employer... which is almost working like i want it to, but
          thats another story.

          i appreciate the help, i took an access class at the local university,
          but it was so basic that it didn't really help...

          lesperancer@nat pro.com wrote:
          create a form based on employers
          >
          create a continuous form based on locations
          >
          open the employers form and drag the locations form to it... creating a
          subform, linked by employerId
          >
          now you can add a employer and one-or-more locations
          or you can search for any existing employer and add/delete locations
          >
          Deus402 wrote:
          Here is my table design:

          tblEmployers
          EmployerID autonum (primary key)
          EmployerName text

          tblLocations
          LocationID autonum (primary key)
          EmployerID longint (foreign key)
          LocationAdress text
          LocationCity text
          Location ...
          ...
          ...

          what i am trying to do is have a form where you can enter a new
          location, and when you type in the employer name it will either look up
          the EmployerID in the tblEmployers table and insert it into the
          tblLocations table, or if it doesn't exist, create a new entry in the
          tblEmployers Table and insert the new EmployerID into the tblLocations
          table. I am not sure exactly how to do this... right now i have a
          query that just includes all fields from both tables, and a form based
          on that query. in the tblLocations.Em ployerID field i have the control
          source set to tblLocations_Em ployerID and the row source set to
          tblEmployers.Em ployerID. this allows me to enter a new employer,
          putting the right data in both tables EmployerID, but it will create a
          new entry in the tblEmployers table even if the EmployerName already
          exists.

          Am i on the right track, or is there a better way to do this?

            Comment

            • Deus402
              #4
              Re: multiple table form based on a query

              Nevermind, i went with a different design.


              Deus402 wrote:
              thats not exactly what i am looking for...
              >
              what i am looking for is the ability to enter all the data for one
              location and employer in one shot, when a new employer is added, but
              prevent the user from entering employer as new if it already exists.
              even with your subform design, you can still enter a employer name that
              already exists and it creates a new key for it. should i just chnge my
              table so the primary key is the employer name? it seems kind of silly
              to have a table with only one column. maybe i should just have the
              employer name in the locations table and not worry about normalization?
              >
              >
              the ultimate goal here is to have a link on the main switchboard for
              new data entry, because most employers will only have one location when
              they are newly entered. i have a separate form for adding a location to
              an existing employer... which is almost working like i want it to, but
              thats another story.
              >
              i appreciate the help, i took an access class at the local university,
              but it was so basic that it didn't really help...
              >
              lesperancer@nat pro.com wrote:
              create a form based on employers

              create a continuous form based on locations

              open the employers form and drag the locations form to it... creating a
              subform, linked by employerId

              now you can add a employer and one-or-more locations
              or you can search for any existing employer and add/delete locations

              Deus402 wrote:
              Here is my table design:
              >
              tblEmployers
              EmployerID autonum (primary key)
              EmployerName text
              >
              tblLocations
              LocationID autonum (primary key)
              EmployerID longint (foreign key)
              LocationAdress text
              LocationCity text
              Location ...
              ...
              ...
              >
              what i am trying to do is have a form where you can enter a new
              location, and when you type in the employer name it will either look up
              the EmployerID in the tblEmployers table and insert it into the
              tblLocations table, or if it doesn't exist, create a new entry in the
              tblEmployers Table and insert the new EmployerID into the tblLocations
              table. I am not sure exactly how to do this... right now i have a
              query that just includes all fields from both tables, and a form based
              on that query. in the tblLocations.Em ployerID field i have the control
              source set to tblLocations_Em ployerID and the row source set to
              tblEmployers.Em ployerID. this allows me to enter a new employer,
              putting the right data in both tables EmployerID, but it will create a
              new entry in the tblEmployers table even if the EmployerName already
              exists.
              >
              Am i on the right track, or is there a better way to do this?

                Comment

                • Kc-Mass
                  #5
                  Re: multiple table form based on a query

                  Just make it an index - no duplicates

                  Kevin C


                  "Deus402" <dpolak@gmail.c omwrote in message
                  news:1162899196 .154532.323310@ h48g2000cwc.goo glegroups.com.. .
                  thats not exactly what i am looking for...
                  >
                  what i am looking for is the ability to enter all the data for one
                  location and employer in one shot, when a new employer is added, but
                  prevent the user from entering employer as new if it already exists.
                  even with your subform design, you can still enter a employer name that
                  already exists and it creates a new key for it. should i just chnge my
                  table so the primary key is the employer name? it seems kind of silly
                  to have a table with only one column. maybe i should just have the
                  employer name in the locations table and not worry about normalization?
                  >
                  >
                  the ultimate goal here is to have a link on the main switchboard for
                  new data entry, because most employers will only have one location when
                  they are newly entered. i have a separate form for adding a location to
                  an existing employer... which is almost working like i want it to, but
                  thats another story.
                  >
                  i appreciate the help, i took an access class at the local university,
                  but it was so basic that it didn't really help...
                  >
                  lesperancer@nat pro.com wrote:
                  >create a form based on employers
                  >>
                  >create a continuous form based on locations
                  >>
                  >open the employers form and drag the locations form to it... creating a
                  >subform, linked by employerId
                  >>
                  >now you can add a employer and one-or-more locations
                  >or you can search for any existing employer and add/delete locations
                  >>
                  >Deus402 wrote:
                  Here is my table design:
                  >
                  tblEmployers
                  EmployerID autonum (primary key)
                  EmployerName text
                  >
                  tblLocations
                  LocationID autonum (primary key)
                  EmployerID longint (foreign key)
                  LocationAdress text
                  LocationCity text
                  Location ...
                  ...
                  ...
                  >
                  what i am trying to do is have a form where you can enter a new
                  location, and when you type in the employer name it will either look up
                  the EmployerID in the tblEmployers table and insert it into the
                  tblLocations table, or if it doesn't exist, create a new entry in the
                  tblEmployers Table and insert the new EmployerID into the tblLocations
                  table. I am not sure exactly how to do this... right now i have a
                  query that just includes all fields from both tables, and a form based
                  on that query. in the tblLocations.Em ployerID field i have the control
                  source set to tblLocations_Em ployerID and the row source set to
                  tblEmployers.Em ployerID. this allows me to enter a new employer,
                  putting the right data in both tables EmployerID, but it will create a
                  new entry in the tblEmployers table even if the EmployerName already
                  exists.
                  >
                  Am i on the right track, or is there a better way to do this?
                  >

                    Comment

                    Previous template Next
                    Working...