Sep 28 2021 07:46 AM
Sep 28 2021 07:46 AM
I haven't used Access before but I have created two data bases. One for our providers and one for our residents. I want to enter the invoices that the providers send to our residents so that I can keep track of what each resident owes to each provider. I also want to create reports reflecting what all the invoices a resident has received from all of the providers and also a report where I can see who each provider has invoiced. Does this make sense? Can someone help me with how I would enter the invoices into Access to connect it to a provider and a resident?
Sep 29 2021 01:38 AM
Sep 29 2021 06:04 AM
First a bit of terminology. We refer to a "database" as the complete set of objects included the the application. We refer to the objects in which data is stored as "tables". Therefore, what you created was two tables, not two databases. With that clarification, communication with other developers will be clearer.
Next, if each resident can receive invoices from one or more providers, and if each provider can submit invoices for one or more residents, that is what is called a many-to-many relationship. If that term is not familiar to you yet, pause work on this particular database and invest your time in learning about "Normalization", which is the underlying principles behind proper relational database application design. Without that understanding, you'l end up with an unreliable, and hard-to-use database.
That said, the way to handle many-to-many relationships is to create a third table. It is usually referred to as a Junction Table. Junction tables have two field for Foreign Keys. One of tose Foreign Key fields is for the Primary Key in the Resident table and the other is for the Primary Key in the Provider table. Each time a provider submits an invoice, you enter one record in the Junction table. That record will include (at least) the Foreign Key from the Provider table (i.e. the Primary Key in that table inserted as the Foreign Key) and the Foreign Key from the Resident table (i.e. the Primary Key in that table inserted as the Foreign Key), along with the invoice number, amount, date and other required information.
Nothing pertaining to invoices should be recorded in either Provider or Resident tables. Invoices are recorded ONLY in this junction table.
You'll find a lot of detailed information on Many-to-Many relationships by searching on that term.