Where Have I Gone Wrong!!

[scouser]

I made a number of changes to a DB that was working fine (normalised further). So what's wrong? I have a number of forms (for my sample DB I have stripped down to just one, if I can resolve for this I can apply to others).

qryENTRY_COMPUTERS
ENTRY COMPUTER FORM

The user adds a new computer then selects the Computer Make (qryLOOKUP_COMPUTER_SUPPLIER) & Computer Model (qryLOOKUP_COMPUTER_MODELS).

This now returns error 'recordset is not updateable'.

The query on which the form is based is incorrect (qryENTRY_COMPUTERS). Previously I was able to add tblSupplier & tblSupplierProducts to the query then add field ProductDescription fom tblSupplierProducts to the query. This allows users to select a value from cboComputerModelID (ProductID), field ProductDescription then displays the related descriptive value for the ProductID selected (value remain on screen when user naviagtes between records.

I have attached a sample database (access 2002-2003 format).

I am sure the answer is staring me in the face but I have now spent hours trying to resolve!!

Help very much appreciated.
Phil.

Update: One difference I can see is that on the query 'qryENTRY_COMPUTERS' where i have added tblSupplierProducts & tblSupplier the join properties are in reverse i.e. Left Table Name = tblSupplierProducts Rigth Table Name = tblComputers Left Column Name = ProductID Right Column Name = ProductID ????

I can't edit this.......................

 

[scouser]

Anything Obvious?

Guys can anyone see an obvious error regards what I have done? Really miffed?
Thanks,
Phil.

 

[scouser]

tblSupplier & tblSupplierProducts

The issue is caused by the addition of tblSupplierProducts to any relationship within a query.

I can happily relate tblComputers & tblSuppliers in a query. If I had tblSupplierProducts in order to capture the ProductDescription then it fails to work?

Hope someone out there can help on this one.
Thanks,
Phil.

 

[Rabbie]

This link may help you with this issue. Hope it helps

 

[scouser]

Reply

Thanks for the reply + link. I have had a look but nothing jumps out. Had a look at the query properties for both databases (one that works, one that doe not). The settings are the same. I belive the indexing to be OK.The Record Set Type is Dynaset.

JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

This works as stated previously by removing tblSupplierProducts from the query. However I need this to display the ProductDescription.

Any further thoughts?
Thanks,
Phil.

 

[scouser]

Another Thought

In the past when I created qryENTRY_COMPUTERS then added tables tblSupplier & tblSupplierProducts relationships were automatically created between tblComputers - tblSupplier (SupplierID) and tblComputers - tblSupplierProducts (ProductID)

SupplierID FK tblComputers
ProductID FK tblComputers

I would then remove the relationship between tblComputers & tblSuppliers else the filter would display 2500 recors as opposed to 96.

I am doing things but not understanding how or why they work as they do (fingers crossed!!).

Hope someone can resolve as I am struggling BIG TIME.
Thanks,
Phil.

PS: Is there a probel with the relationship between tblSupplier & tblSupplierProducts?

 

[neileg]

I haven't checked this out, but I believe it's because you have the SupplierID in tblComputers. It shouldn't be there because it's an attribute of the product.

Alternatively, if the same product can be sourced from several suppliers, then it is an attribute of the computer, but not an attribute of the product.

 

[scouser]

Reply

Thanks Neil. I will remove and test. I had a similar setup in the system that worked. Instead of tblSuppliers I had tblComputerMakes instead of tblSupplierProducts I had tblModels.

tblComputers
ComputerID PK
ComputerMakeID FK
ComputerModelID FK

tblComputerMakes
MakeID PK

tblComputerModels
ModelID PK
MakeID FK

When I review tblComputers it returns the computer ID (PC-001) + the Make (Hewlett Packard) + the Model (DC7600).

That's why I had SupplierID. If it's not needed I will refine.
Phil.

 

[scouser]

Still a Problem

I removed SupplierID from tblComputers.

Linked tblComputers to tblSupplierProducts (ProductID). Same issue so back to where we were.

Any ideas?
Thanks,
Phil.

 

[Simon_MT]

Scouser,

What version of Access are you running as I remember I had a similar problem with Access 97.

Simon

 

[neileg]

I did this and it worked:
In tblSupplierProducts set ProductID as the primary key
In the relationships diagram, remove the link between tblSupplier and tblComputers and create a link between tblProductSuppliers and tblComputers on ProductID

 

[scouser]

Re-Try

Neil I will retry and post back.
Many Thanks,
Phil.

PS: Access 2002-2003 format

 

[scouser]

Working

Neil it is now working!!

I have retained the SupplierID with tblComputers but have linked via ProductID.

So why is this relationship required?

I developed with the following in mind:

tblSuppliers as the parent table tblSupplierProducts as the child.

A Supplier (i.e. company that manufacturers the product, Microsoft / HP / Dell) can supply 1 or many products. A product (Windows XP / Precison 650 / nx6400) is supplied by 1 supplier.

A computer Model has both a Supplier (HP) and a Product ID (nx6400) (as does hardware & software.

Hope I make sense!!

Any comments?
Thanks,
Phil.

 

[neileg]

Neil it is now working!!

I have retained the SupplierID with tblComputers but have linked via ProductID. So why is this relationship required?

I think the problem arose from a conflict between the relationships in the relationship diagram and in the query. Plus, a non-updateable query arises where Access can't identify uniquely which record in each table is included in the data in the query. Probably this needed the primary key to be defined as I suggested.

I developed with the following in mind:
tblSuppliers as the parent table tblSupplierProducts as the child.
A Supplier (i.e. company that manufacturers the product, Microsoft / HP / Dell) can supply 1 or many products. A product (Windows XP / Precison 650 / nx6400) is supplied by 1 supplier.
A computer Model has both a Supplier (HP) and a Product ID (nx6400) (as does hardware & software.

That means that the supplier is an attribute of the product, not the computer. You are storing the supplier in two different places and the one in tblComputers should not be there.

Hope I make sense, too!!

 

[scouser]

Makes Sense

Neil you make perfect sense. I can remove SupplierID across multiple tables.
The ProductID (PK) was an oversight. I have been playing about and did not set it up after re-naming the table + fields!!
Many Thanks,
Phil.

PS: I have a more challenging problem regards how license allocations are counted based on whether they are part of a license agreement but that is another post altogether!! So if you like a challenge watch this space!!

 

[scouser]

Me Again

That means that the supplier is an attribute of the product, not the computer.

Just so I am clear. I have table(s) that store information about all computers / hardware / software etc..on the network (tblComputers - tblHardware - tblSoftware).

I will use tblComputers as my example here. To eliminate duplication I created additional tables to store information about RAM / Operating System / Manufacturer (Supplier) / Model (Product Description). I then needed create relationships (obviously!!). So I added FK's to tblComputers:
SupplierID
ProductID
OperatingSystemID
RAMID

So when I looked at tblComputers I could see that it was called PC001, it was manufactured by SupplierID 1 (HP) and was ModelID 10 (nx6400). By removing SupplierID I now see that computer PC001 is ModelID 10 (nx6400).

I thought it would have been good to know the manufacturer (supplierID).

Anyhows I can do that via a query I know.......but you can see where I am coming from??
Thanks,
Phil.

 

[neileg]

I can see where you are coming from, it's just that it's the wrong way to do it. Tables are for storing data, not displaying it. You should view your data in a form and the form should be based on a query. So you bring in the the supplier information in this query because it is linked by the product.

If you followed your logic to it's extreme, you would have all of your data in a single table with lots of duplications. That's the spreadsheet approach. In a relational database, you normalise the data so you hold it only once. This makes life easier to maintain the data. If you hold the same data in two places and they end up being different, how do you know which one is correct?

 

[scouser]

Makes Sense

Thanks Neil.

I obviously have to have a relationship between tables, just wondered why ProductID is the preferred attribute in tblComputers and not SupplierID. I presume this is because you said SupplierID is an attribute of tblSupplierProducts and not tblComputers (although I can't seem to my get my head round it!!). Why is ProductID an attribute of tblComputers? I know I need one of them for the relationship!!

Thanks for both your time and your patience. I developed the DB with basic understanding (although this has improved) I am still not familiar with many aspects (SQL - Query Joins etc....).
Phil.

 

[scouser]

Makes Sense

Thanks Neil.

I obviously have to have a relationship between tables, just wondered why ProductID is the preferred attribute in tblComputers and not SupplierID. I presume this is because you said SupplierID is an attribute of tblSupplierProducts and not tblComputers (although I can't seem to my get my head round it!!). Why is ProductID an attribute of tblComputers? I know I need one of them for the relationship!!

Thanks for both your time and your patience. I developed the DB with basic understanding (although this has improved) I am still not familiar with many aspects (SQL - Query Joins etc....).
Phil.

 

[neileg]

How do I know who is the supplier of the computer on my desk? I can see that it's an Optiplex GX520. Where do I buy an Optiplex GX520? From Dell.

In fact, since I didn't physically buy the computer I don't know where it was bought from. I can work it out because the only supplier for that machine is Dell, so my employer must have bought my computer from Dell. So the only piece of information I need to store about the computer is the product type. Storing the supplier ID is redundant data.