kgriba said:
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
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
