Filling a table based on ANOTHER table. (1 Viewer)

timothy.corbin

Registered User.
Local time
Yesterday, 22:50
Joined
Apr 24, 2019
Messages
11
I am wondering if a thing is possible, and if so where I can read up on how to achieve this.
A form feeds a table. The form has two quantity fields: Weight or Units, which represent either how many of an item have been thrown away OR how much by weight. The FBI already "knows" that HERB SAGE is sold by individual units (EA) and APPLE GALA is sold by weight (WT).
Is it possible to have ONE quantity field on the form, and when the user inputs this quantity the DB recognizes that it is either a EA or WT number then directs that data to the correct field on a table?
The attached db isn't the most updated. I have it set now so that it exports the salvage data for excel, and auto populates the unit price on the data entry form based on the item selected.
Yall have helped me immensely, and I keep learning!
 

Attachments

  • SALVAGE v4.zip
    1.9 MB · Views: 88
Last edited:

Micron

AWF VIP
Local time
Today, 01:50
Joined
Oct 20, 2018
Messages
3,476
possible but without knowing a lot more about the design of things, can only be general.
If you base the measure/units on product/item type then yes, but not if you try to associate "each" with quantity. A "linking" table may be required, where apples are associated with "each" or "weight" - whichever is appropriate.
EDIT - to clarify
directs that data to the correct field on a table?
What would get written to the Units field is either the text value from lookup/linked table (Pounds) or its PK ID, which could be autonumber 5.
 
Last edited:

timothy.corbin

Registered User.
Local time
Yesterday, 22:50
Joined
Apr 24, 2019
Messages
11
Micron,

I shared the most recent version I have access to. I am out and about when this idea came to me. Playing on my phone!
 

Micron

AWF VIP
Local time
Today, 01:50
Joined
Oct 20, 2018
Messages
3,476
As mentioned, you should have a Unit field for ea, lb, kg, case, whatever and a Qty field.
However (you're not going to like this) due to your design things are going to get more difficult as time goes on. What's not good

- calculated table fields
- table lookup fields
- table(s) with no PK
- horizontal design (like a spreadsheet) instead of row based
- spaces & special characters in names
- using reserved words for names
- pricing has no history. If you run loss report at $1.25 each, what happens when you change price to 1.50 but need to compare to last price in the future? $1.25 is gone, calculation is now based on 1.50
- you don't have commodity based product data so you end up with 4 records for banana. You need otherwise unnecessary expressions and/or functions to find out anything about bananas in general.
- you're opening tables to do data entry

How about I stop there before you get real mad at me (too late)? My intention isn't to dis your db but to let you know you're headed for a tough row to hoe and will probably be here a lot as things get harder to figure out. I have to wonder how long you've been working on this and how tough things are at this point. Probably not eager to start over either, but the deeper you go the less you're going to want to fix it I guess.

Maybe research normalization (entity/attribute) concepts or any of the other issues I brought up if at all interested.
 

timothy.corbin

Registered User.
Local time
Yesterday, 22:50
Joined
Apr 24, 2019
Messages
11
Why would I be upset? This is the type of feedback I am seeking!
I have been at this for about a month and a half now. Actually, closer to two years if you include the bulky, cumbersome attempt at using Excel...but that is a story for another time.
I have "rebuilt it" a few times, and each time when I run the table analyzer it recommends that I not split the SALVAGE table. I continue to refine the queries each time as well, with the intent of weeding out those pesky calculated fields in the tables.
As for the pricing history, when the user selects the produce in the data entry form, it pulls the current prices from the reference data table, and stores it in the salvage table. When the price is changed in the reference table (NOTE TO SELF: CREATE A REFERENCE TABLE QUERY IN ORDER TO MOVE THE UNIT PRICE EQUATION!), that change doesn't affect previous entries in the SALVAGE table. If you are telling me that it isn't supposed to work that way, I totally and thoroughly believe you. I was actually very afraid that would be the very thing TO happen--that when I updated the price of the Braeburn Apple to 99 cents on the Reference Table, ALL the Braeburns on the Salvage table would change to .99 cents. It is very important that this NOT happen, as I can use that to see the price at which people stop buying a certain commodity.
Reserved WOrds: Got it. I will be going through and changing some names. If it tells me I CAN'T because the data in a table is connected via relationship blah, blah, words...so be it. I will just make Salvage Log VERSION FIVE!!! Unless you are talking about the names of the products...if so, I think my hands are tied, as these are the names which our distributor uses, and I must use when I report this data to our Regional Managers.
Opening table to do data entry. Yeah. Sorry about that one. That was supposed to be a query based form that has all the information from the Salvage table. I just (literally) forgot to do the query.
I am glad you answered and brought up these points. I am still learning, and can't learn unless someone guides me!
 

Micron

AWF VIP
Local time
Today, 01:50
Joined
Oct 20, 2018
Messages
3,476
I didn't see a date associated with prices(that I recognized as such) so maybe you're ok and I missed something(s) around that. Can you tell me what the $ loss was for any given month from the past? If yes, I guess OK. As for other points, each 1 is a searchable topic - I'll paste the usual links en masse for these. Check them out then re-read my comments because I don't think you're grasping some of them (as suggested by your comments on names). Suggest you continue to use what you have for now if it's needed but I'd stop developing this one if possible. When you study normalization perhaps it will make sense how you can have "bananas" in a table once and still have 4 types should you normalize to that level. The analyzer can give you wrong advice fer sure (GIGO). Way forward is probably to study concepts, design tables and in one fashion or another, post the relationship info along with a detailed synopsis of what the db needs to support if design feedback is desired.

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html and/or

http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers - http://www.utteraccess.com/wiki/Autonumbers

- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm

Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

About calculated table fields - http://allenbrowne.com/casu-14.html

About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
WOW - when checking these links I somehow lost my post. Thankfully I had followed my practice if copying long posts to clipboard!!!
 
Last edited by a moderator:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:50
Joined
Feb 28, 2001
Messages
27,001
That's a good post and a wealth of information, Micron.
 

timothy.corbin

Registered User.
Local time
Yesterday, 22:50
Joined
Apr 24, 2019
Messages
11
Excellent! I'll be doing some reading and redeveloping, for sure.
 

Users who are viewing this thread

Top Bottom