Many to Many?

Creating tables and queries
Post Reply
mmistyrose
Posts: 1
Joined: Wed Jan 24, 2018 1:33 am

Many to Many?

Post by mmistyrose »

I've looked for days and I'm not sure I understand how to set up my base for what I need. I work with a food bank and we are large enough to need a database. I've got it started enough to be able to have the client database, however, each week I need to be able to pull up the client, input the date they are picking up a foodbox and I will need to be able to run a report to pull up such info later.

What is the easiest way to achieve this? Obviously there will be many clients with the same date of pick up. Any help ASAP would be greatly appreciated!!!
OpenOffice 4; OS is Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to Many?

Post by Villeroy »

File>New>Databasae...
blah, blah
save database

menu:Tools>SQL...

Code: Select all

CREATE TABLE "A" ("NAME" VARCHAR(30) NOT NULL, "ID" INTEGER IDENTITY);
CREATE TABLE "B" ("NAME" VARCHAR(30)NOT NULL, "ID" INTEGER IDENTITY);
CREATE TABLE "AB" (
  "AID" INTEGER, "BID" INTEGER, 
  PRIMARY KEY("AID","BID"), 
  FOREIGN KEY ("AID") REFERENCES "A" ("ID"),
  FOREIGN KEY ("BID") REFERENCES "B" ("ID")
);
menu:View>Refresh Tables
menu:Tools>Relationships... (drag the tables apart from each other to see the relations)

Enter some names in table B.

Now create a main form on table "A" with a subform on "AB" linked through A.ID --> AB.AID
Add a grid control with a list box column.
Fill the list box with the following SQL expression

Code: Select all

SELECT "NAME", "ID" FROM "B" ORDER BY "NAME"
Now you can enter A items to the main form and select B items from the listboxes in the subform grid belonging to the current item in the main form'.

Create another form on table B with a subform on "AB" linked through B.ID --> AB.BID and fill the listbox in the subform grid with A names.

See viewtopic.php?f=100&t=40444 (Persons <--> Things )
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to Many?

Post by Villeroy »

mmistyrose wrote:What is the easiest way to achieve this?
This is it.
Attachments
many-to-many_t92110.odb
(20.64 KiB) Downloaded 179 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Many to Many?

Post by UnklDonald418 »

Villeroy provided a nice example of a many to many relationship, but I believe you might be confused as to what constitutes a many to many relationship. In that situation each client could pick up multiple boxes, but each box could also be picked up by multiple clients.
I think what you need is a one to many relationship where each client can pickup multiple boxes.
I attached a simple database demonstrating how that might work.
The TransEntry form allows you to select an existing client from the list in the upper table. You can also add new clients to that table.
The lower table displays all the transactions for that client, and also allows entry of new transactions. The PickupDate can be entered manually or if left blank the current date will be automatically entered.
There are two queries, Query1 displays all the transactions in chronological order. Query 2 prompts for a date and displays all the transactions that occurred on that date.
Attachments
Demo16_Foodbox.odb
(13.14 KiB) Downloaded 187 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to Many?

Post by Villeroy »

@Uncle Donald,
Your example is a many-to-many relation between persons and item_types mapped by a table of transactions.
Your form is the same as mine. A main form of persons with the mapping transactions in a subform grid and item types selectable from list boxes in the subform grid.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Many to Many?

Post by UnklDonald418 »

Villeroy is correct. I probably confused the situation when added an Item_Type that mmistyrose didn't mention. That does result in a many to many relationship between Clients and Item_Type.
However, I believe mmistyrose was construing many Clients and many Transactions to be a many to many relationship, which it is not. Each client can have many transactions (receive one or more food boxes on a particular date) but each transaction has only one client. Thus a one to many relationship.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply