my first question about quieries

[val_87]

So as i said i am looking to make my job a bit easier and no money will be coming in any time soon from the top to do so, so its up to me!

Here is my situtation.
I unedrstand the concept of tables, queries and forms. The task i am trying to accomplish is not really that complicated. But still a few things i don't know how to do.

There are 4 stages in a process, when user enters info i will not have other information until later, for example personal details of client are available straight away however date of discharge or tests will be available at a later state. All together there is 4 stages. It is important to note here that ACCESS auto ID won't do the job to identify as id will have to be assigned manualy as a separate data unit.

Now. I created a big table with 47 values. And a first query and a form to input that information. Here is a problem, when i create a second query to input stage 2 for that client, how do i tell it to add info to the same row in the table as in continuation where query 1 left off? Same for stage 3 and 4.

Or would it be more easier to have 4 different tables with each stages and a report function to display information from the 4 tables in one report?

This is my first problem and i am stuck on it....
Does anyone know how to solve it?
Any help will be greately appreciated.... ask if you need to know more.
As i said the database itself isn't that complicated.

 

[Minty]

Now. I created a big table with 47 values./QUOTE]

This tends to indicate you possibly haven't normalised your data.

These 47 fields are maybe test results based on your description. What happens if you add another test? You probably should store the tests in a separate table - Simplified layout example;

tblTests
Field - Description
TestID - Autonumber - (PK)
TestDesc - Text Description of the test.

tblTestResults
Field - Description
ResultID - Autonumber - (PK)
TestID - number (FK) from the table above
Result - Text or whatever

 

[val_87]

Now. I created a big table with 47 values./QUOTE]

This tends to indicate you possibly haven't normalised your data.

These 47 fields are maybe test results based on your description. What happens if you add another test? You probably should store the tests in a separate table - Simplified layout example;

tblTests
Field - Description
TestID - Autonumber - (PK)
TestDesc - Text Description of the test.

tblTestResults
Field - Description
ResultID - Autonumber - (PK)
TestID - number (FK) from the table above
Result - Text or whatever

Thank you for the reply. I understand that, the tests won't change. What i don't understand is how to get tables / queries to talk to each other via relationships? As i described above, when first stage is done, so that second stage would add information to the same line in the table continuing what started in second stage.

 

[Minty]

Are there always 47 tests carried out?
You sound like you are (still) thinking in Excel terms. The line in the table is your record.

Using your current data layout (which I am fairly certain will give you problems in the future) you would simply find the record and display the fields you need to update for stage 2. ditto stage 3...

Have a look at the Northwind sample access database for a search form to start you off.