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!!!
Many to Many?
-
- Posts: 1
- Joined: Wed Jan 24, 2018 1:33 am
Many to Many?
OpenOffice 4; OS is Windows 10
Re: Many to Many?
File>New>Databasae...
blah, blah
save database
menu:Tools>SQL...
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
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 )
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: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"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Many to Many?
This is it.mmistyrose wrote:What is the easiest way to achieve this?
- 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Many to Many?
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.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Many to Many?
@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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Many to Many?
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.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11