Full Version: Proper Syntax
UtterAccess Forums > Microsoft® Access > Access Forms
startingover0001
Just a curious question syntax and arrangement. Trying to put together a small db to help me keep track of various game elements such as structures and what raw materials would be available to build that structure. The building is available from level 1 to level 4. The raw material Straw is avail from levels 1 to 4. The raw material Stone is available from level 1 to 5. The raw material Mud is available from level 1 to 4. My current query compares the From and To values on the Structure form with the Products from and to values. It shows the Straw and Mud, but not the Stone, even though it's available from level 1. Since the building is good only through level 4, the stone should still show up for that building through all it's levels. Once the building gets to level 5, it should still show the stone, but no longer show the Mud and Straw. Is this making any sense? I can show everything at or above the From level (but then of course that shows products that are not yet available or I can show things available prior to and including the To value, but then that would show things that shouldn't be available anymore. Does this require 2 queries, one piggy backing off the other? Thx
RJD
Hi: I'm not completely clear on what you want, and we don't have your table designs, but here is an idea attached. It has one table of materials and levels, and one query. Then it has two reports: one levels by materials and one materials by levels.

Is this what you mean? Or do we need some clarifications?

I would assume you might want to expand on this, having a table of materials and a table of levels, then a junction table of how materials link to levels (the junction is what I have included, alone). But let's see if this is going in the right direction to begin with.

HTH
Joe
startingover0001
I uploaded the db I'm playing around with. Play with the levels available from and to on the frmBuildings, and play with the available from and to in the products table. As long as it equals the from level on the building form to the to level on the building form, all items in the dropdown should only show those available during these particular levels regardless of what level they start or end on...as long as each item shows up if it's within the range of the building level numbers. I guess an easier way to state it would be to say the the products should all show regardless of when their from and to numbers are as long as they're in the range listed on the building form. If the product numbers are not in the building range, then they shouldn't show up.
RJD
Hi again: Your table design really doesn't allow for the kind of comparisons you seem to want. By specifying only the From and To levels, you miss the levels in between. You really need a junction table linking materials to each level and one more to link buildings to each level.

Rather than doing all that for you, I worked around your design by adding a Levels table and creating the junctions on-the-fly using the level ranges in the products and buildings tables with the Levels table and range limited Cartesian products. See the two queries that do that.

I then linked the two queries together to get the materials available to each level in each building. You can report from there.

Take a look and see why the expanded number of records (one for each level for each material item and for each building) is necessary.

Then you can think about a redesign around junction tables to more easily and permanently accommodate the results you want.

HTH
Joe
startingover0001
Thank you. I will take a look at this and study it. Every once in a while, I come across something that I just can't see to wrap my head around lol. I'll study it a bit and report back. Thank you for your help/suggestions.
startingover0001
@RJD - I've studied your example and this is one I guess I can't seem to get my head around. In the first place, by doing "between"'s, doesn't that eliminate the ends (first and last)? While I see your point TO a point, I can't seem to understand how I would redesign the tables with levels being a junction. Does there not have to be something on the individual forms (buildings and raw materials [which is actually taken from products as those available for the appropriate levels]) from which to be compared?
RJD
QUOTE
...by doing "between"'s, doesn't that eliminate the ends (first and last)?

No. Between is inclusive. So Between 1 And 4 includes 1,2,3,4.

The junction table is pretty straightforward. You can keep the Buildings and Products tables the same if you want, then add a junction table. I'll try to do a mod to your db for you to see - but that will have to be later since I am shutting down now. Entry forms for Buildings and Products will be simple. For assigning products to a building, junction entry can be done as a subform to a Buildings form, where you select the Product and the level from comboboxes (cascading the levels from the product selection), with appropriate checks. Remember, this is assignments.

For all possibilities, then you can use the approach I previously posted. It's not absolutely clear whether you are looking for possibilities or to make assignments - or both.

As I said, I'll look at this tomorrow and see if I can do a simple assignments junction for you with the entry form.

HTH
Joe
RJD
Hi again: I finally got back to this after detours today to a client and some other work ...

See the db revision attached. This is rough, and just a guess, since I don't have a complete grasp on what you are trying to accomplish. The form that appears on db open demonstrates use of a junction table (record creation) and how to limit selections based on building levels (levels to select and products available for the level selected).

Is this something like what you are trying to do?

Also, you are using lookup fields (see tblBuildings, fields BuildingType and StructureType). This is almost always a bad choice. Suggest you build tables to support these lists and then use comboboxes on forms to select the value you want. See HERE for why not to use lookup fields.

HTH
Joe
startingover0001
@ RJD - Thank you. Yes, this is what I'm trying to accomplish at least to the major degree. I was hoping for only the one combobox showing the available product or raw material based on the level stated on the structure form rather than having to choose a level manually each row. The only thing I noticed was that when an item (say stone) was no longer available, b/c of the continuous form simply reproducing the same combobox and requiring it, had stone been chosen several rows ago, the re-query removes the item from even the previous row. However, in my particular case, it doesn't matter as the materials would be saved to a separate table attached to that particular structure. The combo box would only be for the selection of the item so no continuous form is needed. I'd still rather only one combo box using the level from the structure form than a combo box to choose level manually. This is because the particular structure (while it may last through several levels) is particular for construction to that particular assigned level. It makes it easier to separate out later. A daub & wattle house in level 2 will have advanced to perhaps use bricks instead of mud, even though the level 1 d&w house will continue to stand (until weather kills it lol).

As for your question of lookup fields...I RARELY ever use them. I don't like them. While "E-lookup" fields are supposed to be somewhat more efficient, I still don't like them. (probably b/c when I was first learning to use them, I could never remember without having to look it up which syntax part referred to which field in which table. I love combo boxes. It's nothing to bring over all the information you need and you can bring over far more info than with a lookup field.

I will study the way you designed this. I think it will work well for what I'm trying to do as long as I can wrap my head around the junction tables and querys smile.gif

If I have further questions, I shall be sure to ask. I thank you for your kind patience and indulgence in my learning slowness lol.
RJD
Hi: You are very welcome. As I said, this is a rough demo, just to show you the junction design, so not all checks and balances will be there. To correct the first issue you mentioned you can add this ...

Private Sub cboProducts_GotFocus()
Me!cboProducts.Requery
End Sub

This will correct the Product list for any row you look at, rather than just cascade the list based on the last selection of level.

And there are no doubt other clean-up issues as well.

Good luck with your project.

Regards
Joe
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.