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

[Access Sortof Newb]

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)

 

[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]

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

 

[Access Sortof Newb]

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

 

[Guest]

how are you using PartTypeID in the report? j.

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

 

[Access Sortof Newb]

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.