UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Select Which Field To Pull Info From Based Input In Another Table, Access 2016    
 
   
chrismja
post Nov 9 2018, 12:30 PM
Post#21



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


Absolutely George!
I KNOW Access is the right tool for what I need to do and so far I've made it to work despite my deficiency in knowing everything I'd like to about it. I have somehow cobbled it together to make it work.
I enjoy learning and figuring things out, so I don't care to put in the work to learn and rebuild this to make it work better, sometimes it's just helpful to be pointed in the right direction and so, for that, I appreciate you giving me some direction on where to start.
I do most definitely understand the problem with multiple tables as I have them and I want to learn how to do it better/more efficiently! I'm very appreciative of everyone's help here!
Go to the top of the page
 
tina t
post Nov 9 2018, 01:15 PM
Post#22



Posts: 5,582
Joined: 11-November 10
From: SoCal, USA


go with George's link, hon. you can't get better help than that. thorough process analysis (even when it's "your" process), and careful normalization of data, are the strong foundation of any relational database. the Access software is optimized to work with normalized data tables, so everything you do with your Access database will be incredibly easier, when you've started with a thorough process analysis and careful normalization of data. good luck with your project!

hth
tina
This post has been edited by tina t: Nov 9 2018, 01:16 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MadPiet
post Nov 9 2018, 01:20 PM
Post#23



Posts: 2,547
Joined: 27-February 09



Any "feature" that an instance of a Vehicle (a Vin#) can have only one of (Pkg Code, Motor, Transmission, BodyStyle) would be attributes of an instance of a truck (identified by a VIN#). Then you'd probably have a child table that identifies what Options a vehicle has, and a table of "all" Vehicle Options.

Then if you price each individual option, you can add all those up and get a total cost of all selected options, right?
Go to the top of the page
 
chrismja
post Nov 9 2018, 01:49 PM
Post#24



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


QUOTE
MadPiet

Any "feature" that an instance of a Vehicle (a Vin#) can have only one of (Pkg Code, Motor, Transmission, BodyStyle) would be attributes of an instance of a truck (identified by a VIN#). Then you'd probably have a child table that identifies what Options a vehicle has, and a table of "all" Vehicle Options.

Then if you price each individual option, you can add all those up and get a total cost of all selected options, right?


I'm following your question somewhat.
Yes, each vehicle can have only 1 package, 1 body style, 1 motor code, 1 transmission code, etc.
In my mind here is the tricky part though - different vehicle lines (Fusion and F150 for instance) may share the same package code number and the same motor code and the same transmission code. So I have to have some way to specify that these certain rebates go with these codes for Fusions and these other rebates with the same codes belongs to F150s.
Go to the top of the page
 
chrismja
post Nov 9 2018, 01:50 PM
Post#25



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


QUOTE
tina t

go with George's link, hon. you can't get better help than that. thorough process analysis (even when it's "your" process), and careful normalization of data, are the strong foundation of any relational database. the Access software is optimized to work with normalized data tables, so everything you do with your Access database will be incredibly easier, when you've started with a thorough process analysis and careful normalization of data. good luck with your project!

hth
tina


Thank you!
Go to the top of the page
 
MadPiet
post Nov 9 2018, 04:23 PM
Post#26



Posts: 2,547
Joined: 27-February 09



So I have to have some way to specify that these certain rebates go with these codes for Fusions and these other rebates with the same codes belongs to F150s.

Say your price table was structured like this (Make, Model, PriceCode) and the (Make, Model) combination was unique, you'd be sorted, right? You can join two tables in a query by as many fields as you want.

(that's kinda why I keep returning to the "how are these things related?" questions - because a good structure will make your job infinitely easier, and will actually work for you and not against you.
Go to the top of the page
 
chrismja
post Nov 9 2018, 05:31 PM
Post#27



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


QUOTE
So I have to have some way to specify that these certain rebates go with these codes for Fusions and these other rebates with the same codes belongs to F150s.

Say your price table was structured like this (Make, Model, PriceCode) and the (Make, Model) combination was unique, you'd be sorted, right? You can join two tables in a query by as many fields as you want.

(that's kinda why I keep returning to the "how are these things related?" questions - because a good structure will make your job infinitely easier, and will actually work for you and not against you.


Yes, that's correct. It would be one huge table (which might make it more difficult to understand for the ones that would updating it) but yes, it's possible that I could have one huge table with each vehicle and their subsequent body codes. If I were to go that route, it might look something like this (but much, much larger and longer!) and then in my mind, I'd need another table with just the motor and transmission rebates? I guess the reason I have shied away from going this route is that it is going to be one huge table that scrolls on forever. (not really forever but you catch my drift! Lol)
MODEL YEAR BODY % Rebate Model Descrip
F-150 19 X1R 0.00% $0 RAPTOR S/C (X1R)
F-150 19 W1R 0.00% $0 RAPTOR CRW (W1R)
F-150 18 F1C 99.00% $4,000 4X2 R/C (F1C)
F-150 18 F1E 99.00% $4,000 4X4 R/C (F1E)
F-150 18 X1C 99.00% $4,000 4X2 S/C (X1C)
F-150 18 X1E 99.00% $4,000 4X4 S/C (X1E)
F-150 18 W1C 99.00% $4,000 4X2 CREW (W1C)
F-150 18 W1E 99.00% $4,000 4X4 CREW (W1E)
Fusion 18 P0G 99.00% $2,000 FUSION S
Fusion 18 P0H 99.00% $2,000 FUSION SE FWD
Fusion 18 P0T 99.00% $2,000 FUSION SE AWD
Fusion 18 P0K 99.00% $2,000 FUSION PLATINUM FWD / FUSION TITANIUM FWD
Fusion 18 P0D 99.00% $2,000 FUSION PLATINUM AWD / FUSION TITANIUM AWD
Fusion 18 P0V 99.00% $2,000 FUSION SPORT
Fusion 18 P0U 99.00% $3,500 FUSION S HYBRID
Fusion 18 P0L 99.00% $3,500 FUSION SE HYBRID / FUSN SE HYBRID
Fusion 18 P0R 99.00% $3,500 FUSION PLATINUM HYBRID / FUSION TITANIUM HYBRID
Fusion 18 P0P 99.00% $3,500 FUSION ENERGI SE
Fusion 18 P0S 99.00% $3,500 FUSION ENERGI PLATINUM / FUSION ENERGI TITANIUM
Go to the top of the page
 
MadPiet
post Nov 9 2018, 05:41 PM
Post#28



Posts: 2,547
Joined: 27-February 09



You can use cascading comboboxes to filter the list down though... there are ways to get around that problem.
Go to the top of the page
 
chrismja
post Nov 9 2018, 06:08 PM
Post#29



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL



QUOTE
MadPiet
You can use cascading comboboxes to filter the list down though... there are ways to get around that problem.

That is a GREAT point! That's why you're the expert! I knew this, but hadn't thought about it! smile.gif
Go to the top of the page
 
MadPiet
post Nov 9 2018, 06:14 PM
Post#30



Posts: 2,547
Joined: 27-February 09



Honestly, I'd fix the table design(s) and then worry about the UI later. If the database doesn't answer your questions, a pretty interface won't matter anyway.

For the core stuff (drive train, engine size, etc) you could put those in the Vehicles table (identified by VIN), and then for the optional stuff, you could use a child table (subform inside vehicle main form). That should be flexible enough to choose all the options.

My two cents is to start small. When I did this stuff in school, I was told by one of my professors to test tables/relationships with at most 3 records in a single table. Make sure that part works, then you can worry about adding real data later.
Go to the top of the page
 
chrismja
post Nov 9 2018, 06:38 PM
Post#31



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


Thank you.
I've already started reading and studying on what George pointed me to earlier and I'm going to start a new database and start trying to see what I can figure out with your suggestions. Once again, the database works as I have it now and has been working for the last 12-13 years since I first built it so we can continue using it until I can get better and improve it!
One thing I don't think I had mentioned previously and should have is that all of my raw data (in regards to the inventory, options, packages, motor codes, transmission codes, etc) comes straight out of our computer system - so that is one limitation - I have to deal with data that is less than perfect when it comes into the database.
Manually entering this info is a no go because we're talking about hundreds of vehicles here that change daily. The only manual entry is whenever the rebates change, someone has to go in and change them. Other than that - someone will input a stock number as a parameter to generate a price tag for that unit (many of these are done daily) and someone will daily export a list of all vehicles in stock with their prices after the have gone through the calculation process.
Go to the top of the page
 
MadPiet
post Nov 9 2018, 06:44 PM
Post#32



Posts: 2,547
Joined: 27-February 09



You should be able to import all the data from the other systems... If you're having trouble with that, post back. (Almost wish the transformation features from PowerBI were available in Access... that would make things infinitely easier!)
Go to the top of the page
 
chrismja
post Nov 9 2018, 07:10 PM
Post#33



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


Well the data that comes out of the system is often inconsistent despite my best efforts to get everything consistent! For instance, sometimes F150 comes through as F-150, F150, F150 Series, F150 Light Duty, etc.
But I've made it work by having a table and a helper query that helps me normalize these (the best way I know how to) by saying these are all alternate names for F150 and consider them all "F150".
Go to the top of the page
 
MadPiet
post Nov 9 2018, 10:32 PM
Post#34



Posts: 2,547
Joined: 27-February 09



Awesome! Nice job!

Now the next part to figure out is how to deal with the attributes of a vehicle that every vehicle has (that you're interested in) and add those fields to your Vehicles table.

How does the "Package" stuff work? Is it basically a "bundle" of features? (If you can give 3 examples, it makes it easier to see the pattern). Once we have that, we can figure out how to add that to this. Oh wait... you said a vehicle can have only one / at most one "package". Then that would just be an attribute of a vehicle. (You could create another table that you use as a pick list.)

Is there an easy way to put all the Incentive stuff in a single table and all the Rebate stuff in another table? (You could easily join it back to a model... just add a ModelID (long integer) to the Rebate table and to the Incentive table... (You need the key for the parent vehicle table to do that, though).
This post has been edited by MadPiet: Nov 9 2018, 10:42 PM
Go to the top of the page
 
chrismja
post Nov 10 2018, 10:08 AM
Post#35



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


Yes, the "package" is normally a bundle of features, but fortunately we don't have to concern ourselves with anything other than just the package code. The package code is always 4 digits. 3 numbers followed by a letter. 100A, 300A, 600A. Each vehicle can only have one package code but multiple vehicles (across multiple carlines) could have the same package code.
For instance a Fusion might have a package code of 300A and it's also possible that a F150 has a package code of 300A.
This is what currently happens: I import the inventory table with all of it's data. This info comes from our system without any manual input from anyone.
Once the inventory data is in the db, the individual queries look at the inventory table and looks to compare the incentive (i.e. rebate) tables to the inventory and match up the inventory and it matches on the following things:
Vehicle line (carline - F150, Fusion, etc)
Year model (18, 19)
Body code (which is the 5th, 6th, and 7th digits of the vin number - i.e. P0H, W1E, P3F)
Package code (100A, 300A, 601A, etc.)
Motor (which is the 8th digit of the Vin)
Transmission (comes over in the inventory table from our system)
And possibly a couple other things in certain instances but those are rare.

Then once everything is matched up - it sums all the rebates that each vehicle qualifies for based on all these things and also looks at what percentage it should be priced at based on the percentage stored in the rebate table for that body code. Then it calculates the price based on that percentage and subtracts the rebates that that vehicle qualifies for and gives a final price after rebate.
Go to the top of the page
 
MadPiet
post Nov 10 2018, 04:33 PM
Post#36



Posts: 2,547
Joined: 27-February 09



Maybe the easiest way to do this is to have Rebate tables ... so you could join on one (or more) columns to return the rebate value. Then you may be able to just add "across". Too weekendy outside to dig into this right now, though. <g>
Go to the top of the page
 
chrismja
post Nov 12 2018, 06:29 PM
Post#37



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


I have started rebuilding my database with help and suggestions from many of you.
Does it look like I'm on the right path? Any suggestions of anywhere you see that I may be veering off course? Thanks!
Attached File(s)
Attached File  New_Inventory_Test.zip ( 264.89K )Number of downloads: 1
 
Go to the top of the page
 
chrismja
post Nov 12 2018, 07:51 PM
Post#38



Posts: 242
Joined: 14-January 05
From: Muscle Shoals, AL


I apologize, this is the most recent copy of the updated rebuilt database. I couldn't figure out how to edit my previous post!
Attached File(s)
Attached File  New_Inventory_Test.zip ( 327.58K )Number of downloads: 1
 
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2018 - 05:22 AM