First of all, I'm kind of a newbie to all of this, but not entirely, bare this in mind please ;-) Also I'm from the
Netherlands and my english might be a bit rusty, so bear with me if you please.... :-)
I'm having a problem for which I cannot seem to find a solution. Below I've outlined the situation:
I have a couple of tables up to now:
CustomerTable has got a primary key: Clientnumber
CaseDateTimeTable has primary key: Casenumber
CaseTechInfoTable has no primary keys.
CustomerTable only includes (apart from all adress info etc) the clientnumber field which is of importance in this case i
CaseDateTimeTable includes both Casenumber and clientnumber.
CaseTechInfoTable also includes both Casenumber and clientnumber. Only both other tables have Primary keys, and this one
I've created a relation between CustomerTable - Clientnumber and CaseDateTimeTable - Clientnumber.
Another relation I've made is between CaseDateTimeTable - casenumber and CaseTechInfo - casenumber.
Now, I have made a form in which the name and adress data is fed into the database, in normal form view. Below these
textboxes etc. I've created a SubForm in datasheet view in which the existing cases from the at that time selected/displayed
customer should be displayed. At the moment only fields from the CaseDateTimeTable can be displayed, but I would also like to
display some fields from, for example, CaseTechInfoTable in that same subform.
When I create a (sub)form via the wizard and for example select:
CaseDateTimeTable - clientnumber
CaseDateTimeTable - Casenumber
CaseTechInfoTable - Problem description
and display these fields in the subform as a datasheet, then the separate cases from each customer are not displayed. I have
5 clients in my test dbase, just about all of them have 2 or 3 cases associated with them. In the example above only the data
from two clients is displayed and then only from 1 case each, not the actual amount of cases. So for example:
clientnumber 4 has casenumber 5 and casenumber 6. But only casenumber 5 is displayed. The same happens for clientnumber 2, he
has casenumbers 9 and 3 but only casenumber 9 is displayed.
The strange thing is though, that when I create the same (sub)form in datasheet view, but only use fields from the
CaseDateTimeTable all cases per client are nicely displayed in the subform.
Why? And how do I solve this problem? I need to solve it so that I can add data from other tables into the subform later on
so a nice overview of certain important data can be given per customer per case, even before the case form(s) is/are opened.
I've tried making a query and base a form on that, but for some reason I can't get the query to display all my customers, let
alone all cases associated per customer. In the past I have made another (smaller and simpler) database for which I also made
a lot of queries. But now? Pff... I'm lost at the moment.