Access Relationship Help

decmatt · 25 Mar 2009 at 00:29

Hi

Here are my relationships
http://www.watotron.com/Geoz/upload/relationships.jpg

What I am trying to do is for the Client to have a catalogue of what he artists buys (Buying Habits), and the artists to have events that the client can go too. The client also makes enquirys of stock that he wishes to buy. If you have any questions on the ralationships please ask. But I just cannot get them to share data between each other and im quite stumped why. So any ideas from looking at it would be greatful!

Thanks

Dolph · 25 Mar 2009 at 00:33

What do your queries look like (either in query view or SQL)?

From a relationship point of view, it all looks like it should work to me, so the problem is likely to be in how you're trying to construct the queries.

MarcLister · 25 Mar 2009 at 01:43

Are you getting any messages then when you try to share the data across? If you are then the message contents could be helpful to find out what the issue is.

I agree with Dolph on the relationships looking OK. I'm not an Access or database buff but I've got a fair understanding and it all looks fine to me.

decmatt · 25 Mar 2009 at 02:51

I dont currently have any queries, I have tried imputting data via forms, I will use the enquiry bit as an example

When a customer makes an enquiry, the enquiry needs to be linked that that cutomer made the enquiry, so I have the linking table. But, the when I input it via the form, no record is inserted into the linking table, so it enters into enquiry, but not the linking.

decmatt · 25 Mar 2009 at 22:40

Any ideas? Just cannot get it to link data?

Dolph · 25 Mar 2009 at 23:45

Sorry, been at work all day.

I don't think you've got quite how it works. Inserting a record into a data table (such as client) will not automatically insert an entry into the associated link table.

You set up the data in each data table, then add the data into the link tables to show the which items link with which.

For example, when client a purchases an item from artist B, you would then add an entry into the buying habits table linking the two as part of the processing for future reference. This is an automated process from an end user point of view, but not from the design and implementation point of view. From an implementation point of view, you have to create the 'automation'.

decmatt · 26 Mar 2009 at 00:16

Any ideas how I would go about creating this automation? I see I need to make a query, but how would I design the query to update when the user makes for example a specific enquiry?

Dolph · 26 Mar 2009 at 00:25

Solac said:
Any ideas how I would go about creating this automation? I see I need to make a query, but how would I design the query to update when the user makes for example a specific enquiry?

That would depend on exactly what you wanted to do. If you wanted to (for example) store someone's search history, you'd have a select query for the search, and an insert query for the store and link the two in code when the search button is pressed so both queries are run, then display the results of the select query as the search results.

If you were using an insert query, you'd have to have some logic to check for duplicates and not try to insert a second (which would result in an error) on a repeat search.

Looking at your design, you could have a form that contains the enquiry details, and store the client id, and use insert queries to insert the new enquiry into the enquiry table, and then the enquiry ID and client ID into the link table.

Is this homework/project work or something you are doing for fun, because to build a complete system to do everything you want (including UI and the code to tie it all together) is going to be a fair bit of work.