Welcome to SPN

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

Sign Up Now!

Some advice on table setup (might fix a querying issue)

Discussion in 'Information Technology' started by Access Sortof Newb, Jul 28, 2006.

  1. Access Sortof Newb

    Access Sortof Newb
    Expand Collapse
    Guest

    Good day all,

    I am currently creating a part numbering/inventory/purchase ordering
    database. Most of the database is working around the tblPartNumber
    table. My problem is specifically with my bill of materials tables
    (tblBom and tblBomDetails). Here are my relationships (as seen from the
    "relationships" window in MS Access. "ID" suffix is an index and is the
    same as the name of the table root (i.e. tblPartNumber's primary key is
    PartNumberID) (ASIDE: use fixed font on google groups. For everyone
    else I am sorry; I have no clue how to make a table look good in
    usenet)

    left table right table relationship ID
    tblPartNumber tblBom 1 to 1 PartNumberID
    tblBom tblBomDetails 1 to many PartNumberID
    tblPartNumber tblBomDetails 1 to many PartNumberID
    tblPartType tblPartNumber 1 to many PartTypeID

    Here is the thing. A part number can be both a top level product that
    has a bill of materials (BOM) or it can also be a component within the
    BOM for that product. This is why the primary key of tblPartNumber is
    related to both tblBom and tblBomDetails, since tblBomDetails holds the
    actual parts that make up the top level assembly (the two relationships
    to tblBomDetails are different fields in the table). The part type
    table (tblPartType) is an identifier for the specific part number and
    it can be anything like a screw, a nut, an electrical device etc.

    I have created a query that inner joins the tables tblBomDetails and
    tblPartNumbers

    SQL statement:
    SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    tblBomDetails.PartNumberID
    FROM tblPartNumber INNER JOIN tblBomDetails ON
    tblPartNumber.PartNumberID = tblBomDetails.PartNumberID
    ORDER BY tblPartNumber.PartTypeID;

    The SQL query works fine and shows the data I was expecting (First
    column has repeated values of the BOM associated with the data in the
    following columns and the remaining columns list the part number and
    part type of the part number). Now, I want to create a report from this
    data that will show the part number of the BOM at the form header, then
    break down into part type, then part number. When I try to accomplish
    this in the report wizard, I am unable to move past the first step in
    the wizard, because as soon as I try to add PartTypeID to the report I
    get an error stating "subscript out of range". I have looked over the
    data in the tables and I do not see any reason as to why this is
    arising. Does anybody have an idea what might be happening?

    Thank you for the help (and sorry for the long message)
     
  2. Loading...

    Similar Threads Forum Date
    Advice On Situation New to Sikhism Aug 31, 2016
    Islam The Guru's Advice to Muslims Interfaith Dialogues Oct 8, 2015
    Do you believe in seeking help and advice on worldly matters from SGGS, via taking gur-vaaks ? Intellectual Articles May 19, 2015
    Marriage and Caste... Advice please Love & Marriage Mar 6, 2013
    Relationship advice Love & Marriage Sep 30, 2012

  3. Jen

    Jen
    Expand Collapse
    Guest

    Try linking from tblPartNumber equijoin to tblBomDetails, and from
    tblPartNumber left outer join to tblBom. Drop the tblBom to tblBomDetails
    link.

    Enjoy. j.

    "Access Sortof Newb" wrote:

    > Good day all,
    >
    > I am currently creating a part numbering/inventory/purchase ordering
    > database. Most of the database is working around the tblPartNumber
    > table. My problem is specifically with my bill of materials tables
    > (tblBom and tblBomDetails). Here are my relationships (as seen from the
    > "relationships" window in MS Access. "ID" suffix is an index and is the
    > same as the name of the table root (i.e. tblPartNumber's primary key is
    > PartNumberID) (ASIDE: use fixed font on google groups. For everyone
    > else I am sorry; I have no clue how to make a table look good in
    > usenet)
    >
    > left table right table relationship ID
    > tblPartNumber tblBom 1 to 1 PartNumberID
    > tblBom tblBomDetails 1 to many PartNumberID
    > tblPartNumber tblBomDetails 1 to many PartNumberID
    > tblPartType tblPartNumber 1 to many PartTypeID
    >
    > Here is the thing. A part number can be both a top level product that
    > has a bill of materials (BOM) or it can also be a component within the
    > BOM for that product. This is why the primary key of tblPartNumber is
    > related to both tblBom and tblBomDetails, since tblBomDetails holds the
    > actual parts that make up the top level assembly (the two relationships
    > to tblBomDetails are different fields in the table). The part type
    > table (tblPartType) is an identifier for the specific part number and
    > it can be anything like a screw, a nut, an electrical device etc.
    >
    > I have created a query that inner joins the tables tblBomDetails and
    > tblPartNumbers
    >
    > SQL statement:
    > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > tblBomDetails.PartNumberID
    > FROM tblPartNumber INNER JOIN tblBomDetails ON
    > tblPartNumber.PartNumberID = tblBomDetails.PartNumberID
    > ORDER BY tblPartNumber.PartTypeID;
    >
    > The SQL query works fine and shows the data I was expecting (First
    > column has repeated values of the BOM associated with the data in the
    > following columns and the remaining columns list the part number and
    > part type of the part number). Now, I want to create a report from this
    > data that will show the part number of the BOM at the form header, then
    > break down into part type, then part number. When I try to accomplish
    > this in the report wizard, I am unable to move past the first step in
    > the wizard, because as soon as I try to add PartTypeID to the report I
    > get an error stating "subscript out of range". I have looked over the
    > data in the tables and I do not see any reason as to why this is
    > arising. Does anybody have an idea what might be happening?
    >
    > Thank you for the help (and sorry for the long message)
    >
    >
     
  4. Access Sortof Newb

    Access Sortof Newb
    Expand Collapse
    Guest

    Is this the correct SQL statement for this operation? I removed the
    tblBom and tblBomDetails relationship as well:

    SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    tblBomDetails.PartNumberID
    FROM (tblPartNumber INNER JOIN tblBomDetails ON
    tblPartNumber.PartNumberID=tblBomDetails.PartNumberID) LEFT JOIN tblBom
    ON tblPartNumber.PartNumberID=tblBom.PartNumberID
    ORDER BY tblPartNumber.PartTypeID

    The query looks fine in datasheet view, but the report wizard is still
    flagging subscript out of range when PartTypeID is a member of the
    report.

    thanks again


    Jen wrote:
    > Try linking from tblPartNumber equijoin to tblBomDetails, and from
    > tblPartNumber left outer join to tblBom. Drop the tblBom to tblBomDetails
    > link.
    >
    > Enjoy. j.
    >
    > "Access Sortof Newb" wrote:
    >
    > > Good day all,
    > >
    > > I am currently creating a part numbering/inventory/purchase ordering
    > > database. Most of the database is working around the tblPartNumber
    > > table. My problem is specifically with my bill of materials tables
    > > (tblBom and tblBomDetails). Here are my relationships (as seen from the
    > > "relationships" window in MS Access. "ID" suffix is an index and is the
    > > same as the name of the table root (i.e. tblPartNumber's primary key is
    > > PartNumberID) (ASIDE: use fixed font on google groups. For everyone
    > > else I am sorry; I have no clue how to make a table look good in
    > > usenet)
    > >
    > > left table right table relationship ID
    > > tblPartNumber tblBom 1 to 1 PartNumberID
    > > tblBom tblBomDetails 1 to many PartNumberID
    > > tblPartNumber tblBomDetails 1 to many PartNumberID
    > > tblPartType tblPartNumber 1 to many PartTypeID
    > >
    > > Here is the thing. A part number can be both a top level product that
    > > has a bill of materials (BOM) or it can also be a component within the
    > > BOM for that product. This is why the primary key of tblPartNumber is
    > > related to both tblBom and tblBomDetails, since tblBomDetails holds the
    > > actual parts that make up the top level assembly (the two relationships
    > > to tblBomDetails are different fields in the table). The part type
    > > table (tblPartType) is an identifier for the specific part number and
    > > it can be anything like a screw, a nut, an electrical device etc.
    > >
    > > I have created a query that inner joins the tables tblBomDetails and
    > > tblPartNumbers
    > >
    > > SQL statement:
    > > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > > tblBomDetails.PartNumberID
    > > FROM tblPartNumber INNER JOIN tblBomDetails ON
    > > tblPartNumber.PartNumberID = tblBomDetails.PartNumberID
    > > ORDER BY tblPartNumber.PartTypeID;
    > >
    > > The SQL query works fine and shows the data I was expecting (First
    > > column has repeated values of the BOM associated with the data in the
    > > following columns and the remaining columns list the part number and
    > > part type of the part number). Now, I want to create a report from this
    > > data that will show the part number of the BOM at the form header, then
    > > break down into part type, then part number. When I try to accomplish
    > > this in the report wizard, I am unable to move past the first step in
    > > the wizard, because as soon as I try to add PartTypeID to the report I
    > > get an error stating "subscript out of range". I have looked over the
    > > data in the tables and I do not see any reason as to why this is
    > > arising. Does anybody have an idea what might be happening?
    > >
    > > Thank you for the help (and sorry for the long message)
    > >
    > >
     
  5. Access Sortof Newb

    Access Sortof Newb
    Expand Collapse
    Guest

    Is this the correct SQL statement for this operation? I removed the
    tblBom and tblBomDetails relationship as well:

    SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    tblBomDetails.PartNumberID
    FROM (tblPartNumber INNER JOIN tblBomDetails ON
    tblPartNumber.PartNumberID=tblBomDetails.PartNumberID) LEFT JOIN tblBom
    ON tblPartNumber.PartNumberID=tblBom.PartNumberID
    ORDER BY tblPartNumber.PartTypeID

    The query looks fine in datasheet view, but the report wizard is still
    flagging subscript out of range when PartTypeID is a member of the
    report.

    thanks again


    Jen wrote:
    > Try linking from tblPartNumber equijoin to tblBomDetails, and from
    > tblPartNumber left outer join to tblBom. Drop the tblBom to tblBomDetails
    > link.
    >
    > Enjoy. j.
    >
    > "Access Sortof Newb" wrote:
    >
    > > Good day all,
    > >
    > > I am currently creating a part numbering/inventory/purchase ordering
    > > database. Most of the database is working around the tblPartNumber
    > > table. My problem is specifically with my bill of materials tables
    > > (tblBom and tblBomDetails). Here are my relationships (as seen from the
    > > "relationships" window in MS Access. "ID" suffix is an index and is the
    > > same as the name of the table root (i.e. tblPartNumber's primary key is
    > > PartNumberID) (ASIDE: use fixed font on google groups. For everyone
    > > else I am sorry; I have no clue how to make a table look good in
    > > usenet)
    > >
    > > left table right table relationship ID
    > > tblPartNumber tblBom 1 to 1 PartNumberID
    > > tblBom tblBomDetails 1 to many PartNumberID
    > > tblPartNumber tblBomDetails 1 to many PartNumberID
    > > tblPartType tblPartNumber 1 to many PartTypeID
    > >
    > > Here is the thing. A part number can be both a top level product that
    > > has a bill of materials (BOM) or it can also be a component within the
    > > BOM for that product. This is why the primary key of tblPartNumber is
    > > related to both tblBom and tblBomDetails, since tblBomDetails holds the
    > > actual parts that make up the top level assembly (the two relationships
    > > to tblBomDetails are different fields in the table). The part type
    > > table (tblPartType) is an identifier for the specific part number and
    > > it can be anything like a screw, a nut, an electrical device etc.
    > >
    > > I have created a query that inner joins the tables tblBomDetails and
    > > tblPartNumbers
    > >
    > > SQL statement:
    > > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > > tblBomDetails.PartNumberID
    > > FROM tblPartNumber INNER JOIN tblBomDetails ON
    > > tblPartNumber.PartNumberID = tblBomDetails.PartNumberID
    > > ORDER BY tblPartNumber.PartTypeID;
    > >
    > > The SQL query works fine and shows the data I was expecting (First
    > > column has repeated values of the BOM associated with the data in the
    > > following columns and the remaining columns list the part number and
    > > part type of the part number). Now, I want to create a report from this
    > > data that will show the part number of the BOM at the form header, then
    > > break down into part type, then part number. When I try to accomplish
    > > this in the report wizard, I am unable to move past the first step in
    > > the wizard, because as soon as I try to add PartTypeID to the report I
    > > get an error stating "subscript out of range". I have looked over the
    > > data in the tables and I do not see any reason as to why this is
    > > arising. Does anybody have an idea what might be happening?
    > >
    > > Thank you for the help (and sorry for the long message)
    > >
    > >
     
  6. Jen

    Jen
    Expand Collapse
    Guest

    how are you using PartTypeID in the report? j.

    "Access Sortof Newb" wrote:

    > Is this the correct SQL statement for this operation? I removed the
    > tblBom and tblBomDetails relationship as well:
    >
    > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > tblBomDetails.PartNumberID
    > FROM (tblPartNumber INNER JOIN tblBomDetails ON
    > tblPartNumber.PartNumberID=tblBomDetails.PartNumberID) LEFT JOIN tblBom
    > ON tblPartNumber.PartNumberID=tblBom.PartNumberID
    > ORDER BY tblPartNumber.PartTypeID
    >
    > The query looks fine in datasheet view, but the report wizard is still
    > flagging subscript out of range when PartTypeID is a member of the
    > report.
    >
    > thanks again
    >
    >
    > Jen wrote:
    > > Try linking from tblPartNumber equijoin to tblBomDetails, and from
    > > tblPartNumber left outer join to tblBom. Drop the tblBom to tblBomDetails
    > > link.
    > >
    > > Enjoy. j.
    > >
    > > "Access Sortof Newb" wrote:
    > >
    > > > Good day all,
    > > >
    > > > I am currently creating a part numbering/inventory/purchase ordering
    > > > database. Most of the database is working around the tblPartNumber
    > > > table. My problem is specifically with my bill of materials tables
    > > > (tblBom and tblBomDetails). Here are my relationships (as seen from the
    > > > "relationships" window in MS Access. "ID" suffix is an index and is the
    > > > same as the name of the table root (i.e. tblPartNumber's primary key is
    > > > PartNumberID) (ASIDE: use fixed font on google groups. For everyone
    > > > else I am sorry; I have no clue how to make a table look good in
    > > > usenet)
    > > >
    > > > left table right table relationship ID
    > > > tblPartNumber tblBom 1 to 1 PartNumberID
    > > > tblBom tblBomDetails 1 to many PartNumberID
    > > > tblPartNumber tblBomDetails 1 to many PartNumberID
    > > > tblPartType tblPartNumber 1 to many PartTypeID
    > > >
    > > > Here is the thing. A part number can be both a top level product that
    > > > has a bill of materials (BOM) or it can also be a component within the
    > > > BOM for that product. This is why the primary key of tblPartNumber is
    > > > related to both tblBom and tblBomDetails, since tblBomDetails holds the
    > > > actual parts that make up the top level assembly (the two relationships
    > > > to tblBomDetails are different fields in the table). The part type
    > > > table (tblPartType) is an identifier for the specific part number and
    > > > it can be anything like a screw, a nut, an electrical device etc.
    > > >
    > > > I have created a query that inner joins the tables tblBomDetails and
    > > > tblPartNumbers
    > > >
    > > > SQL statement:
    > > > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > > > tblBomDetails.PartNumberID
    > > > FROM tblPartNumber INNER JOIN tblBomDetails ON
    > > > tblPartNumber.PartNumberID = tblBomDetails.PartNumberID
    > > > ORDER BY tblPartNumber.PartTypeID;
    > > >
    > > > The SQL query works fine and shows the data I was expecting (First
    > > > column has repeated values of the BOM associated with the data in the
    > > > following columns and the remaining columns list the part number and
    > > > part type of the part number). Now, I want to create a report from this
    > > > data that will show the part number of the BOM at the form header, then
    > > > break down into part type, then part number. When I try to accomplish
    > > > this in the report wizard, I am unable to move past the first step in
    > > > the wizard, because as soon as I try to add PartTypeID to the report I
    > > > get an error stating "subscript out of range". I have looked over the
    > > > data in the tables and I do not see any reason as to why this is
    > > > arising. Does anybody have an idea what might be happening?
    > > >
    > > > Thank you for the help (and sorry for the long message)
    > > >
    > > >

    >
    >
     
  7. Access Sortof Newb

    Access Sortof Newb
    Expand Collapse
    Guest

    It is connected to tblPartNumber as a one to many relationship.

    I decided to scrap the wizard and simply use the design interface.
    Everything worked out okay when I used it, so I think it may have been
    an issue with the wizard.

    Thank you for the help.

    Jen wrote:
    > how are you using PartTypeID in the report? j.
    >
    > "Access Sortof Newb" wrote:
    >
    > > Is this the correct SQL statement for this operation? I removed the
    > > tblBom and tblBomDetails relationship as well:
    > >
    > > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > > tblBomDetails.PartNumberID
    > > FROM (tblPartNumber INNER JOIN tblBomDetails ON
    > > tblPartNumber.PartNumberID=tblBomDetails.PartNumberID) LEFT JOIN tblBom
    > > ON tblPartNumber.PartNumberID=tblBom.PartNumberID
    > > ORDER BY tblPartNumber.PartTypeID
    > >
    > > The query looks fine in datasheet view, but the report wizard is still
    > > flagging subscript out of range when PartTypeID is a member of the
    > > report.
    > >
    > > thanks again
    > >
    > >
    > > Jen wrote:
    > > > Try linking from tblPartNumber equijoin to tblBomDetails, and from
    > > > tblPartNumber left outer join to tblBom. Drop the tblBom to tblBomDetails
    > > > link.
    > > >
    > > > Enjoy. j.
    > > >
    > > > "Access Sortof Newb" wrote:
    > > >
    > > > > Good day all,
    > > > >
    > > > > I am currently creating a part numbering/inventory/purchase ordering
    > > > > database. Most of the database is working around the tblPartNumber
    > > > > table. My problem is specifically with my bill of materials tables
    > > > > (tblBom and tblBomDetails). Here are my relationships (as seen from the
    > > > > "relationships" window in MS Access. "ID" suffix is an index and is the
    > > > > same as the name of the table root (i.e. tblPartNumber's primary key is
    > > > > PartNumberID) (ASIDE: use fixed font on google groups. For everyone
    > > > > else I am sorry; I have no clue how to make a table look good in
    > > > > usenet)
    > > > >
    > > > > left table right table relationship ID
    > > > > tblPartNumber tblBom 1 to 1 PartNumberID
    > > > > tblBom tblBomDetails 1 to many PartNumberID
    > > > > tblPartNumber tblBomDetails 1 to many PartNumberID
    > > > > tblPartType tblPartNumber 1 to many PartTypeID
    > > > >
    > > > > Here is the thing. A part number can be both a top level product that
    > > > > has a bill of materials (BOM) or it can also be a component within the
    > > > > BOM for that product. This is why the primary key of tblPartNumber is
    > > > > related to both tblBom and tblBomDetails, since tblBomDetails holds the
    > > > > actual parts that make up the top level assembly (the two relationships
    > > > > to tblBomDetails are different fields in the table). The part type
    > > > > table (tblPartType) is an identifier for the specific part number and
    > > > > it can be anything like a screw, a nut, an electrical device etc.
    > > > >
    > > > > I have created a query that inner joins the tables tblBomDetails and
    > > > > tblPartNumbers
    > > > >
    > > > > SQL statement:
    > > > > SELECT tblBomDetails.BomID, tblPartNumber.PartTypeID,
    > > > > tblBomDetails.PartNumberID
    > > > > FROM tblPartNumber INNER JOIN tblBomDetails ON
    > > > > tblPartNumber.PartNumberID = tblBomDetails.PartNumberID
    > > > > ORDER BY tblPartNumber.PartTypeID;
    > > > >
    > > > > The SQL query works fine and shows the data I was expecting (First
    > > > > column has repeated values of the BOM associated with the data in the
    > > > > following columns and the remaining columns list the part number and
    > > > > part type of the part number). Now, I want to create a report from this
    > > > > data that will show the part number of the BOM at the form header, then
    > > > > break down into part type, then part number. When I try to accomplish
    > > > > this in the report wizard, I am unable to move past the first step in
    > > > > the wizard, because as soon as I try to add PartTypeID to the report I
    > > > > get an error stating "subscript out of range". I have looked over the
    > > > > data in the tables and I do not see any reason as to why this is
    > > > > arising. Does anybody have an idea what might be happening?
    > > > >
    > > > > Thank you for the help (and sorry for the long message)
    > > > >
    > > > >

    > >
    > >
     

Share This Page