check mark in query

[Guest]

Hi, I have created a query for the table with check mark. For some reason I
can not check my checkmark in query, but I still able to change it in the
table itself. I have never had this problems before (in diffent databases).
ANy ideas?

 

[Guest]

Hi.

For some reason I
can not check my checkmark in query, but I still able to change it in the
table itself.

The query must be an updateable query in order for you to change values in
these records. Does the query contain grouping, or aggregate functions, or a
UNION query? These aren't updateable queries. Do the underlying tables
contain primary keys (especially on linked tables)? If not, then the queries
may not be updateable. If this information doesn't help you pinpoint the
problem, then please post the SQL statement, and we'll try to help.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

This query is a combination of two tables, linked by customer number. If that
check box would be unavailable, is there any other way to mark record as
complete. I use this query for billing, and want to have something to check
that I already billed this person.
Thanks

 

[Guest]

Hi.

This query is a combination of two tables, linked by customer number.

In the relationship between these two tables, does the field with the
customer number have a unique index for the table on the "one" side of the
relationship, and is this field "Required" in that table as well? If the
customer number field is the primary key of the table on the "one" side, then
it meets these requirements (and then some).

Are either of these tables linked tables? If so, are they Access (Jet)
database tables, or in a client/server database, such as SQL Server?

If that
check box would be unavailable, is there any other way to mark record as
complete.

If the query is an updateable query, then the check box will be updateable,
too. Posting the SQL statement of the query may be helpful if the questions
above don't shed any light on why your query is currently not updateable.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

I occasionally get this if I join two tables and accidentally drag a field
down from the wrong one, ie, from the "one" table instead of the "many" table.

Randall Arnold

 

[Guest]

Actually it is a little bit more complicated. Both tables are created in Palm
Handbase database and then exported to access through ODBC merge. Those
databases have a linked field that I use to link them in access database. So
what I have is one record in main database and then several encounter for
that patient with billing. My query is filtered only for discharged patients.
So , if the patient is discharged, I can see him in the billing report and
generate that billing statement. I created the check mark to separate
discharged patients that are already billed from those new discharged
patients that are not billed yet.

To answer your question, neiter of linked fields are selected to be
required. Obviously, they are entered on each patient. Those two tables are
stand alone and not linked to anything else, except between each other.
Again, I am open to any other solution to separate billed from not billed
patients.
Thanks.

 

[Guest]

I tried to create check box in both tables, neither works in query

 

[Duane Hookom]

You should edit data in a form where you can use a check box control. You
can quickly create a datasheet view form that uses a check box.

 

[Guest]

It still sounds like your query is not updateable, and based on what you just
posted maybe the problem lies in how your tables are brought in. are you
able to change field values of the individual tables from within Access?

Randall Arnold

 

[Guest]

yes, I can update fields in individual tables.

It still sounds like your query is not updateable, and based on what you just
posted maybe the problem lies in how your tables are brought in. are you
able to change field values of the individual tables from within Access?

Randall Arnold

 

[Guest]

I have created a form, but having the same problem. My checkbox is not
editable.

 

[Duane Hookom]

I don't see where you have ever provided the SQL view or the primary/foreign
key relationship.

 

[Guest]

Hi.

So
what I have is one record in main database and then several encounter for
that patient with billing.

That "one record in the main database" is the record in the table on the
"one" side of the relationship. This table needs to have either a primary
key on the customer number field or else a unique index that is required and
doesn't allow NULL's. The customer number field in the other table, the
"many" side, needs to have that field required and not allow NULL's, either.

That way, your query will be updateable and you'll be able to use it as the
Record Source Property for a form that displays the data for you to mark
those check boxes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

I am sorry, how do I do it?

 

[Guest]

Open the query in Design mode, then under View in the menu select SQL View.
Copy and paste the SQL code to a reply here. You can also just describe the
primary keys and relationships.

Randall Arnold

 

[Guest]

SELECT Billing.Floor, Billing.Room, Billing.Name, Billing.MR,
Billing.Hostipal, Billing.[Admit date], Billing.Encouter, Billing.Diagnosis,
Billing.D_C, Billing_enc.Date, Billing_enc.Charge, Billing_enc.[Complete Bill
1], Billing.Complete
FROM Billing INNER JOIN Billing_enc ON Billing.Encouter = Billing_enc.MR
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));

Billing is main table linked to Billin_enc. The field Encounter in Billing
is linked to MR in billing_enc. Again this is done this way because of
original database in Handbase.
Billing.Complete is my checkmark that I would like to be able to edit and it
is located in Billing table. Billing_enc[Complete Bill 1] is another
checkmark, but now in billing_enc table. I can not edit either, but just need
to use one. I have created checkmarks in both tables for troubleshooting.
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));
Query is filtered by hospital and by patients that were discharged
(checkmark checked in Billing database)

 

[Duane Hookom]

And "the primary/foreign key relationship"? Is Encouter the primary key in
the Billing table? If it isn't, what happens when you make it the primary
key?

--
Duane Hookom
MS Access MVP

SELECT Billing.Floor, Billing.Room, Billing.Name, Billing.MR,
Billing.Hostipal, Billing.[Admit date], Billing.Encouter,
Billing.Diagnosis,
Billing.D_C, Billing_enc.Date, Billing_enc.Charge, Billing_enc.[Complete
Bill
1], Billing.Complete
FROM Billing INNER JOIN Billing_enc ON Billing.Encouter = Billing_enc.MR
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));

Billing is main table linked to Billin_enc. The field Encounter in Billing
is linked to MR in billing_enc. Again this is done this way because of
original database in Handbase.
Billing.Complete is my checkmark that I would like to be able to edit and
it
is located in Billing table. Billing_enc[Complete Bill 1] is another
checkmark, but now in billing_enc table. I can not edit either, but just
need
to use one. I have created checkmarks in both tables for troubleshooting.
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));
Query is filtered by hospital and by patients that were discharged
(checkmark checked in Billing database)

Open the query in Design mode, then under View in the menu select SQL
View.
Copy and paste the SQL code to a reply here. You can also just describe
the
primary keys and relationships.

Randall Arnold

 

[Guest]

Brilliant,
I made encounter a primary key and my checkmarks became editable. What was
the secret?
Thank you for your help

And "the primary/foreign key relationship"? Is Encouter the primary key in
the Billing table? If it isn't, what happens when you make it the primary
key?

--
Duane Hookom
MS Access MVP

SELECT Billing.Floor, Billing.Room, Billing.Name, Billing.MR,
Billing.Hostipal, Billing.[Admit date], Billing.Encouter,
Billing.Diagnosis,
Billing.D_C, Billing_enc.Date, Billing_enc.Charge, Billing_enc.[Complete
Bill
1], Billing.Complete
FROM Billing INNER JOIN Billing_enc ON Billing.Encouter = Billing_enc.MR
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));

Billing is main table linked to Billin_enc. The field Encounter in Billing
is linked to MR in billing_enc. Again this is done this way because of
original database in Handbase.
Billing.Complete is my checkmark that I would like to be able to edit and
it
is located in Billing table. Billing_enc[Complete Bill 1] is another
checkmark, but now in billing_enc table. I can not edit either, but just
need
to use one. I have created checkmarks in both tables for troubleshooting.
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));
Query is filtered by hospital and by patients that were discharged
(checkmark checked in Billing database)

Open the query in Design mode, then under View in the menu select SQL
View.
Copy and paste the SQL code to a reply here. You can also just describe
the
primary keys and relationships.

Randall Arnold

 

[Guest]

I made encounter a primary key and my checkmarks became editable. What was

the secret?

It's no secret. You've had the right answer for hours but you ignored it.
It's right here:

http://www.microsoft.com/office/com...ries&mid=f8f9a8d5-057d-4aef-bb85-c95cac28fcd0

It reads:

"That 'one record in the main database' is the record in the table on the
'one' side of the relationship. This table needs to have either a primary
key on the customer number field or else a unique index that is required and
doesn't allow NULL's. The customer number field in the other table, the
"many" side, needs to have that field required and not allow NULL's, either.

That way, your query will be updateable and you'll be able to use it as the
Record Source Property for a form that displays the data for you to mark
those check boxes."

I asked you for the SQL statement so that we could tell you exactly what
fields and tables needed changes. Since you didn't offer the SQL statement
till much later, I had to identify the table name at the time by what you had
called it, "one record in the main database," and the field that linked the
two tables together, "customer number."

The more vague the information you give, the longer it will take to help you
because we can't see your database nor the names. When we used the exact
same terminology that you did, you didn't understand the table name Billing
was actually what you had called "one record in the main database," and the
Encouter field in the Billing table was actually what you had called
"customer field," and MR in the Billing_enc table was what you had called
"customer field" that it linked to.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Brilliant,
I made encounter a primary key and my checkmarks became editable. What was
the secret?
Thank you for your help

And "the primary/foreign key relationship"? Is Encouter the primary key in
the Billing table? If it isn't, what happens when you make it the primary
key?

--
Duane Hookom
MS Access MVP

SELECT Billing.Floor, Billing.Room, Billing.Name, Billing.MR,
Billing.Hostipal, Billing.[Admit date], Billing.Encouter,
Billing.Diagnosis,
Billing.D_C, Billing_enc.Date, Billing_enc.Charge, Billing_enc.[Complete
Bill
1], Billing.Complete
FROM Billing INNER JOIN Billing_enc ON Billing.Encouter = Billing_enc.MR
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));

Billing is main table linked to Billin_enc. The field Encounter in Billing
is linked to MR in billing_enc. Again this is done this way because of
original database in Handbase.
Billing.Complete is my checkmark that I would like to be able to edit and
it
is located in Billing table. Billing_enc[Complete Bill 1] is another
checkmark, but now in billing_enc table. I can not edit either, but just
need
to use one. I have created checkmarks in both tables for troubleshooting.
WHERE (((Billing.Hostipal)=[Hospital]) AND ((Billing.D_C)=-1));
Query is filtered by hospital and by patients that were discharged
(checkmark checked in Billing database)



:

Open the query in Design mode, then under View in the menu select SQL
View.
Copy and paste the SQL code to a reply here. You can also just describe
the
primary keys and relationships.

Randall Arnold