Many to Many?

Creating tables and queries

Many to Many?

Postby mmistyrose » Wed Jan 24, 2018 1:44 am

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
mmistyrose
 
Posts: 1
Joined: Wed Jan 24, 2018 1:33 am

Re: Many to Many?

Postby Villeroy » Wed Jan 24, 2018 3:19 pm

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

menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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 https://forum.openoffice.org/en/forum/v ... 00&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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25701
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to Many?

Postby Villeroy » Thu Jan 25, 2018 2:24 pm

mmistyrose wrote:What is the easiest way to achieve this?

This is it.
Attachments
many-to-many_t92110.odb
(20.64 KiB) Downloaded 31 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25701
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to Many?

Postby UnklDonald418 » Fri Jan 26, 2018 3:22 am

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 42 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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 928
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Many to Many?

Postby Villeroy » Fri Feb 02, 2018 12:05 am

@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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25701
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to Many?

Postby UnklDonald418 » Fri Feb 02, 2018 2:00 am

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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 928
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests