Hi,

I have 2 tables, the first is called Services and the 2nd is called Hardware Orders.

The relationship between these tables is that they contain something called a TNE Number which, if equal, matches hardware to services.

The problem I have however is linking the tables and allowing updates.

I basically have * from Hardware Orders in the query and all I need is a date from the Services table to be shown with the TNE number as the 'common link'.

I cant seem to find a way to build a query / relationship to show this AND allow updates via a form. If I build it with a link between the TNE numbers I cannot update recordsets.

Neither of these can be PKs as they can be duplicated etc.

Help please.

Thanks

To make it updateable you need to have the field in the child table a primary key and used as the linked field back to the FK in the master table.

David

To make it updateable you need to have the field in the child table a primary key and used as the linked field back to the FK in the master table.

David

Thanks for the info but I cannot use the TNE number in the child table as this is not always unique. For a PK, this is a must iirc?

For example, I might have in t_hardware_orders (tying to draw a table, bear with me)

ID | Hardware | Value | TNE | Other info

1, Antenna, £100, 12455, Blah
2, Radio, £200, 12455, Blah more
3, Antenna, £100, 12765, Blah etc

The t_services table has 'other' info about the services element, all revolving around TNE numbers.

TNE numbers are not unique as we capture multiple line items / services per TNE.

Thanks for any help again ....

Thanks for the info but I cannot use the TNE number in the child table as this is not always unique. For a PK, this is a must iirc?

You are incorrect. The PRIMARY key is for the main table and then that primary key, stored with records in the OTHER table is called the Foreign Key. You link the Primary from the main table into the FOREIGN key of the child table.

You are incorrect. The PRIMARY key is for the main table and then that primary key, stored with records in the OTHER table is called the Foreign Key. You link the Primary from the main table into the FOREIGN key of the child table.

Sorry, am getting confused.

I understand tables, relationships and PF / FK (at least I think I do).

Both my tables have a Primary Key field.

What you are saying is that my child table needs to link to my parent table with the child's FK? Ill try that - did not think that would work as I do not see the association between ID fields - how does the system know which records belong to which?

Ill try that now quick and see if I can get it to work.

Thanks once again.

Sorry, am getting confused.

I understand tables, relationships and PF / FK (at least I think I do).

Both my tables have a Primary Key field.

What you are saying is that my child table needs to link to my parent table with the child's FK? Ill try that - did not think that would work as I do not see the association between ID fields - how does the system know which records belong to which?

Ill try that now quick and see if I can get it to work.

Thanks once again.

I think you are misunderstanding what a primary key and foreign key are used for. Here's a simplified example:

TABLE - Orders (MASTER TABLE)
OrderID - Primary Key
Order Date
EmployeeID

TABLE - Order Details (CHILD TABLE)
OrderDetailID - Primary Key
OrderID - Foreign Key
etc..

Then when you want the order details for a specific order you link from OrderID in the Orders table to the OrderID in the OrderDetails table which gives you all order details for a specific order.

Please see attached screeny. If I do the relationship as shown in the Red line I get no records shown.

Ive tried, with the TNE to TNE relationship different join types with no change in result (some useless info - just to explain).

I think you are misunderstanding what a primary key and foreign key are used for. Here's a simplified example:

TABLE - Orders (MASTER TABLE)
OrderID - Primary Key
Order Date
EmployeeID

TABLE - Order Details (CHILD TABLE)
OrderDetailID - Primary Key
OrderID - Foreign Key
etc..

Then when you want the order details for a specific order you link from OrderID in the Orders table to the OrderID in the OrderDetails table which gives you all order details for a specific order.

Thanks for the info again - will try at home this evening and see. Ive been away from the db design for a while and suspect a bit of rust creeping in.

Boblarson, I understand now what you mean - the issue is that without data in the FK field in the child table, there are no results shown. This data would have to be populated to connect records together?

Perhaps a more complex query could be written that does a search for common TNE numbers (thats the key, excuse the pun) and then almost 'link' these together.

I can see a lot of copy / paste or something here for myself if there is no method to 'auto link' 1200 records.

Thanks once again.

If TNE numbers are the same then you can link them together instead of another field, for query purposes. You will get one record for each on the many side and the same TNE number many times on the side where there is only one as it will show up the same for every record in the child table that has a match.

If TNE numbers are the same then you can link them together instead of another field, for query purposes. You will get one record for each on the many side and the same TNE number many times on the side where there is only one as it will show up the same for every record in the child table that has a match.

Apologies for the late reply but when I link them together via the TNE number I am back to the issue that the recordset cannot be updated.

I can try and upload a demo database to show what the story is. I need now to connect these together as more info is being asked for which I can only do once this is sorted.

Strongly considering, if I dont get this right - to try and merge the tables but thats not the 'right way' (IMHO).

Apologies for the late reply but when I link them together via the TNE number I am back to the issue that the recordset cannot be updated.

I can try and upload a demo database to show what the story is. I need now to connect these together as more info is being asked for which I can only do once this is sorted.

Strongly considering, if I dont get this right - to try and merge the tables but thats not the 'right way' (IMHO).
An upload may help.

Trying to upload but getting errors. Ill try again in a short while - Ive stripped down my db to just 2 tables, 1 query and a form so demonstrate what I am trying to do.

If TNE numbers are the same then you can link them together instead of another field, for query purposes. You will get one record for each on the many side and the same TNE number many times on the side where there is only one as it will show up the same for every record in the child table that has a match.

Think Ive got it - basically the statement above - not sure why I didnt get it to work prior. Using join option 3 via the GUI - just testing now to see if it is correct.

Back to square 1 - as soon as I query across two tables I get the recordset error. I can make this work with 2 different forms / queries - one for input and one for cross referencing but it does not really work / help.

I use this statement to base my form on - recordset cannot be updated.

SELECT t_hardware_orders.ID, t_hardware_orders.HW_Path_Number, t_hardware_orders.HW_Date_Ordered, t_hardware_orders.HW_Description, t_hardware_orders.HQ_Qty, t_hardware_orders.HW_Expected_Delivery_Date, t_hardware_orders.HW_Each_Value, t_hardware_orders.HW_Value, t_hardware_orders.HW_Actual_Receive_Date, t_hardware_orders.HW_Quarenteen_YN, t_hardware_orders.HW_Location, t_hardware_orders.HW_PO_Number, t_hardware_orders.HW_TNE_Number, t_hardware_orders.HW_Comments, t_hardware_orders.HW_ATI_Number, t_hardware_orders.HW_Invoice_Number, t_hardware_orders.HW_Invoice_Date, t_hardware_orders.HW_ISO_Number, t_services.Services_TNE_Number, t_services.Services_Actual_Install_Date
FROM t_services INNER JOIN t_hardware_orders ON t_services.Services_TNE_Number = t_hardware_orders.HW_TNE_Number

Doesnt matter what join type I use. I have no relationship in the backend - I only do a join here.

Think its fixed, getting tired of talking to myself anyways :-)

FROM t_services RIGHT JOIN t_hardware_orders ON t_services.Services_TNE_Number = t_hardware_orders.ID;

Slight change on the relationship in the query ...

Bubble burst. The data doesnt match .... It works in the sense of no errors but there is no direct correlation - will play with the relationships again ...

Look at this (http://allenbrowne.com/ser-61.html) and see if this helps.

Database error
The Access World Forums database has encountered a problem.
Please try the following:
Load the page again by clicking the Refresh button in your web browser.
Open the www.access-programmers.co.uk home page, then try to open another page.
Click the Back button to try another link.
The www.access-programmers.co.uk forum technical staff have been notified of the error, though you may contact them if the problem persists.

We apologise for any inconvenience.

Argh, trying to upload a stripped down version of the database ....

Ill see if I can upload it somewhere else.

Look at this (http://allenbrowne.com/ser-61.html) and see if this helps.

Thanks - going through that now - still 'noob' with database stuff but a few things there that make sense to check.

Hopefully this image shows what I am trying to do.

The purple line shows what I am trying to 'relate' to with conditions in the text. In hindsight, perhaps the TNE number should be in its own table?

Got the data to work.

I created a new table that just contains unique TNE numbers as a Primary Key which is then in a relationship with both the hardware and services Foreign Keys but still get the recordset cannot be updated error - at least the data is correct now which helps.

Finally - query and form working.

Had to set Record set properties for both to: Dynaset (Inconsistent Updates)

Any other settings and no go. Ill double check but that seems to have done it. I went through the list one by one from the page (many thanks Bob) until I played with that. Will now see if I can figure why / what that does.