Page 1 of 1

[Solved] SQL This for That

PostPosted: Sun Dec 08, 2019 8:37 pm
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;

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

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 4:47 am
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.

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 12:04 pm
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.

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 8:20 pm
by UnklDonald418
I added a form document Container Edit01. Is that what you are wanting?

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 9:02 pm
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.

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 9:21 pm
by jmbutton
Darn it I didn't have the Relationships set - here's an update

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 9:30 pm
by UnklDonald418
I'm not sure I understand what you are after but look at Container Edit02

Re: SQL This for That

PostPosted: Mon Dec 09, 2019 9:58 pm
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

Re: [Solved] SQL This for That

PostPosted: Tue Dec 10, 2019 11:52 am
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

Re: [Solved] SQL This for That

PostPosted: Tue Dec 10, 2019 8:01 pm
by UnklDonald418
Using Create Query in Design View I quickly assembled the following query
Code: Select all   Expand viewCollapse view
SELECT "Container_2"."LABEL",
FROM "Container" AS "Container_1",
            "Container" AS "Container_2",
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.

Re: [Solved] SQL This for That

PostPosted: Tue Dec 10, 2019 11:31 pm
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