yeah, about the 1
i said that back in an old version of the DB hehe!
Ok, so pretty much what I want is this: When someone choses the projuct number, it will automatically fill the Client, Project name, Phase (this is a time sheet, and the people who I work with are Lazy, lol)
so say like Project number 355 is for the ministry of health, project name: security camera install, phase 2.
and the project numbers should be from a drop down box, people cant make their own
I really hope this makes sense >.<
Thanks for your patience, Paul
ok, if i understand you, you will need to make some drastic changes to your database.
i think you've misunderstood the concept of "normalisation". search the forums here and google. basically, each separate 'thing' should have its own table with sensible names. e.g., tblEmployee, tblClients, tblProjects, tblPhases, tblTypes, tblItems... the list goes on but depends on what data you actually want to store. (names like "q" would not, i don't think, EVER be intuitive for a table).
typically, tables are prefixed with "tbl", queries with "qry", reports with "rpt", forms with "frm", modules with "bas"... and the list goes on (this is called a "naming convention" - if you search this forum or other website you can find a lot on it, e.g., queries are generally "qry", but a parameter query might be "pqry", and a crostab query might have "xqry")
then, you need to make sensible field names. calling every primary key (PK) in your database as "ID" will confuse you AND access in the future, depending on how many tables you end up having. i tend to do things following the table name, so tblEmployees would have the fields "EmployeeID"; "EmployeeName"; etc (never use "Name" or "Date" on their own, as they are 'reserved' access words), and tblClients would have "ClientID"; "ClientName"; "ClientAddress"; "ClientPhone"... etc. and so on.
Then you need to know how to connect the tables to 'link' or 'relate' all the data. for example, a project may have many phases, a client may have many projects.
so how do you link these? well, it's largely up to how your data flow goes, but most things are self-evident.
as a basic example: a project requires a client, as well as other project details. now, you already have a client table, so you don't need to put all the client data into the project data all over again, you just link the tables together via what is called a "foreign key" (FK)- a foreign key is basically the primary ID of the client table in the project table. it is sensible to call the client foreign key identically to the primary key, as it avoids your confusion on the future.
Now, your primary keys ought to be "autonumber" type, so that when you add a new record (a project, say) access will automatically create a number for you which is bigger than the last number you used (it never inherently reuses that numbner, even if you delete it) but your foreign keys only have to
store that number, not generate it, so it is only a "number" type
for example, the project table could look something like this:
tblProjects
------------
ProjectID (PK, Autonumber)
ProjectName (Text)
Description (Text/Memo)
ClientID (FK, Number)
InvoiceSent (Y/N)
.
.
.
i tend to make the ClientID an easy combobox on my form. i was once told, and i regurgitated just to clarify, how to properly do this
in this thread here. (start with post #18 on page two, and read ALL the way to the end of the thread).
then: back to what you said you wanted - to select a project number and have some details automatically filled. there are two scenarios (for example):
1) you have a list of clients and want to update a particular detail/field - so you want to select the client from a drop-down box (combobox) or a listbox and have their details displayed for you so you can change any of them.
2) you have an existing project, and you want to select one from the combo/list and add a new phase/client/item/job to it (or whatever you add to your projects). in this case, you would have a similar setup to (1), but you would also have a 'subform' of the data which is related to the project - which one specificly will depend on your own database and requirements.
now. there are some other basic things i would urge you to do, which aren't directly related to setting up tables, queries etc:
A) always keep backups of your database. there are hundreds of threads on this forum alone which are dedicated to various methods of backing up - from physically copying the file in windows explorer, to making access do it at the press of a button, to automating at a specific time (e.g., weekly, monthly, other) etc etc...
B) i personally, though i don't know about anyone else, choose to setup my databases to "compact on close". in access 2007, you get there by selecting the office orb, then "access options" right down the bottom, then "current database", then check the "compact on close" option. what this does i'm not exactly sure, but the most obvious effect is that it can drastically reduce your database file size - to do this only sporadically, you can go to the office orb, then "manage" then "compact and repair".
i did point (B) on your database and it went from almost 4 Mb to just under 400 kb.
i think your misunderstanding of database design is why you are having difficulty explaining what you want, and why the rest of us are having difficulty understanding your wants. hang in there! i'm pretty sure my first database was no better than yours - just have a think about what i've said here, and read the link i've provided, then have a good attempt at starting again, then come back if you get stuck - we'll all still be here to help
