Re: Lookups
- From: Philip Herlihy <bounceback@xxxxxxx>
- Date: Wed, 12 Nov 2008 22:09:10 +0000
kgriba wrote:
Thanks everyone! Yes, I clearly get the sense that a new way of thinking about a problem needs to be embraced by my Excel-steeped brain! I will take a look at the links you have all sent. I think part of my problem has also been that I don't know where to look as I do not yet have enough Access lingo under my belt to ask the right questions in the first place.
A bit more on the problem I am trying to tackle:
I work for a developer and we build condominiums. If, for example, I have a listing of 130 units being built in a building, but within that listing of 130 units, there may only be a total 6 unique floorplans.
So to that end, I created a table listing the 6 unique floorplan types, each with their respective specifications, such as square footage, bedroom count, bathroom, count, etc.
Then I created a second table, listing all 130 units in the entire building, each unit given a number for addressing purposes. I then want to assign each unit a floorplan type (I did this easily enough with an Access lookup and linking to the first table I just mentioned above) but then I want the subsequent columns to automatically reflect the remainder of the specs, that is, just by initially assigning the suite a floorplan type, the subsequent columns show the square footages, bedroom count, etc.
Hmmmm... maybe I pull this together in a final report? Because the relationship has already been made... hmmm... I will try that...
Eventually, I want to move this towards tracking the customers that purchased each unit, what they paid, and what their respective finishes and upgrades, and upgrades costs, are for their new suite. I want to, through the reports feature of Access, be able to (i) look up all the info associated with a customer, and (ii) alternately look up the info associated with a suite.
Anyways, thanks again everyone, I will do some more reading, vid watching and experimentation to see what I can come up with.
Cheers,
Kristine
"John W. Vinson" wrote:
On Tue, 11 Nov 2008 15:05:01 -0800, kgriba <kgriba@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
I am super new to Access, but am seasoned in Excel... have come to a point were I think my Excel doc should really be in Access... so I'm trying it out.Philip's given you a good thumbnail overview; below are some more resources.
I see that Lookups in Access are not the same thing as they are in Excel, but I want to be able to, in Access, lookup a value and its corresponding piece of information as can be done in an Excel lookup function.
I suspect that I am tackling it wrong... my mind is so Excel... can anyone point me in the right direction to at least get me started? I'm not having much luck using the Help feature in Access, as all it does of course is explain the Access version of a Lookup.
Thanks!
Do be aware that Access is emphatically NOT "Excel on steroids"; the tools
(VLookup say) that you would routinely use in Excel are inappropriate in
Access! There is a DLookUp function but it is actually rather uncommon to need
it! Instead you will use Tables related by Queries to pull information
together.
For some tutorials see
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page:
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
-
John W. Vinson [MVP]
This may be easier than you expect. If you've correctly designed your tables (sounds like you have) then each suite in one table, and each floorplan-type in the other, will have an identifier (termed the "primary key") which is unique. (We often include an Autonumber field in a table just for this purpose). Then you could add a column in the Suites table called "type" which will be assigned the identifier ("key") of one of the floorplan types.
Worth noting in this case that you'd only do this way if Types really are fixed - if the square footage varied, for example, you could end up with an infinite number of Types, which isn't neat. But if the "Balmoral" suite always has 5 rooms and 120 square feet and a blue bathroom then you can think of those attributes as "dependent" on the name "Balmoral". An important principle is that each table represents an "entity" which makes sense and "hangs together" - and you often need more tables than you'd first have thought.
Anyway - you have to tell Access that the "Types" field in the "Suites" table corresponds to primary keys in the "Types" table. Access will sometimes guess correctly if the field names are the same, but you can set up a relationship by dragging fields between tables in the "Relationships" panel (preferred). Alternatively, you can do the same (just for one query) in the Query Builder. You "add" both tables to the query, then drag one field to the corresponding field in the other table. Then Access knows how to link the two tables.
Then, in the middle part of the Query Builder, simply drag the fields you want onto the matrix. You can refine this by adding sorting and conditions (later) but when you run the query you'll get just what you want. At this point you'll be hooked, and you'll wonder why anyone tries to do this in Excel.
Terminology - an Access "report" is a separate entity (based on a query) which formats the output for printing. When you're ready to fool around with this, stick to the Report Wizard!
Anyway, you'll need to read up, or watch some videos, and try a few things. Note the "backup" option under the File menu!
Phil
.
- References:
- Lookups
- From: kgriba
- Re: Lookups
- From: John W . Vinson
- Re: Lookups
- From: kgriba
- Lookups
- Prev by Date: Re: How do I use count if in access?
- Next by Date: Re: Encoding/Decoding
- Previous by thread: Re: Lookups
- Next by thread: Re: Lookups
- Index(es):
Relevant Pages
|
