Can I have one Form with two Tables?

[Tired]

Hi there again,

I asked a question a while back about counting hyperlinks. For example, link
a document via hyperlink and name it "1" and then add the "1"'s up. Excel can
do it but not Access.

I was told at that time to create two tables, one containing the hyperlink
and another containing the 1's.

My follow-up question is: If I have two tables and one form - How do I make
both tables function? I tried a couple of different methods but the form will
display the fields for one table and for the fields in the other table it
displays "#Name".

Any help is appreciated.

Thank-you!!!

 

[pietlinden]

Hi there again,

I asked a question a while back about counting hyperlinks. For example, link
a document via hyperlink and name it "1" and then add the "1"'s up. Excelcan
do it but not Access.

I was told at that time to create two tables, one containing the hyperlink
and another containing the 1's.

My follow-up question is: If I have two tables and one form - How do I make
both tables function? I tried a couple of different methods but the form will
display the fields for one table and for the fields in the other table it
displays "#Name".

Any help is appreciated.

Thank-you!!!

you could just as easily use DCount to get the number of child
records... or you could use a totals query. It depends what you want
to do with the count...

 

[Jeff Boyce]

You've asked a "how" question.

In Access, it all starts with the data ... please describe a bit more about
the "what".

For example, how does your table "contain... the 1's"? What are the fields
in your tables, how do you relate them?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tired]

Hello,

I am a novice so please pardon lack of proper vocabulary.

I have a primary table and another table.

My primary table contains the people. The other table contains modules that
the people need to complete. The two tables are related through a primary
key.

For exmple:
Primary Table contains Bill Evans
The other table contains a list of modules that must be completed by Bill
Evans.

The Modules are completed via paper and then scanned into digital format.
Each module is represented by a field: A-1 is the first module and it goes
out until A-26. There are approximately 148 modules.

The idea was to use the other table to house the numbers that are really
hyperlinks to the actual document.

So the field would show a 1 which is a hyperlink the document otherwise
there would be nothing in there ("Null"). The concept would be that Access
then counts the "1" hyperlinks as the number of completed modules.

If I want to look at the actual paper, I follow the hyperlink by selecting
the 1.

When I last asked this question, one of your colleagues suggested using two
separate tables, one to contain the hyperlinks and the other to contain the
completed modules.

The form that I use for the modules is a sub form of the main form which
contains the people. The problem is that I cannot seem to get both of the
tables into one form. I can get one table to show up containing the
hyperlinks but the field for the other table just shows "#Name".

Thanks for your time!
Let me know if you need more information.

 

[pietlinden]

I think you need 3 tables...

Person---(1,M)---<MustComplete>---(M,1)---Module

Person(PersonID (PK), PersonName...)
Module(ModuleID (PK), ModuleName,...)
MustComplete(PersonID, ModuleID, CompletionDate...)

"MustComplete" is the intersection between the two tables. It's the
"list of who must complete what" and when it was completed. If you go
that way, you can tell who completed or did not complete which
module. If some modules are mandatory, you can add a Yes/No field to
Module and then create a deliberate partial or full cartesian
product... but first things first, start with a correct design.

 

[Tired]

Very well,

Let's say I have three tables. Going back to the original question, can I
have a sub form to a main form that has more than one table? If so, how would
that work?

Thank-you,
Mark