Connecting TWO Databases with a link table

[alfasprint]

Hello.


I am quite new in Access, but did a lot of reading and tries.

I have an Access 2013 x64 database that has two "systems". One that carries Client details (3 connected tables working perfectly) and another system that has the Dates these clients visited the company and what they bought or requested (10 connected tables working perfectly).

I have made a LINK TABLE with 2 primary keys connecting the two systems. I have created queries and forms that can be updated correctly, BUT the problem is the following:

When I enter data on the visiting date, this is connected to a client.
When I add details on what he bought or requested, this is connected to the date (which is as I said connected to the client).

I can't find a way to create a query, report or form where you can have the Client details, the Visiting Date AND what he bought or requested on that day (which might be located in more that 2 connected to the Date tables).

Any clues???

[orange]

?? Why are there 2 separate databases?? Maybe some background and more info about your business would be helpful to readers.
Are your databases split? Research msAccess split database
You may want to step back and work through some tutorials and/or watch some tutorials on database design.


The bottom line is it isn't clear what the business is that you are trying to support with database.

There are a number of free data models at Barry Williams' site that may be useful as a starting point.

Good luck.
There are some rules associated with your business and they impact the design of your database.

[alfasprint]

OK. I think I get your point.

Let me explain myself a little more.

There is an one to many connection between Client_Address (PK) and Client (FK) table. (PK=Primary Key, FK=Foreign Key)
There is an one to many connection between Client (PK) and Client_Visits (FK) table.
There is an one to many connection between Client_EmployeeAssistant (PK) Client (FK) table.
There is an one to many connection between Client_Visits (PK) and Clients_Visits_Product_Category_Bought (PF) table.
There is an one to many connection between Product (PK) and Clients_Visits_Product_Category_Bought (PF) table.

I can;t find a way for my db to show in any way ALL these data together, i.e. Customer Name & Address, Time of Visit, Employee Assisted him, Product category bought and Product Name.

[orange]

For your benefit, and the best use of an hour, work through this tutorial from Rogers Access Library. You have to work through the tutorial to get some experience. There is a problem statement, a solution and a series of steps that will lead you to a database structure to do what you want. Once you work through this tutorial, you can use what you learn to with any database.

Good luck.

[alfasprint]

Thanks for the tutorial. Will get into it immediatelly.

[orange]

Good luck.
The Hernandez Process in a Nutshell is a great reference also.

Here's a video to watch after you finish the tutorial.
All of his Database videos are very well done.