Help on Designing a Query

[funkymuppet]

Hi all,

Am a relative newbie to Access, although have worked with it over the last decade in various capacities. I have a database to manage hook (as in fly fishing hook) inventory. And I'm trying to build a Query that shows me the current inventory amount of one or more or all hooks that I own.

To define more: a hook has a manufacturer and a reference number - with those you can look up the design features and other shape factors. I have created one table called Hooks-Types that picks the Manufacturer (from another table) and specifies the shape characteristics for each hook. Each hook *type* is then the primary ID of that table.

A hook then has a size - and comes in multiple sizes. So in order to create an inventory structure, I created a table called Hooks-Inventory where the records are individual inventory transactions, specifying the hook (via ID), the size, and the transaction type. By transaction type, I have Opening Bal, Addition, Usage and Adjustment. My idea was to have a table where I could record the purchase of particular hooks in a particular size, the usage of those hooks (as flies are tied) etc.

So, to the problem: I need to build a query that has as input parameters the hook type and the size, and then shows me the set of transactions (from the Hook-Inventory table) that apply to those parameters, and gives me a total current inventory count at the end.

I'm not sure I've designed this the right way, so am very willing to redesign to suit an alternative approach - or if it is okay, then to understand how I could build the above query would be fantastic.

Many thanks indeed,

JW

[orange]

Here's a link to a tutorial that may help you with your design.

[June7]

Saving a calculated aggregate value like Balance is usually a bad idea. Conventional approach is to save raw transaction data then calculate summary info when needed.

Basically, that means adding all the transactions for items in and adding all the transactions for items out and calculating the difference for balance.

Often it's better to build a report instead of trying to accomplish all within query. A report allows display of detail records as well as summary calcs.

[funkymuppet]

orange, many thanks indeed - that tutorial was very helpful. One follow-up question, I've created the series of tables (based on the relationships determined through that method), but how do I have one form, for example the Hook Purchase Entry form, in which I can enter a new Hook and Size etc. and have those linked records automatically created in the other tables? I've tried to do this, and used sub-forms - but I keep getting an error "Field cannot be updated" - how do I enable auto-create on the linked forms - so that I can have fields from a number of different tables on a single form and have the new record creation flow through?

June7 - thanks for the reply as well, and that's the route I'm hoping to follow: have records for each transaction and dynamically calculate the remaining balance. The point about reports vs queries is great, although I need to resolve the issue above (or figure an alternative) to enter data easily.

Many thanks - JW

[June7]

Need to know your data relationships. What 'other' records should automatically create?

If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

As June7 has mentioned, it would be helpful if you could send a jpg of your tables and relationships.
Even better would be a copy of your database with just enough records to highlight the issue. You should remove anything confidential before posting.

[funkymuppet]

Thank you very much. I've posted a JPG below of the relationships - but not sure it will come out large enough in the post. I've attached her a 500kB copy of the database - I have hardly any records in there at the moment, just some to give a flavour of the data I will capture.

In designing the tables, the one that feels excessive is the Purpose, which then required a HookTypePurchase intermediary table - the Purpose is a series of categories of use for a Hook Type e.g. Nymph, Dry Fly, Streamer, Wet Fly - and a Hook can belong to one or more of these Purposes. It does seem I could just do a lookup table in that field and all selection of multiple entries.

In any case, what I want to be able to do in this instance is open up a form to enter a new transaction (a purchase of hooks, for example) - and from that form, if the hook type I purchased is not in the list, then fill out the Hook Product ID, the shape/style details and then also the size and details I purchased, which would then create the new Hook Type and also the Transaction automatically. Does this make sense?

It may seem a bit trivial - as in, it is not a lot of work to just go to the Hook Type table, figure whether what I purchased is already there, and then go to the Transaction table to enter details - but I am designing another Book Inventory (separate project) database, and to be able to fill out details on a single form that then populates new records in multiple tables would be ideal.

Any and all comments/feedback would be very much appreciated. Thanks - JW

[June7]

What you describe - adding record to the 'lookup' table while entering transaction - can be managed one way with code in NotInList event of combobox. Here is one tutorial http://www.blueclaw-db.com/access_no...ed_example.htm

Advise not building lookups with alias in table http://access.mvps.org/access/lookupfields.htm

HookTypePurposeID field is not used.

Consider not saving ID as foreign key. If the descriptive text is short like Nymph, Dry Fly, etc., saving the descriptor instead removes the requirement for joining tables to generate output. Also simplifies the combobox. Autonumber ID as PK/FK has advantages in large database (faster indexing, less memory) but in a small database, the inconveniences might outweigh.

[funkymuppet]

June7 - thank you for the response - this has been hugely helpful. Have redesigned and started putting some more sample data in, and attached a most recent copy below (as a zip file).

I have a couple of (hopefully) quick follow-up questions:

1. I'm still unclear how to manage a many-to-many relationship, i.e. where a Hook Type has multiple Purposes, and a Purpose can relate to a number of Hook Types - but that's the extent of it. I've set it up right now to be one Purpose only - but that was just to get going. I can see how other many-many relationships work, but in this case the intermediary table would be nothing more than a mapping table - which seems excessive? Am I missing something?
2. I've added a further feature to the transaction: Location (I need to track the addition or usage of items by specific location) - so have a table tblLocation. But if I need to do a Transfer transaction, i.e. transfer from one location to another, I need two fields in the transaction table, each of which would be a Location - however, I can only have one relationship from the tblHookTransactions to tblLocation. It feels as if I'm not doing this right either - I do want to generate reports that filter transactions based on Location (either Source or Destination)

Many thanks again - the help is very much appreciated.

JW

[orange]

where a Hook Type has multiple Purposes, and a Purpose can relate to a number of Hook Types - but that's the extent of it. I've set it up right now to be one Purpose only - but that was just to get going. I can see how other many-many relationships work, but in this case the intermediary table would be nothing more than a mapping table - which seems excessive? Am I missing something?

Don't jump in to the nitty gritty (forms, reports..) until you have a data model that supports your needs. If you do something to get going, from experience there are 2 things that happen:
1) you'll stick with it because it sort of works, but it doesn't do x or y.....
2) I've got too much in it now I can't go back and change

Seen this too many times.

I'm not convinced you have a complete statement of your "business". You really need that before completing and testing the model.
From the quote, I already see that you recognize a shortcoming of the current database.

A HookType may have 1 or Many HookPurposes
A HookPurpose may relate to 1 or Many HookTypes
If you know this is a fact, then adjust the model accordingly.

I think your HookTransactions needs to be broken down/clarified.

My recommendation is that you write a 5-6 line description of what your business is. What exactly are the subjects involved? You will be surprised how it helps in distinguishing and determining some details.

From my perspective it sounds like you Order/Purchase Hooks (Products/Items) from Various Suppliers/Manufacturers. Hooks come in different types. Hooks have specific and general properties.

Good luck with your project.

[funkymuppet]

orange - many thanks indeed for the pointers. spent a good part of the weekend re-assessing and now have an improved design with separate tables for the transactions, and have the locations being recorded sensibly. Even have the many-many piece sorted as well.
Appreciate all the help,
JW

[orange]

Glad your project is moving along. The better the design matches your business, the better it will serve you.