I have created a database to track applicants eligibility process. Just recently, I was approached to add a new field to find out how applicants know about our program (through Friend, Family, School, other etc.)
So I created a lookup table and added a new field 'AppSource' to an existing table (tblHouseHold), and created relationships (please see attached screenshot).
1. On my form I do not see the dropdowns.
2. How should the query look like so I can get a total # for each AppSources. For example 80 applicants learn about our program throug Friend, 50 applicants learn about our program thrugh School etc.
In tblAppSource you created 7 fields where there should only be 1 (ex.AppSourceType). Your 7 field names should be row entries in the field called AppSourceType.
In tblHousehold, AppSource is correctly a number field. The type of control should be combo, source type should be query, 2 columns (AppSourceId and AppSourceType) with field widths of 0;3 (or other).
the query for counting (using QBE) will be something like:
AppSource (add this field twice if you wish to see the names and the count)
- select groupby
- select Count for *one* of the the AppSource fields
Thanks wazz for your reply.
Following your suggestions, I redesign my tables (pls. see attached). My questions are as follows:
1. On my form, in design view, I tried to drag and drop the AppSource field, but don't get the dropdown options.
2. What type of query do I need to create to get the total for each option?
It's not a good idea at all to use the lookup function within the table design, it will create nothing but problems for you. Eliminate your lookup in the table design. Instead use a combobox in your form and set the rowsource property to your lookup table. Set limit to list to "yes" if you want and you're in business.
You would want to use a report to get the totals. I would assign a field valueing "1" to each record in the query or SQL underlying the report, group by each type of referral, then sum the "1" field for each group. Since each record has "1" the sum would give you the total number of records in each referral group.
i'll try to change what i said to follow what meremyb said more closely:
first, in in tblAppSource change AppSourceType's data type to text and don't use a value list, enter your values (Friend, Family, etc,) as rows in the table. in the field properties (bottom of tbl design), in the Lookup Tab, change (if necessary) the display control to text box (default).
in tblHousehold, i would firstly change the field name 'AppSource' to match the original: 'AppsourceID'. In this table (tblhousehold) the data type for this field is number because it will be looking up the AppSourceID field - which is a number - from tblAppsource. also leave this field type as the default textbox for now.
create a query (qryAppSourceTypes) that includes the ID field and the AppSourceType field (Ascending).
on your *form*, drag the AppSourceID field (from tblHousehold) to the form. then select Format/ChangeTo/Combo Box (or select the combobox from the toolbar, then drag the field to the form). set the row source for this combo box to your new query.
to get the totals, in a new query (qryAppSourceTotals) use tblHousehold, add the AppSourceID field to the grid *Twice*. select the Totals button from the toolbar (Greek letter). leave the first field as GroupBy, and change the second field to Count. (when you use this you'll want to hide the first column and view the second column.)
I still do not see the dropdown in my form. It may be my relationships are not correct?
I created the relationships by dragging AppSourceID from (tblAppSource) to AppSourceID in (tblHousehold) and I have AppSourceType as a PK, and wasn't sure what type of relationship I should get (one-to-many .) and, as suggested, when I change the display control to text (default), all the values I entered (Friend, Family...) are gone.