Possibly error in query

[Jesper Lützen]

I've created a minor DB to register what my company buys and for who (Clothes
etc). In the DB I've got "Brands" i one table containing ID and Description,
and "Catalogue" in another table containing Id, EmployeeId, BrandId, and
Price. The Brands are grapped from the Brand table without a problem, but
when the data is saved in my formula, the result is, that the Brand ID is
saved as a new entry (with default ID) and with the selected ID as BrandName.
It is not at all supposed to make a new entry in the Brand table, but only in
the Catalogue table.

Help, please..?

 

[sherwin verdun]

are you making a relational database?

 

[Jesper Lützen]

are you making a relational database?

I have defined the relations between the tables, so I guess so, yes.

 

[Jeff Boyce]

We can't see your "formula". Are you working directly in the tables, or via
forms? Why is a field named [EmployeeID] included in a table named
"Catalogue"? How have you related these tables? What data types are the
[ID] fields in each table?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jeff Boyce]

Jesper

Thank you for the additional description. Please see my comments in-line
below...

The tables are as follows:
Table: Brands
BrandId (auto nb. - primary key)
BrandName (txt)

Table: EmployeeCategory
EmployeeTypeId (auto nb. - primary key)
EmployeeTypeName (txt)

Table: Employees
EmployeeId (auto nb. - primary key)
EmployeeName (txt)
EmployeeCategory (number - lookup from table EmployeeCategory)

If you mean the EmployeeCategory field in the Employees table is set as a
"lookup" data type, you may find it problematic. Check the tablesdbdesign
newsgroup for additional comments about "lookup" data types. The primary
issue, for me, is that this field shows one thing, but stores another. This
can cause considerable confusion. For example, in a query against this
table, what are you looking for, the ID or the text value?!

Table: Catalogue
ItemId (auto nb. - primary key)
ItemBrand (number - lookup from table Brands
ItemDescription (txt)

Again, if ItemBrand is a lookup data type, this can cause problems and will
cause confusion.

Table: Buys
BuyId (auto nb. - primary key)
EmployeeId (number - lookup from table Employees)
ItemId (number - lookup from table Catalogue)
Size (txt)
BuyDate (date)
Price (currency)

The purpose of the database is, to register clothing items for the employees
buyed by the company. All the "lookup" relations has been set between the
tables.

Once more, if you are working directly in the tables (not a good idea,
that's what Forms are for), and are using lookup data types, you may be
running into problems related to that data type. The more common approach
is to NOT use lookup data types, and to use combo boxes (or list boxes) in
forms to perform the looking up.

Updating the tables happens in formulas, and they all work except the
"catalogue"-formula, which is supposed to update the "catalogue" table with
new entries. Unfortunately it ALSO updates the "Brand" table as previously
descriped.

I don't understand what "formulas" you are using, nor where you have these
formulas. From your description, a simpler and more common approach might
be to use Forms for data entry/edit/display.

The data selection is no problem, so my guess is, that one of the many other
settings for the box is the problem, but I have no idea of what I am looking
for.

I'm not there, so I have no idea what "box" you are referring to.

Is this enough for anyone to be able to guess why this error occurs?

Thank
Jesper

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/