Linked table query

[Guest]

Thanks to all taking the time to read my problem. I am trying to make a
query based on multiple linked tables with SQL 2K as the back end. I have
'tblBilling' with primary key 'tblBilling.invoice#', into which goes general
info about an invoice such as dollar amount, dates, and so forth. I have
seven other tables with different categories of units that could be on an
invoice, tblburied_units, tblaerial_units, and so forth, all of which also
have primary key field invoice#. In each of the linked tables I have added a
field that can be checked if the invoice contains that type of unit, so the
user can open a form with the invoice# already populated. Before we upsized
my billing form was based on a query pulling all of the fields from
tblBilling, and the one descriptive field from the seven linked tables. When
the user was filling out the frmBilling the fields from the linked tables
were simply check boxes on the form. When, for example, an invoice had
buried units on it the user checked the box for buried units which came from
tblburied_units, and was included in the query, then a command button was hit
to open frmburied_units to enter in all of the units. By checking the check
box a record was automatically created in tblburied_units with the invoice#
field, the primary key, already populated with the invoice# entered on the
frmBilling. Does this make sense? Now, however, when I try to check the
check boxes for the linked tables I hear a beep with no message at the bottom
left. I am still able to enter information into all the fields from
tblbilling, but not the other tables. From reading the posts I have made
sure all the tables had a primary key, I defined the relationships both in
the MDB and in the servers' Enterprize Manager, and added timestamp fields to
all the table, but it still will not work. Is what I am trying to do
possible? Is there another way to accomplish the goal? Is there a reason
when a make the relationships in the MDB the cascade update and delete check
boxes are greyed out? I thought maybe i was trying to include too many
tables in the query, so I tried it with only two tables and it still did not
work. Here is the query with only two tables for simplicity;

SELECT tblbilling.*, tblaerial_units.aerial_units
FROM tblbilling LEFT JOIN tblaerial_units ON tblbilling.[Invoice#] =
tblaerial_units.[invoice#];

I am very frustrated and would greatly appreciate any advice.

 

[Duane Hookom]

I would have attempted to not have "seven other tables". There may be a good
reason in your situation but it seems a bit un-normalized.

If you look at your linked SQL tables in design view, do you see the primary
key fields?

You can't expect to define more than just a visual relationship in your
front end. Relationships are always defined in the back end database.

Are you running code when you check a box?

--
Duane Hookom
MS Access MVP

Thanks to all taking the time to read my problem. I am trying to make a
query based on multiple linked tables with SQL 2K as the back end. I have
'tblBilling' with primary key 'tblBilling.invoice#', into which goes
general
info about an invoice such as dollar amount, dates, and so forth. I have
seven other tables with different categories of units that could be on an
invoice, tblburied_units, tblaerial_units, and so forth, all of which also
have primary key field invoice#. In each of the linked tables I have
added a
field that can be checked if the invoice contains that type of unit, so
the
user can open a form with the invoice# already populated. Before we
upsized
my billing form was based on a query pulling all of the fields from
tblBilling, and the one descriptive field from the seven linked tables.
When
the user was filling out the frmBilling the fields from the linked tables
were simply check boxes on the form. When, for example, an invoice had
buried units on it the user checked the box for buried units which came
from
tblburied_units, and was included in the query, then a command button was
hit
to open frmburied_units to enter in all of the units. By checking the
check
box a record was automatically created in tblburied_units with the
invoice#
field, the primary key, already populated with the invoice# entered on the
frmBilling. Does this make sense? Now, however, when I try to check the
check boxes for the linked tables I hear a beep with no message at the
bottom
left. I am still able to enter information into all the fields from
tblbilling, but not the other tables. From reading the posts I have made
sure all the tables had a primary key, I defined the relationships both in
the MDB and in the servers' Enterprize Manager, and added timestamp fields
to
all the table, but it still will not work. Is what I am trying to do
possible? Is there another way to accomplish the goal? Is there a reason
when a make the relationships in the MDB the cascade update and delete
check
boxes are greyed out? I thought maybe i was trying to include too many
tables in the query, so I tried it with only two tables and it still did
not
work. Here is the query with only two tables for simplicity;

SELECT tblbilling.*, tblaerial_units.aerial_units
FROM tblbilling LEFT JOIN tblaerial_units ON tblbilling.[Invoice#] =
tblaerial_units.[invoice#];

I am very frustrated and would greatly appreciate any advice.

 

[Guest]

Thank you Duane for answering me so promptly and so late. I am able to see
the primary Key fields, and all relationships were set up on the back end.
When at first it did not work I thought maybe I should also define them in
the MDB. It does not matter now, for I have just discovered linked
sub-forms. I am such an idiot. I have been so focused on trying to make
this work with a query that I did not explore other options. This problem
has been troubling me for weeks, and I can not believe it was so easy.
Thanks to all of you who help foundlings like me.

I would have attempted to not have "seven other tables". There may be a good
reason in your situation but it seems a bit un-normalized.

If you look at your linked SQL tables in design view, do you see the primary
key fields?

You can't expect to define more than just a visual relationship in your
front end. Relationships are always defined in the back end database.

Are you running code when you check a box?

--
Duane Hookom
MS Access MVP

Thanks to all taking the time to read my problem. I am trying to make a
query based on multiple linked tables with SQL 2K as the back end. I have
'tblBilling' with primary key 'tblBilling.invoice#', into which goes
general
info about an invoice such as dollar amount, dates, and so forth. I have
seven other tables with different categories of units that could be on an
invoice, tblburied_units, tblaerial_units, and so forth, all of which also
have primary key field invoice#. In each of the linked tables I have
added a
field that can be checked if the invoice contains that type of unit, so
the
user can open a form with the invoice# already populated. Before we
upsized
my billing form was based on a query pulling all of the fields from
tblBilling, and the one descriptive field from the seven linked tables.
When
the user was filling out the frmBilling the fields from the linked tables
were simply check boxes on the form. When, for example, an invoice had
buried units on it the user checked the box for buried units which came
from
tblburied_units, and was included in the query, then a command button was
hit
to open frmburied_units to enter in all of the units. By checking the
check
box a record was automatically created in tblburied_units with the
invoice#
field, the primary key, already populated with the invoice# entered on the
frmBilling. Does this make sense? Now, however, when I try to check the
check boxes for the linked tables I hear a beep with no message at the
bottom
left. I am still able to enter information into all the fields from
tblbilling, but not the other tables. From reading the posts I have made
sure all the tables had a primary key, I defined the relationships both in
the MDB and in the servers' Enterprize Manager, and added timestamp fields
to
all the table, but it still will not work. Is what I am trying to do
possible? Is there another way to accomplish the goal? Is there a reason
when a make the relationships in the MDB the cascade update and delete
check
boxes are greyed out? I thought maybe i was trying to include too many
tables in the query, so I tried it with only two tables and it still did
not
work. Here is the query with only two tables for simplicity;

SELECT tblbilling.*, tblaerial_units.aerial_units
FROM tblbilling LEFT JOIN tblaerial_units ON tblbilling.[Invoice#] =
tblaerial_units.[invoice#];

I am very frustrated and would greatly appreciate any advice.