Windows 7 Pro
Access 2007
(Multiple machines tested, with same configuration)
I am designing a database to hold current and previous data about a large number of products.
I have a main table called 'Article' which has an AutoNumber (ArticleUID) and a 'IsDeleted' boolean field.
There are then 30 or so tables which each pertain to a different characteristic of the product. so Art_Barcode, Art_RefCode, Art_Brand, Art_Description, Art_Price, etc etc.
The structure of these tables is identical really.
UID (AutoNumber)
ArticleUID (Number)
Value (Text)
StartDate (Date)
OldUID (Number)
NewUID (Number)
OldUID means the previous entry (i.e. a previous price), or the same UID as the current record if it is the first one. NewUID is the next price when a price change is implemented. Or NULL if it is the latest price. It doesn't matter about storing the price as text at this point.
When I run a query which has 'Value' and 'NewUID' (criteria: is Null) from each of the 30 tables, access just crashes. SQL below. Sorry about the german.
Access 2007
(Multiple machines tested, with same configuration)
I am designing a database to hold current and previous data about a large number of products.
I have a main table called 'Article' which has an AutoNumber (ArticleUID) and a 'IsDeleted' boolean field.
There are then 30 or so tables which each pertain to a different characteristic of the product. so Art_Barcode, Art_RefCode, Art_Brand, Art_Description, Art_Price, etc etc.
The structure of these tables is identical really.
UID (AutoNumber)
ArticleUID (Number)
Value (Text)
StartDate (Date)
OldUID (Number)
NewUID (Number)
OldUID means the previous entry (i.e. a previous price), or the same UID as the current record if it is the first one. NewUID is the next price when a price change is implemented. Or NULL if it is the latest price. It doesn't matter about storing the price as text at this point.
When I run a query which has 'Value' and 'NewUID' (criteria: is Null) from each of the 30 tables, access just crashes. SQL below. Sorry about the german.
Code:
SELECT Art_LiefArtNr.LiefArtNr, Art_RefNr.RefNr, Art_PH.PH, Art_Bezeichnung.Bezeichnung, Art_Marke.Marke, Art_Warengruppe.Warengruppe, Art_Kollektion.Kollektion, Art_Artikelart.Artikelart, Art_Unterkat.Unterkategorie, Art_Produktlinie.Produktlinie, Art_Steine.Steine, Art_Grosse.Grosse, Art_BestellInfo.BestellInfo, Anz_Fields.Field16, Art_Verpak.VerpakEinheit, Anz_Fields.Field18, Art_Gewicht.Gewicht, Anz_Fields.Field20, Anz_Fields.Field23, Anz_Fields.Field24, Anz_Fields.Field25, Anz_Fields.Field26, Anz_Fields.Field27, Anz_Fields.Field28
FROM Anz_Fields INNER JOIN (((((((((((((((DB_Artikel INNER JOIN Art_LiefArtNr ON DB_Artikel.ArtUID = Art_LiefArtNr.ArtUID) INNER JOIN Art_RefNr ON DB_Artikel.ArtUID = Art_RefNr.ArtUID) INNER JOIN Art_Bezeichnung ON DB_Artikel.ArtUID = Art_Bezeichnung.ArtUID) INNER JOIN Art_Marke ON DB_Artikel.ArtUID = Art_Marke.ArtUID) INNER JOIN Art_PH ON DB_Artikel.ArtUID = Art_PH.ArtUID) INNER JOIN Art_Produktlinie ON DB_Artikel.ArtUID = Art_Produktlinie.ArtUID) INNER JOIN Art_Kollektion ON DB_Artikel.ArtUID = Art_Kollektion.ArtUID) INNER JOIN Art_Steine ON DB_Artikel.ArtUID = Art_Steine.ArtUID) INNER JOIN Art_Warengruppe ON DB_Artikel.ArtUID = Art_Warengruppe.ArtUID) INNER JOIN Art_Artikelart ON DB_Artikel.ArtUID = Art_Artikelart.ArtUID) INNER JOIN Art_BestellInfo ON DB_Artikel.ArtUID = Art_BestellInfo.ArtUID) INNER JOIN Art_Unterkat ON DB_Artikel.ArtUID = Art_Unterkat.ArtUID) INNER JOIN Art_Gewicht ON DB_Artikel.ArtUID = Art_Gewicht.ArtUID) INNER JOIN Art_Grosse ON DB_Artikel.ArtUID = Art_Grosse.ArtUID) INNER JOIN Art_Verpak ON DB_Artikel.ArtUID = Art_Verpak.ArtUID) ON Anz_Fields.ArtUID = DB_Artikel.ArtUID
WHERE (((Art_LiefArtNr.NeuUID) Is Null) AND ((Art_RefNr.NeuUID) Is Null) AND ((Art_Bezeichnung.NeuUID) Is Null) AND ((Art_Marke.NeuUID) Is Null) AND ((Art_PH.NeuUID) Is Null) AND ((Art_Produktlinie.NeuUID) Is Null) AND ((Art_Kollektion.NeuUID) Is Null) AND ((Art_Steine.NeuUID) Is Null) AND ((Art_Warengruppe.NeuUID) Is Null) AND ((Art_Artikelart.NeuUID) Is Null) AND ((Art_BestellInfo.NeuUID) Is Null) AND ((Art_Unterkat.NeuUID) Is Null) AND ((Art_Grosse.NeuUID) Is Null) AND ((Art_Gewicht.NeuUID) Is Null) AND ((Art_Verpak.NeuUID) Is Null));
