Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-09-2019, 02:03 PM   #1
timothy.corbin
Newly Registered User
 
Join Date: Apr 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
timothy.corbin is on a distinguished road
Filling a table based on ANOTHER table.

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!
Attached Files
File Type: zip SALVAGE v4.zip (1.90 MB, 8 views)


Last edited by timothy.corbin; 06-09-2019 at 02:12 PM. Reason: Added attachment. Updated text.
timothy.corbin is offline   Reply With Quote
Old 06-09-2019, 02:12 PM   #2
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 711
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Filling a table based on ANOTHER table.

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
Quote:
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 06-09-2019 at 02:15 PM. Reason: clarification
Micron is offline   Reply With Quote
Old 06-09-2019, 02:18 PM   #3
timothy.corbin
Newly Registered User
 
Join Date: Apr 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
timothy.corbin is on a distinguished road
Re: Filling a table based on ANOTHER table.

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!

timothy.corbin is offline   Reply With Quote
Old 06-09-2019, 04:34 PM   #4
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 711
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Filling a table based on ANOTHER table.

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.
Micron is offline   Reply With Quote
Old 06-10-2019, 06:15 AM   #5
timothy.corbin
Newly Registered User
 
Join Date: Apr 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
timothy.corbin is on a distinguished road
Re: Filling a table based on ANOTHER table.

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!
timothy.corbin is offline   Reply With Quote
Old 06-10-2019, 08:21 AM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 711
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Filling a table based on ANOTHER table.

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...on-part-i.html and/or

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

Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/...cation-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/m...lds/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 isladogs; 06-10-2019 at 11:34 AM. Reason: Changed final url to hyperlink
Micron is offline   Reply With Quote
The Following 2 Users Say Thank You to Micron For This Useful Post:
The_Doc_Man (06-10-2019), timothy.corbin (06-10-2019)
Old 06-10-2019, 09:35 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,931
Thanks: 79
Thanked 1,564 Times in 1,452 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Filling a table based on ANOTHER table.

That's a good post and a wealth of information, Micron.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-10-2019, 10:53 AM   #8
timothy.corbin
Newly Registered User
 
Join Date: Apr 2019
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
timothy.corbin is on a distinguished road
Re: Filling a table based on ANOTHER table.

Excellent! I'll be doing some reading and redeveloping, for sure.

timothy.corbin is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filling some table data from another table ITguy1981 Tables 2 05-14-2015 08:55 PM
Table filling with #'s Larnu Forms 14 11-07-2013 06:50 AM
Filling up a table from another prabirsenuk General 4 05-24-2007 12:29 AM
filling a table amanzimdwini Macros 2 02-18-2007 07:29 PM
Filling in fields on a form based on an outside table Rhythmdvl Forms 3 08-13-2001 09:44 AM




All times are GMT -8. The time now is 12:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World