Cant get textbox to read query.

[Guest]

Hi i have a form & table ("Form1" & "Table1"), Certain fields in the table
has got a "Scoring value" - I created a query for this, " query1". but now I
can't get the one textbox in the form to read this query, this is a running
score and it actually updates the query info but not the table and also not
the form.
Please assist

 

[Guest]

Hi

The simplist method would be to bring "all" the fields from Table1 (that are
used on Form1) into the query and base Form 1 on that query instead of the
table.

 

[Guest]

Thanks wayne,
I tried that, but then I can't seem to add any new records, or change the
current records when im on the form, am I doing something wrong?

 

[Guest]

Do you have any other tables in the query.

If so - how are the conected (relationship). Check you have done this
correctly.

Oh yes. In your 1st post you said that you could not update the table with
the "sum". Don't do this as it will cause many problems. Just use the
calculated result as and when you need it and never store it.

 

[Guest]

I have a table that if for example an amount is over 80000, the person gets a
score of 5, there are a couple of these fields, so the scoring at the end
will be a combined score from all these fields, i created queries to get all
of these sorted out, and then created a total score - query, so i basically
need this textbox/field to update with this query as the rest of the
fields(that makes up the scoring) are updated.
So i basically need it in table form so that i can add more records, export
etc...

 

[Guest]

Not really of course but it sounds like it could be improved.

The table(s) should only store data.
In this case the I assume you have a table with people details in and this
is linked to another table with the amounts.

Give it a try.

Create a new table
Name = tblPeople
PersonsID = Autonumber (primary field)
Name = Text

Create a new table
Name = tblAmount
Amount = Number (NOT primary field)
PersonsID = this is the linking field to tblPeople

Create a new query
Bring in both new tables

View SQL
Use something like this (note I have included an iif to work out the
"score").
Note aircode so may need some working.

SELECT tblPeople.PersonsID, tblPeople.Name, tblAmount.Amount,
IIf([Amount]>80000,5) AS Score
FROM tblPeople INNER JOIN tblAmount ON tblPeople.PersonsID =
tblAmount.PersonsID;


I think that maybe your basic DB could be able to "reduced" like this. Of
course the score could be worked out on a form (I just prefer to do most
calculations like this in a query).

Give it a try and see if this what you are looking for.

 

[Guest]

It is actually just one table,
It is basically an application - if a person applies for a certain thing
they get scored to see if they qualify for a sponsorship. all the info is
added into one table, with the scoring as well, because all the fields relate
to that person, single breadwinner, salary,years of service etc.... so there
are no other tables that i am using, the score query works, but i cant get
the textbox to read it, i have tried it as a macro as well, but that doesnt
work either, the scoring will be a part of that persons record.

I do appologize for being an idiot in this regard, but thanks for getting
back to me...I am a real Ass when it gets to access

Not really of course but it sounds like it could be improved.

The table(s) should only store data.
In this case the I assume you have a table with people details in and this
is linked to another table with the amounts.

Give it a try.

Create a new table
Name = tblPeople
PersonsID = Autonumber (primary field)
Name = Text

Create a new table
Name = tblAmount
Amount = Number (NOT primary field)
PersonsID = this is the linking field to tblPeople

Create a new query
Bring in both new tables

View SQL
Use something like this (note I have included an iif to work out the
"score").
Note aircode so may need some working.

SELECT tblPeople.PersonsID, tblPeople.Name, tblAmount.Amount,
IIf([Amount]>80000,5) AS Score
FROM tblPeople INNER JOIN tblAmount ON tblPeople.PersonsID =
tblAmount.PersonsID;


I think that maybe your basic DB could be able to "reduced" like this. Of
course the score could be worked out on a form (I just prefer to do most
calculations like this in a query).

Give it a try and see if this what you are looking for.




--
Wayne
Manchester, England.

I have a table that if for example an amount is over 80000, the person gets a
score of 5, there are a couple of these fields, so the scoring at the end
will be a combined score from all these fields, i created queries to get all
of these sorted out, and then created a total score - query, so i basically
need this textbox/field to update with this query as the rest of the
fields(that makes up the scoring) are updated.
So i basically need it in table form so that i can add more records, export
etc...

 

[Guest]

I just thought of something, is there a way to have a a field in a table
reflect the info in a query??

 

[Guest]

No worries

Sorry if I didn't tell correctley - english is not my 1st.

Anyway

Access is a relational database system.

You can "relate" table and data. So at the moment I think you will have a
number of records with (for example) John each showing an amont and from that
you will have a query showing the amount converted into "scores". You want
to export this infomation and do other "stuff" with it.

OK.

You need a table that has just the personal information regarding John (and
anyone else).
Of course this is a problem in itself as how will you know which "John" (if
there are more than 1.
Access will work this out for. You need to create the a new table and the
1st field set the format to Autonumber. This "IS" the record.

Cteate a table with
TableName = tblPersonalDetails
PersonalID = Autonumber
1stName = Text
2ndName = Text
Date of birth = Time/Date
AddressLine1 = Text
AddressLine2 = Text
AddressLine3 = Text
AddressLine4 = Text
PostZipCode = Text
Etc
Etc

DON'T put any work "stuff in this table - just the personal details.

Next create another table
TableName = tblEmployment
PersonalID = Number (this is the linking field)
Location = Text
DateComenced = Time/Date
Title = Text
etc
etc

Create another table
TableName = tblScores
PersonalID = Number (This is the linking field)
Amount = Number

Open the relationship window.

Drag the PersonalID field from tblPersonalDetails accross to the other 2
table one at a time. Check the referential box (this means that you can't
create a new record in tblEmployment or tblScores unless there is a
coresponding record in tblPersonalDetails - you can't have a score for
someone who does not exist).

Next create a query and search this forum on "if" and you will find out how
to use the Amount in tblScores to give each person thier score. You will
also be able to run a sum of these scores to give a personl thier overall
score.

Oh yes

I do appologize for being an idiot in this regard, but thanks for getting
back to me...I am a real Ass when it gets to access
You don't t>
I do appologize for being an idiot in this regard, but thanks for getting
back to me...I am a real Ass when it gets to access

You don't need to say sorry - I ask some "really" stupid questions on this
forum and always get an answer. I pent years makeing MS Works DB's and only
swaped over the access in 2002. So I am still a newby in comparision to most
other on this site.



--
Wayne
Manchester, England.

It is actually just one table,
It is basically an application - if a person applies for a certain thing
they get scored to see if they qualify for a sponsorship. all the info is
added into one table, with the scoring as well, because all the fields relate
to that person, single breadwinner, salary,years of service etc.... so there
are no other tables that i am using, the score query works, but i cant get
the textbox to read it, i have tried it as a macro as well, but that doesnt
work either, the scoring will be a part of that persons record.

I do appologize for being an idiot in this regard, but thanks for getting
back to me...I am a real Ass when it gets to access

Not really of course but it sounds like it could be improved.

The table(s) should only store data.
In this case the I assume you have a table with people details in and this
is linked to another table with the amounts.

Give it a try.

Create a new table
Name = tblPeople
PersonsID = Autonumber (primary field)
Name = Text

Create a new table
Name = tblAmount
Amount = Number (NOT primary field)
PersonsID = this is the linking field to tblPeople

Create a new query
Bring in both new tables

View SQL
Use something like this (note I have included an iif to work out the
"score").
Note aircode so may need some working.

SELECT tblPeople.PersonsID, tblPeople.Name, tblAmount.Amount,
IIf([Amount]>80000,5) AS Score
FROM tblPeople INNER JOIN tblAmount ON tblPeople.PersonsID =
tblAmount.PersonsID;


I think that maybe your basic DB could be able to "reduced" like this. Of
course the score could be worked out on a form (I just prefer to do most
calculations like this in a query).

Give it a try and see if this what you are looking for.




--
Wayne
Manchester, England.

I have a table that if for example an amount is over 80000, the person gets a
score of 5, there are a couple of these fields, so the scoring at the end
will be a combined score from all these fields, i created queries to get all
of these sorted out, and then created a total score - query, so i basically
need this textbox/field to update with this query as the rest of the
fields(that makes up the scoring) are updated.
So i basically need it in table form so that i can add more records, export
etc...

:

Do you have any other tables in the query.

If so - how are the conected (relationship). Check you have done this
correctly.

Oh yes. In your 1st post you said that you could not update the table with
the "sum". Don't do this as it will cause many problems. Just use the
calculated result as and when you need it and never store it.

--
Wayne
Manchester, England.



:

Thanks wayne,
I tried that, but then I can't seem to add any new records, or change the
current records when im on the form, am I doing something wrong?

:

Hi

The simplist method would be to bring "all" the fields from Table1 (that are
used on Form1) into the query and base Form 1 on that query instead of the
table.

--
Wayne
Manchester, England.



:

Hi i have a form & table ("Form1" & "Table1"), Certain fields in the table
has got a "Scoring value" - I created a query for this, " query1". but now I
can't get the one textbox in the form to read this query, this is a running
score and it actually updates the query info but not the table and also not
the form.
Please assist

 

[Guest]

Don't do it in this case. You DO NOT want to ever store calculated results.

If you have John with 3 amounts of 1, 2 and 3 his overall will be 6
If you add another amount the overall will be wrong. You would need to keep
updateing the allover result that you have stored.

Best to just use the overall score (from your query) in a form or query and
then use this is reports, exports, etc. This way it will always be up to
date.

 

[Guest]

Thanks alot,
I am waiting for another guy to come to my desk just to assist with your
last explanation, can I please ask you just to provide me with the way to
have one field in the table mirror the info in the query, as the info will
not change, once it is typed in it is final, as this should also work if we
don't succeed in your previous one.

And then how do I do a rating on all the assist that you have given me....

 

[Guest]

You can save results into a table but "it is a mistake". Don't do it. If
you want to type some thing into a table field then do this but any
calculation is the result of a serise of varibles and by their very nature
these can change - which will make the saved results wrong.

As a basic rule you should not be working on/in the table itself. Alway use
a query or form to do "stuff". if you are (as you should be) working on the
form or query the details you are wanting (to save into the table) will be
there anyway so there is no reason to save them.

It may look easier at first but the ease is not worth the problems will
may/will cause.

__________________________________

TOP TIP - Do not save calculated results into a table.

__________________________________