Prev: Query Joins
Next: Time format in Query not updating to hh:nn Short Time
From: Jerry Whittle on 9 Feb 2010 12:44
In which tables is the SKU the primary key? To completely eliminate the
possibility that there could be a cartesian product, both the [tblFinished
Products].SKU and [tblBatchSheet-subtable].SKU fields would need to be PKs.
That would necessitate a one-to-one relationship.

Ideally the [tblFinished Products].SKU field is the primary key and the
[tblBatchSheet-subtable].SKU is the foreign key fields. A nice 1-M
relationship.

Then you would link the tblBatchSheet-subtable PK to a FK in
tblBatchSheet2-subtable. Unless the first two tables are in a 1-1
relationship, the SKU will not be the PK-FK combination joining the two
subtables.

From what I see either your tables are set up incorrectly or you are joining
the two subtables incorrectly. If the data flows down from tblFinished
Products to tblBatchSheet-subtable to tblBatchSheet2-subtable, then table
tblBatchSheet2-subtable shouldn't even have a SKU field in it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"tasha" wrote:

> I'm building a query using [tblFinished Products].SKU, [tblBatch
> Sheet-subtable].Batter, and [tblBatch Sheet2-subtable].Batter.
>
> If I use the query builder to create the following statement I get a
> cartesian result:
> SELECT [tblFinished Products].SKU, [tblBatchSheet-subtable].Batter,
> [tblBatchSheet2-subtable].Batter
> FROM ([tblFinished Products] INNER JOIN [tblBatchSheet-subtable] ON
> [tblFinished Products].SKU = [tblBatchSheet-subtable].SKU) INNER JOIN
> [tblBatchSheet2-subtable] ON [tblBatchSheet-subtable].SKU =
> [tblBatchSheet2-subtable].SKU;
>
> I've also tried buiding the query to use the following statement but I still
> get a cartesian result:
> SELECT [tblFinished Products].SKU, [tblBatchsheet-subtable].Batter,
> [tblBatchSheet2-subtable].Batter
> FROM ([tblFinished Products] INNER JOIN [tblBatchSheet-subtable] ON
> [tblFinished Products].SKU = [tblBatchSheet-subtable].SKU), ([tblFinished
> Products] INNER JOIN [tblBatchSheet2-subtable] ON [tblfinished Products].SKU
> = [tblBatchSheet2-subtable].SKU)
> WHERE (([tblBatchShet-subtable] = True AND [tblBatchSheet2-subtable] = True))
> ORDER BY [tblFinished Products]
>
> Can anyone please help me???
>
 | 
Pages: 1
Prev: Query Joins
Next: Time format in Query not updating to hh:nn Short Time