By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 1,783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

New user query.

P: n/a
Hi
Is it possible within Access to have fields in a table that react to
fields within another table? I have a table that contains all valid
order numbers. In another table there could be any number of records
containing the order numbers from the first table. I would like two
fields in the first table, the first would contain either a Y or N to
signal that a record does or does not exist for this order number in
the second table. Then another field in the first table representing
how many times a record for this order number appears in the second
table. Hope this makes sense.
Thanks for any advice.
Johno
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Johno" wrote
Is it possible within Access to have
fields in a table that react to
fields within another table?


No, Access does not allow calculated Fields in Tables -- it would be a
violation of relational database design principles and would complicate
handling the database. You can include a Foreign Key in the "child" table,
retreive the data with a Query, and the absence of child Table data will
serve the same purpose. If you wish, in the Query, you could generate the
Yes/No value, I suppose, but it's as easy to test for Null as to test for
Yes/No.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #2

P: n/a
Br
Johno wrote:
Hi
Is it possible within Access to have fields in a table that react to
fields within another table? I have a table that contains all valid
order numbers. In another table there could be any number of records
containing the order numbers from the first table.
What you are explaining is a one-to-many relationship between the
tables.
I would like two
fields in the first table, the first would contain either a Y or N to
signal that a record does or does not exist for this order number in
the second table.
Don't enter data into a table that can be determined. You can determine
if there are any records in the second table by using a query.
Then another field in the first table representing
how many times a record for this order number appears in the second
table.
Again, you can determine this with a query.
Hope this makes sense.
Thanks for any advice.
Johno


Sounds like you probably need to learn some database theory basics :)

Br@dley

Nov 13 '05 #3

P: n/a
Johno,
Not a table. A query or view can be built which will do what you want. In
some cases Access will allow you to edit the results of a query so it
functions similar to a table with the added ability to include calculated
columns in the query.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Johno" <Jo****************@lycos.co.uk> wrote in message
news:10**************************@posting.google.c om...
Hi
Is it possible within Access to have fields in a table that react to
fields within another table? I have a table that contains all valid
order numbers. In another table there could be any number of records
containing the order numbers from the first table. I would like two
fields in the first table, the first would contain either a Y or N to
signal that a record does or does not exist for this order number in
the second table. Then another field in the first table representing
how many times a record for this order number appears in the second
table. Hope this makes sense.
Thanks for any advice.
Johno

Nov 13 '05 #4

P: n/a
Johno,
What about: "SELECT ORDER_NO,
IIF(NZ(COUNT_ORDERS_VW.ORDER_COUNT,0)>0,"Y","N") AS ORDER_EXISTS,
NZ(COUNT_ORDERS_VW.ORDER_COUNT,0) AS ORDER_COUNT FROM ORDER_TBL LEFT OUTER
JOIN (SELECT ORDER_NO, COUNT(*) AS ORDER_COUNT FROM DETAIL_TBL GROUP BY
ORDER_NO) AS COUNT_ORDERS_VW ON ORDER_TBL.ORDER_NO =
COUNT_ORDERS_VW.ORDER_NO;" If I have understood you right this should
generate the result you want. Table & column names are of course,
fictitious because I don't know enough detail to write a SELECT statement
that would work without changes.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Johno" <Jo****************@lycos.co.uk> wrote in message
news:10**************************@posting.google.c om...
Hi
Is it possible within Access to have fields in a table that react to
fields within another table? I have a table that contains all valid
order numbers. In another table there could be any number of records
containing the order numbers from the first table. I would like two
fields in the first table, the first would contain either a Y or N to
signal that a record does or does not exist for this order number in
the second table. Then another field in the first table representing
how many times a record for this order number appears in the second
table. Hope this makes sense.
Thanks for any advice.
Johno

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.