Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Create a new Table Question

Discussion in 'Information Technology' started by Bob, Jul 28, 2006.

  1. Bob

    Bob
    Expand Collapse
    Guest

    I have a table that has 3 Coolum's
    OwnerID
    HorseID
    OwnerPercentage

    OwnerID and HorseID only give the ID Number
    In other tables I have OwnerName and HorseName, how can I put:
    OwnerName, HorseName, and OwnerPercentage together!

    Thanks in advance.........Bob Vance
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Create a query, and in the query you can include all 3 tables.

    In a form, you could use combo boxes for the OwnerID and HorseID, and set
    Column Widths of the combos to the ID column is zero-width.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Bob" <xxx@xx.xx> wrote in message
    news:e7t3em$jj1$1@lust.ihug.co.nz...
    >
    > I have a table that has 3 Coolum's
    > OwnerID
    > HorseID
    > OwnerPercentage
    >
    > OwnerID and HorseID only give the ID Number
    > In other tables I have OwnerName and HorseName, how can I put:
    > OwnerName, HorseName, and OwnerPercentage together!
    >
    > Thanks in advance.........Bob Vance
     
  4. Bob

    Bob
    Expand Collapse
    Guest

    My table already has OwnerID ClientID OwnersPercentage
    How do I match there Names to ID because there on a different table!
    Thanks Bob
    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:OqeGEanmGHA.3296@TK2MSFTNGP05.phx.gbl...
    > Create a query, and in the query you can include all 3 tables.
    >
    > In a form, you could use combo boxes for the OwnerID and HorseID, and set
    > Column Widths of the combos to the ID column is zero-width.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Bob" <xxx@xx.xx> wrote in message
    > news:e7t3em$jj1$1@lust.ihug.co.nz...
    >>
    >> I have a table that has 3 Coolum's
    >> OwnerID
    >> HorseID
    >> OwnerPercentage
    >>
    >> OwnerID and HorseID only give the ID Number
    >> In other tables I have OwnerName and HorseName, how can I put:
    >> OwnerName, HorseName, and OwnerPercentage together!
    >>
    >> Thanks in advance.........Bob Vance

    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    1. Click the Queries tab of the Database window.

    2. Click New.
    Access opens a new query, and the Add Table dialog.

    3. Add all 3 tables to the query.

    4. Close the dialog.

    5. Add as many fields as you want from all 3 tables.

    In the query results, you can now see the names from the other tables, as
    well.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Bob" <xxx@xx.xx> wrote in message news:e7t5un$oau$1@lust.ihug.co.nz...
    > My table already has OwnerID ClientID OwnersPercentage
    > How do I match there Names to ID because there on a different table!
    > Thanks Bob
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:OqeGEanmGHA.3296@TK2MSFTNGP05.phx.gbl...
    >> Create a query, and in the query you can include all 3 tables.
    >>
    >> In a form, you could use combo boxes for the OwnerID and HorseID, and set
    >> Column Widths of the combos to the ID column is zero-width.
    >>
    >> "Bob" <xxx@xx.xx> wrote in message
    >> news:e7t3em$jj1$1@lust.ihug.co.nz...
    >>>
    >>> I have a table that has 3 Coolum's
    >>> OwnerID
    >>> HorseID
    >>> OwnerPercentage
    >>>
    >>> OwnerID and HorseID only give the ID Number
    >>> In other tables I have OwnerName and HorseName, how can I put:
    >>> OwnerName, HorseName, and OwnerPercentage together!
     
  6. Bob

    Bob
    Expand Collapse
    Guest

    When I try to create a qry from different tables access tells me I need a
    wizard to do this , but just cant seem to get a qry from 3 different
    tables..thanx bob

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:uIGa0MomGHA.4700@TK2MSFTNGP03.phx.gbl...
    > 1. Click the Queries tab of the Database window.
    >
    > 2. Click New.
    > Access opens a new query, and the Add Table dialog.
    >
    > 3. Add all 3 tables to the query.
    >
    > 4. Close the dialog.
    >
    > 5. Add as many fields as you want from all 3 tables.
    >
    > In the query results, you can now see the names from the other tables, as
    > well.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Bob" <xxx@xx.xx> wrote in message news:e7t5un$oau$1@lust.ihug.co.nz...
    >> My table already has OwnerID ClientID OwnersPercentage
    >> How do I match there Names to ID because there on a different table!
    >> Thanks Bob
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:OqeGEanmGHA.3296@TK2MSFTNGP05.phx.gbl...
    >>> Create a query, and in the query you can include all 3 tables.
    >>>
    >>> In a form, you could use combo boxes for the OwnerID and HorseID, and
    >>> set Column Widths of the combos to the ID column is zero-width.
    >>>
    >>> "Bob" <xxx@xx.xx> wrote in message
    >>> news:e7t3em$jj1$1@lust.ihug.co.nz...
    >>>>
    >>>> I have a table that has 3 Coolum's
    >>>> OwnerID
    >>>> HorseID
    >>>> OwnerPercentage
    >>>>
    >>>> OwnerID and HorseID only give the ID Number
    >>>> In other tables I have OwnerName and HorseName, how can I put:
    >>>> OwnerName, HorseName, and OwnerPercentage together!

    >
    >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The first option when you create a query is:
    Design View.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Bob" <xxx@xx.xx> wrote in message news:e7thqb$du2$1@lust.ihug.co.nz...
    > When I try to create a qry from different tables access tells me I need a
    > wizard to do this , but just cant seem to get a qry from 3 different
    > tables..thanx bob
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:uIGa0MomGHA.4700@TK2MSFTNGP03.phx.gbl...
    >> 1. Click the Queries tab of the Database window.
    >>
    >> 2. Click New.
    >> Access opens a new query, and the Add Table dialog.
    >>
    >> 3. Add all 3 tables to the query.
    >>
    >> 4. Close the dialog.
    >>
    >> 5. Add as many fields as you want from all 3 tables.
    >>
    >> In the query results, you can now see the names from the other tables, as
    >> well.
     
  8. Bob

    Bob
    Expand Collapse
    Guest

    Allen I am getting a no Relationship error..Thanks bob

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:eh$V1WrmGHA.1488@TK2MSFTNGP02.phx.gbl...
    > The first option when you create a query is:
    > Design View.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Bob" <xxx@xx.xx> wrote in message news:e7thqb$du2$1@lust.ihug.co.nz...
    >> When I try to create a qry from different tables access tells me I need a
    >> wizard to do this , but just cant seem to get a qry from 3 different
    >> tables..thanx bob
    >>
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:uIGa0MomGHA.4700@TK2MSFTNGP03.phx.gbl...
    >>> 1. Click the Queries tab of the Database window.
    >>>
    >>> 2. Click New.
    >>> Access opens a new query, and the Add Table dialog.
    >>>
    >>> 3. Add all 3 tables to the query.
    >>>
    >>> 4. Close the dialog.
    >>>
    >>> 5. Add as many fields as you want from all 3 tables.
    >>>
    >>> In the query results, you can now see the names from the other tables,
    >>> as well.

    >
    >
     
  9. Bob

    Bob
    Expand Collapse
    Guest

    I have 3 Tables
    TblHorseDetails= HorseID,OwnerID,OwnerPercentage
    TblHorseInfo=HorseName
    TbleOwnerInfo=OwnersLastName, OwnersFirstName

    What am trying to do is put these together except HorseID and OwnerID which
    are there ID numbers
    When I do put them together in a Qry , I get Every Owner with every Horse
    with every percentage!
    It is not reconizing the Indivual Owner,Horse,percetage
    Thanks for your help, Bob
     
  10. Michael Gramelspacher

    Michael Gramelspacher
    Expand Collapse
    Guest

    In article <e7v0h1$2j8$1@lust.ihug.co.nz>, xxx@xx.xx says...
    > I have 3 Tables
    > TblHorseDetails= HorseID,OwnerID,OwnerPercentage
    > TblHorseInfo=HorseName
    > TbleOwnerInfo=OwnersLastName, OwnersFirstName
    >
    > What am trying to do is put these together except HorseID and OwnerID which
    > are there ID numbers
    > When I do put them together in a Qry , I get Every Owner with every Horse
    > with every percentage!
    > It is not reconizing the Indivual Owner,Horse,percetage
    > Thanks for your help, Bob
    >
    >
    >

    I would have 3 tables:

    Horses
    -------
    horse_nbr INTEGER NOT NULLL PRIMARY KEY
    horse_name VARCHAR (30)

    Owners
    -------
    owner_nbr INTEGER NOT NULL PRIMARY KEY
    last_name VARCHAR (30)
    first_name VARCHAR (30)

    HorseOwners
    -----------
    horse_nbr IINTEGER NOT NULL
    owner_nbr INTEGER NOT NULL
    ownership_begin DATETIME NOT NULL
    ownership_end DATETIME
    PRIMARY KEY (horse_nbr, owner_nbr)

    This way you can have multiple owners of the same horse and ownership
    over time. The current horse owner has ownership_end null. One of the
    owners could buy out another this way too.
     
  11. Bob

    Bob
    Expand Collapse
    Guest

    Micheal Most of the horses do have multiple owners, and the percentage
    don't not have to add to 100% as I have ownership in a lot of them. If there
    is a change of ownership I just change the owners at the end of the month so
    it does not effect the invoicing of that month
    , Is there not a simpler way that I can just see what owners own and there
    percentages....Thanx Bob
    "Michael Gramelspacher" <gramelsp@psci.net> wrote in message
    news:MPG.1f0ceb73f4cbc47a989694@msnews.microsoft.com...
    > In article <e7v0h1$2j8$1@lust.ihug.co.nz>, xxx@xx.xx says...
    >> I have 3 Tables
    >> TblHorseDetails= HorseID,OwnerID,OwnerPercentage
    >> TblHorseInfo=HorseName
    >> TbleOwnerInfo=OwnersLastName, OwnersFirstName
    >>
    >> What am trying to do is put these together except HorseID and OwnerID
    >> which
    >> are there ID numbers
    >> When I do put them together in a Qry , I get Every Owner with every Horse
    >> with every percentage!
    >> It is not reconizing the Indivual Owner,Horse,percetage
    >> Thanks for your help, Bob
    >>
    >>
    >>

    > I would have 3 tables:
    >
    > Horses
    > -------
    > horse_nbr INTEGER NOT NULLL PRIMARY KEY
    > horse_name VARCHAR (30)
    >
    > Owners
    > -------
    > owner_nbr INTEGER NOT NULL PRIMARY KEY
    > last_name VARCHAR (30)
    > first_name VARCHAR (30)
    >
    > HorseOwners
    > -----------
    > horse_nbr IINTEGER NOT NULL
    > owner_nbr INTEGER NOT NULL
    > ownership_begin DATETIME NOT NULL
    > ownership_end DATETIME
    > PRIMARY KEY (horse_nbr, owner_nbr)
    >
    > This way you can have multiple owners of the same horse and ownership
    > over time. The current horse owner has ownership_end null. One of the
    > owners could buy out another this way too.
     
  12. Michael Gramelspacher

    Michael Gramelspacher
    Expand Collapse
    Guest

    In article <e7v8hu$gn2$1@lust.ihug.co.nz>, xxx@xx.xx says...
    > Micheal Most of the horses do have multiple owners, and the percentage
    > don't not have to add to 100% as I have ownership in a lot of them. If there
    > is a change of ownership I just change the owners at the end of the month so
    > it does not effect the invoicing of that month
    > , Is there not a simpler way that I can just see what owners own and there
    > percentages....Thanx Bob
    > "Michael Gramelspacher" <gramelsp@psci.net> wrote in message
    > news:MPG.1f0ceb73f4cbc47a989694@msnews.microsoft.com...
    > > In article <e7v0h1$2j8$1@lust.ihug.co.nz>, xxx@xx.xx says...
    > >> I have 3 Tables
    > >> TblHorseDetails= HorseID,OwnerID,OwnerPercentage
    > >> TblHorseInfo=HorseName
    > >> TbleOwnerInfo=OwnersLastName, OwnersFirstName
    > >>
    > >> What am trying to do is put these together except HorseID and OwnerID
    > >> which
    > >> are there ID numbers
    > >> When I do put them together in a Qry , I get Every Owner with every Horse
    > >> with every percentage!
    > >> It is not reconizing the Indivual Owner,Horse,percetage
    > >> Thanks for your help, Bob
    > >>
    > >>
    > >>

    > > I would have 3 tables:
    > >
    > > Horses
    > > -------
    > > horse_nbr INTEGER NOT NULLL PRIMARY KEY
    > > horse_name VARCHAR (30)
    > >
    > > Owners
    > > -------
    > > owner_nbr INTEGER NOT NULL PRIMARY KEY
    > > last_name VARCHAR (30)
    > > first_name VARCHAR (30)
    > >
    > > HorseOwners
    > > -----------
    > > horse_nbr IINTEGER NOT NULL
    > > owner_nbr INTEGER NOT NULL
    > > ownership_begin DATETIME NOT NULL
    > > ownership_end DATETIME
    > > PRIMARY KEY (horse_nbr, owner_nbr)
    > >
    > > This way you can have multiple owners of the same horse and ownership
    > > over time. The current horse owner has ownership_end null. One of the
    > > owners could buy out another this way too.

    >
    >

    Perhaps someone else here has an answer for you. Maybe even the way you
    are doing it now is the best way for you.
     

Share This Page