[Solved] SQL This for That

Creating and using forms
Post Reply
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

[Solved] SQL This for That

Post by jmbutton »

I'm trying to set up a little home inventory database, but keep stumbling over what I suspect is simple SQL query.

I have a table of Items and another of Containers where the items are stored.

The Items table has a few fields to identify things by name, notes, & qty, not really a problem.

The problem is with the Containers table, where the fields include CONT_ID, LABEL, & LOC_ID.

CONT_ID is the integer key, LABEL is a short text Label I put on the Containers, and LOC_ID is the CONT_ID where the Container is stored, be it a room or another box.

When I add, remove, or relocate Items or Containers, I need an Update form that will show where a Container is located, which would be;

The LABEL of the CONT_ID's LOC_ID.

It would seem to be a simple lookup equating the LOC_ID of a Container with a CONT_ID (= to the LOC_ID) and getting that LABEL.

Here's the table in question;

CONT_ID LABEL LOC_ID
0 BAG001 8
1 BAG002 8
2 BAG003 8
3 BAG004 8
4 BAG005 8
5 BAG006 8
6 BAG007 8
7 BAG008 8
8 CTN001 18
9 CTN002
10 BIN001
11 BIN002
12 ORG003
13 BAG009 8
14 BAG010 8
15 BAG011 8
16 BIN003
17 BIN004
18 Front
19 Bath
20 Bed
21 Kitchen
22 Gallery
23 Front Porch
24 Rear Deck
25 Lumber Rack
26 Outside
27 Elsewhere

Any pointers would be helpful and appreciated
Last edited by Hagar Delest on Mon Dec 09, 2019 9:58 pm, edited 1 time in total.
Reason: tagged solved
openoffice 4.1.7 on windows 8.1
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL This for That

Post by UnklDonald418 »

Welcome to the forum.
You have a much better chance of getting useful help if you upload a sample database. It saves on confusion and we don't have to go through the brain damage trying to recreate your tables, queries and forms.
[Forum] How to attach a document here
One approach to the problem would require an item table, a container table and a location table.
Each item would have a foreign key CONT_ID that designates which container it is stored in.
Each container would have a foreign key LOC_ID that designates the location where the container is stored.
Now if you move the container to a new location you only need to update LOC_ID value in the container table, and all items in that container will automatically know about the new location. That could be easily handled using a listbox on a form.

If you store both container and location in the item table, then you must search for each item in a container and update the locations for all the all the affected items. Base forms are not capable of group updates so each item would need to be changed individually.
It is possible to do group updates using SQL but that is a little clumsy. That requires composing the statement outside of a Base form and executing it at Tools>SQL followed by selecting View>Refresh Tables.
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
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

Re: SQL This for That

Post by jmbutton »

Thanks much for the reply.

That's pretty much what I have - an Item table with its CONT_ID, and a Container table with its LOC_ID.

I've been struggling with Container Edit & Container Entry forms and using listboxes as you describe, but I cannot see how to show the location label of the container that a container is stored in.

I can show an item's container CONT_ID & the associated LABEL, but if that container is stored in a subsequent container, I cannot see how to show that CONT_ID & LABEL.

Here's the database (the CONT table is the simplified Container table I showed in my first post above).

Let's see if I can upload this DB ...yep I dood it.
Attachments
home Inventory 21.odb
(55.75 KiB) Downloaded 231 times
openoffice 4.1.7 on windows 8.1
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL This for That

Post by UnklDonald418 »

I added a form document Container Edit01. Is that what you are wanting?
Attachments
home Inventory 21A.odb
(64.33 KiB) Downloaded 209 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
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

Re: SQL This for That

Post by jmbutton »

Thanks, but not quite. What I need is the LOC_ID & LABEL of the containing Container.

It would be an additional text box. I would only want to edit the current record's location & Note. Seeing the containing Container's location would let me find it so I could get something stored in it or move it to another location.

In the case of record 1 in your form, I would like to see the LOC_ID & thus the LABEL of CTN001.

Record 9 is that of CTN001 and its LOC_ID and LABEL show its location in the Front room.

That's what so frustrating, the data is right there in the same table, I just don't know how to refer to it.
openoffice 4.1.7 on windows 8.1
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

Re: SQL This for That

Post by jmbutton »

Darn it I didn't have the Relationships set - here's an update
Attachments
home Inventory 21B.odb
(78.05 KiB) Downloaded 220 times
openoffice 4.1.7 on windows 8.1
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL This for That

Post by UnklDonald418 »

I'm not sure I understand what you are after but look at Container Edit02
Attachments
home Inventory 21C.odb
(86.76 KiB) Downloaded 231 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
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

Re: SQL This for That

Post by jmbutton »

Oh yes you do and did! Thanks so much that will get me going.

The trick was adding the sub form and I didn't realize one could link Slave & Master within the same table. What I need is within the sub-sub-form ...I suspect I can bump it up to the sub-form level.

Thanks again UnklDonald418
openoffice 4.1.7 on windows 8.1
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

Re: [Solved] SQL This for That

Post by jmbutton »

Interesting ...the solution seems to require the sub-sub-form level to show the Label of the containing Container. I can work with that. Thanks again
openoffice 4.1.7 on windows 8.1
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] SQL This for That

Post by UnklDonald418 »

Using Create Query in Design View I quickly assembled the following query

Code: Select all

SELECT "Container_2"."LABEL", 
               "Container"."CONT_ID" 
FROM "Container" AS "Container_1", 
            "Container" AS "Container_2", 
            "Container" 
WHERE "Container_1"."LOC_ID" = "Container_2"."CONT_ID" AND "Container"."LOC_ID" = "Container_1"."CONT_ID"
Using that as the data source for a SubForm I can eliminate the SubSubForm.
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
jmbutton
Posts: 8
Joined: Sun Dec 08, 2019 8:31 pm

Re: [Solved] SQL This for That

Post by jmbutton »

Far out! Thanks ...I figured there was some sort of SQL query that would do it ...I just didn't think about using alias like that. I gotta try to use that. It's almost a way to make a generalized hierarchical set of fields.
Thanks again - (::)<--yep my ASCII Button
openoffice 4.1.7 on windows 8.1
Post Reply