blackcat said:
i have title information which holds no cass,cds, roylty information,
production info, etc on table a, other tables which store costs etc,
My suggestion would be to set up several Tables, each of which holds
related information, rather than one Table in which each record tells
you all you ever want to know about a given title.
Apparently, each record in your current main Table, [table a], describes
a work of art, the storage medium on which it's stored (cassette, CD),
information about the copyright holder, production company, etc.
Another Table, [costs], includes info on costs of production, I guess.
(There might be others which you did not mention.)
i then
us a dlookup to pull this info into table a,
If you already have a Query that uses DLookup(), is there really a need
to store the results? Your Query may already give you what you need
there, and many times you can use a Query just as if it were a Table.
(For example, you can base other Queries on its results, or use it as
the basis for a Form or a Report, etc.)
i then have a macro which stores
all the calculations and belive me there are loads, this database started out
being fairly simple with just a few calculations and has grown so much!
If the calculations really are complex (for example, matrix operations
or differential equations, though my guess is you're not doing any of
these), you might want to have Excel do them. You can still use Access
to do database stuff like storing and looking up data.
i
need to do it in access as not excel as we need to report on the titles and
recall them etc. thanks for your help and advice on this
Although it would help if you could list the fields you currently have
in [table a], including a brief description if the field name isn't
suggestive of what the field contains, I'm going to make a couple of
guesses here. If I guess wrong, that will invalidate some of what I
suggest, but not necessarily all. But please consider the rest of my
recommendations to implicitly begin with the caveat "Assuming that I
have guessed correctly about the current contents of your Tables, I
suggest that you do this...".
Incidentally, it might not hurt you to look at the Northwind Traders
sample database for examples (although the products there are food
items, not works of art, but they still have production companies,
shipping companies, distributors, consumers, etc.). You don't have to
memorize or imitate what Microsoft did there, but that database has lots
of good ideas that at least you should be aware of.
Anyway, in your database, for a given work of art, you know its title;
that's a good field to leave in [table a], and you might call the field
[Title]. (Incidentally, could you come up with a more descriptive name
for your [table a] Table, such as, maybe, [Titles]? That would help to
give a better idea of what's inside the Table.)
Besides the [Title] field in [table a], you might need some unique
identifier by which you can refer to a record. The [Title] field may be
unique not, but what happens if you later wind up with, e.g., a song
called "Oklahoma!" and a musical show on CDs also called "Oklahoma!" and
a movie on DVD also called "Oklahoma!"? An Autonumber (or similar type)
field, which in this case I'd call [table aID], would allow you to
distinguish records with similar or identical [Title] fields and would
save space and time in linking other Tables to [table a]. (Yes, I'm
assuming you'll have lots of Tables, even though you have only one or
two right now.)
Some of your fields seem to deal with production companies. Especially
if the same company appears more than once in [table a], but even if
not, I'd put compamy information into a [Production company] Table, to
include stuff like [Company name], [Contact person first name], [Contact
person telephone], [ZIP code].
If you're selling these works, you might have customers. In that case,
you could use a [Customers] Table, with fields such as [Company name],
[Contact person first name], etc.
If you have info on storage media, you might want to set up a [CDs]
Table including such fields as [ISBN] (or whatever you use to identify a
CD title), [PublisherID] (this could be a link to a [Publishers] Table,
which in turn might include fields such as [Company name], etc.), [CD
location in my library], [Catalog number], etc.
If you have multiple storage media, you could either devote one field in
[table a] to a link to a [CDs] Table, another field to a [Cassettes]
Table, etc., or to have just one link to a [Media] Table. If you have
similar types of information for CDs and cassettes, the [Media] approach
would be better. In [Media], a [MediaID] field would identify a
specific storage medium for a specific work of art; a [PublishersID]
field could link to the contact information in the [Publishers] Table; a
[Type] field would identify the physical medium, such as "CD" or "cass";
a [Condition] field could contain values such as "scratched", "unused",
"barely readable", "junk", etc.
Your current [Costs] Table might also be broken down into categories, if
you have lots of fields there, but not necessarily (depends on how much
detail you're storing). At the least, you probably want to link each
record in [Costs] to one in [table a] by including a [table aID] field
in each record in [Costs]. Otherwise, you'll know the amount of money,
perhaps, but not what it means.
Please bear in mind that Access (like any RDBMS) is pretty flexible.
Your decisions don't have to be permanent, and it should not be very
difficult to modify your database to accommodate new information.
Anyway, without more details about what information you have and what
you want to do with it, it's difficult to make more specific
suggestions, but I hope you can get an idea from what I said here about
some of the possibilities. And I barely mentioned possible Queries --
they can be used to do all sorts of things with your information once
your Tables are set up in a convenient manner.
:
You would base your form on a QUERY which can contain several related
tables. Just breaking this into two pieces is not the answer though.
Sounds like your structure is badly flawed. Sounds like you should have
several one-to-many relationships.
What is in this table? What is the structure of your data? If you have
things like...
[...]
thanks for your response, so if i create a new table, how can i pull this
into the existing form that i have, ie how do i use two tables on one form
or
report. my calculations are stored in a macro, hense the reason for
needed
to have all fields on one form
Not a sufficient reason. What you put on a Form should be whatever it's
convenient for a human being to look at on the Form. To display many
controls, you can use Tabs, or Subforms, or similar devices, to allow a
user to select a small collection of related data to view or update or
input.
The calculations in your Macro may be difficult to use anywhere else.
Not everyone agrees with me on this, but my suggestion is to move some
of those calculations to named Queries that you can thereafter use in
Forms, Reports, or other Queries. You might be able to have your Macro
refer to the Query instead of doing the calculation right there.
[...]
I agree with Rick B., but this is a guideline. What I think is more
important at first is to put into one Table only closely related
information, such as whatever you need to know about a specific work of
art. If some of that information identifies its publisher, you can
include a link -- one short field -- to another Table that contains
details about the publisher. You might end up with six fields or 36;
what's important is that they logically belong with the subject matter
of your Table. The main idea here is to help you avoid doing
unnecessary work and to avoid mistakes (which can cause much unnecessary
work).
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
[...]
