Beginner Question...

[ddoumani]

I have a database with a few tables, 2 of them are "requirements" and "req_catagory"

req_catagory has 3 fields, ID, longdesc, code

example:

1, Blood Bank, BB
2, Micro Biology, MICRO
3, Nursing, NUR

requirements table has a bunch of fields. once of the fields is category which is a lookup to req_category so that each requirement can be categorized. It also has another field called category2 which is a calculated field "[category] & "-" & [ID]" so that the entry would get a unique label such as BB-1 or MICRO-23 and be used on forms.

everything is working, however the calculated field is pulling in the ID of the category so the unique label is 1-1 or 2-23. I want the textual code of category so that the requirements can be referenced easily.



The lookup wizard was used and the field-type for category is numerical so this all makes sense, however I can't figure out how to get the textual reference which IS displayed in the table for category when viewing it.

[CJ_London]

calculated fields cannot be used in that way, your table is storing the ID so that is the only value it can reference in a calculation. calculated fields cannot reference fields in other tables and can only use a limited range of functions. Because of their limitations, many don't use them. The way round it is to use a query

Also, see these links about lookup fields in tables

https://bytes.com/topic/access/answe...-lookup-fields
http://access.mvps.org/access/lookupfields.htm

[CJ_London]

One thought - drop the ID fields from your req_catagory and make code the primary key - it has to be unique anyway, then your calculated field will work because it will be storing 'BB' whatever. Although personally I would avoid using lookups and calculated fields in tables - once you have finished development - who is going to look at the tables? they will be (or should be) going through forms or reports

[ddoumani]

Thanks for the reply.

i was able to do it in the query, however what I am trying to do it use it as a unique-id (for humans, not the database) on the input form (i.e. NUR-23) so that as the project goes on that requirement ID and all subsequent ID's are referenced.

In the form I could not find a way to display that concatenation.

The data is handled in 3 distinct forms.

1) collection (not needed in this form)
2) requirement initiation
) requirement completion

In forms 2 and 3 is where I am trying to display this value so the people can reference and talk about specific requirements in meetings, in giving out action items, etc...

Maybe I am going about it the wrong way. I have it working in the form but just as the ID+ID of the category and the requirement.

[CJ_London]

did you see my second post?

[orange]

ddoumani,

What is the underlying business issue that you are trying to solve with this database? Keep your response to simple English - no jargon. Give us the 30,000 ft overview of the "business" so we understand the context of where all this fits.

A couple of points:
-I would not use the lookup wizard as Ajax has already advised
-I would start with a description of the business, then build a model
create some test data to ensure the model matched the requirement
-No forms etc until the model has been vetted with test data
-With a validated model (blueprint for database) create your tables and relationships.

Good luck.