dependent lookup / query

[elliesmith]

Hello

I am relatively new to creating databases. I can create the table,
understand (??) PK and FK in theory. Here's my issue:

I have a main table: [all_document_info] with fields
[PK]
[reference_number]
[last_ref_used]
[from_short]
[from_long]

I have a second table [dept_refs_name] that contains the data needed in the
above fields:
[PKrefnumber]
[dept_acronym] relates to the from_short field
[full_dept_name1] relates to the from_long field
[ref_number] that relates to the reference_number field above.

This is where my limited knowledge runs out. from_short is a drop down and I
want the corresponding LAST data for that field input into from_long and
last_ref_used. So the table will be sorted by PK. It is a one to many
relationship (I think) or "if [all_document_info.from_short]=[dept_refs_name.
dept_acronym] then [all_document_info.from_long]=[dept_refs_name.
full_dept_name1] and [all_document_info.last_ref_used]=[dept_refs_name.
referene_number]

The form will (eventually) allow the user to select the (from) department
acronym that sent the correspondence and automatically see the long
department name and the number of the last memo received from that department.


The user will then be able to fill in the new information for the new piece
of correspondence. Each department will have many ref_numbers, but only one
acronym and long name.

The last_ref_used and from_long fields are dependent one what the user
chooses from_short chosen from the drop down.


Can someone explain what I do next? I am truly stuck. Thanks Ellie

 

[Philip Herlihy]

Hello

I am relatively new to creating databases. I can create the table,
understand (??) PK and FK in theory. Here's my issue:

I have a main table: [all_document_info] with fields
[PK]
[reference_number]
[last_ref_used]
[from_short]
[from_long]

I have a second table [dept_refs_name] that contains the data needed in the
above fields:
[PKrefnumber]
[dept_acronym] relates to the from_short field
[full_dept_name1] relates to the from_long field
[ref_number] that relates to the reference_number field above.

This is where my limited knowledge runs out. from_short is a drop down and I
want the corresponding LAST data for that field input into from_long and
last_ref_used. So the table will be sorted by PK. It is a one to many
relationship (I think) or "if [all_document_info.from_short]=[dept_refs_name.
dept_acronym] then [all_document_info.from_long]=[dept_refs_name.
full_dept_name1] and [all_document_info.last_ref_used]=[dept_refs_name.
referene_number]

The form will (eventually) allow the user to select the (from) department
acronym that sent the correspondence and automatically see the long
department name and the number of the last memo received from that department.


The user will then be able to fill in the new information for the new piece
of correspondence. Each department will have many ref_numbers, but only one
acronym and long name.

The last_ref_used and from_long fields are dependent one what the user
chooses from_short chosen from the drop down.


Can someone explain what I do next? I am truly stuck. Thanks Ellie

So am I - truly stuck. I think if you stand back and try to imagine
reading this not knowing anything (!) about what you're trying to model,
you'll see that it would take more than my poor brains to figure out
what's going on.

As far as I can tell, this is something about recording document
exchanges between departments. I guess you'd need a table of Documents
and a table of Departments. Do you just want to be able to list
documents in date order from or to a specific department? Or do you
need to record "threads"? You're asking us to figure out what you want
to do from how you are attempting (so far unsuccessfully) to implement it...

I suspect you don't have your tables right - the most usual and
fundamental problem. One aphorism is that every fact should be recorded
in exactly one place - no more, no less. See:
http://tinyurl.com/ms-table-design-tutorial
and:
http://office.microsoft.com/en-us/training/CR101582831033.aspx
... for guidance on this.

1) Understand what you're trying to model and be able to explain it in
non-technical terms.
2) Get your tables right - vital!
3) Tell Access about any relationships via the Relationship window
4) Create queries to link related information from your tables
5) Create forms/reports based on those queries
6) Look out for wizards and use them first.

Do all this and it can be astonishing how much Access can do the work
for you.

I hope that does help!

Phil, London