Normalisation and Data Retrieval

[VT]

Hello

I need a little help retrieving data for a subform. The data to retrieve
requires a piece of data from the main form and a piece from a row of the
subform.

All our products need to be licenced before being sold overseas. The
licences are identified by our government by a five figure reference. Every
licence may cite more than one product, and every licence may cite more than
one country, e.g. licence number 12345 may permit sales of Product 1 and
Product 3 to France, Belize and Ireland.

For this reason I have a licences table, products table and a countries
table. There are also two junction tables, ProductLicences and
CountryLicences.

I also have an invoice form with an invoice details subform.There is a
CountryID field on the main form in which the user adds in the country to
which the goods are heading. Each row of the subform identifies individual
products.

What I need is to retrieve and display a Licence number appropriate for the
product in every row of the invoice details subform, and that licence number
must also be appropriate for the country identified in the main form.

If we don't have a licence covering the sale of that product to that
country, then we cannot sell the product to that country.

How can I do this? Will I need to use a subquery, given that five tables are
involved, thanks to normalisation?

Thank you in advance.

 

[KARL DEWEY]

Seems to me the simplest way would be to have a table with a compound key
consisting of license, product, and country.

 

[VT]

Hi Karl

I tried something like that, and added it to the query underlying invoice
details, but it was always non-updateable.

VT

 

[KARL DEWEY]

Post that query.

 

[VT]

Hi Karl

It looks like this. For "Clearances", read licences.
"ApplicableCountryClearances" is a query that has three columns: Country,
Clearance, Product. The main form is "Transaction Form", the subform is
"Transaction Details".

SELECT [Transaction Details].DocumentNo, [Transaction Details].ProductID,
Products.ProductName, [Transaction Details].AssetID, DemoStock.SerialNumber,
[Transaction Details].Value, [Transaction Details].Quantity,
ApplicableCountryClearances.ClearanceID
FROM (DemoStock INNER JOIN ([Transaction Details] INNER JOIN Products ON
[Transaction Details].ProductID = Products.ProductID) ON DemoStock.AssetID =
[Transaction Details].AssetID) INNER JOIN ApplicableCountryClearances ON
Products.ProductID = ApplicableCountryClearances.ProductID
WHERE ((([ApplicableCountryClearances]![ShipCountryID])=[Forms]![Transaction
Form]![cboShipCountry]));

This query is not updateable so it cannot be used for data entry.

Sorry if it looks like a mess, I have maybe two hours a week set aside for
this DB and I'm not an expert.

 

[KARL DEWEY]

Try changing the INNER joins to LEFT joins.

--
KARL DEWEY
Build a little - Test a little

Hi Karl

It looks like this. For "Clearances", read licences.
"ApplicableCountryClearances" is a query that has three columns: Country,
Clearance, Product. The main form is "Transaction Form", the subform is
"Transaction Details".

SELECT [Transaction Details].DocumentNo, [Transaction Details].ProductID,
Products.ProductName, [Transaction Details].AssetID, DemoStock.SerialNumber,
[Transaction Details].Value, [Transaction Details].Quantity,
ApplicableCountryClearances.ClearanceID
FROM (DemoStock INNER JOIN ([Transaction Details] INNER JOIN Products ON
[Transaction Details].ProductID = Products.ProductID) ON DemoStock.AssetID =
[Transaction Details].AssetID) INNER JOIN ApplicableCountryClearances ON
Products.ProductID = ApplicableCountryClearances.ProductID
WHERE ((([ApplicableCountryClearances]![ShipCountryID])=[Forms]![Transaction
Form]![cboShipCountry]));

This query is not updateable so it cannot be used for data entry.

Sorry if it looks like a mess, I have maybe two hours a week set aside for
this DB and I'm not an expert.

Post that query.

 

[VT]

I tried that, but the query is still not updateable.

Try changing the INNER joins to LEFT joins.

..

 

[KARL DEWEY]

I would suggest to make a copy, start taking it apart (first remove WHERE
statement) removing a table/query at a time and test it to see what needs to
be omitted to make it work.