Hello all,
I have a table "tblContact s" that holds my Contact Info. Because there can be multiple Contact Types for a Contact, I have a second table "tlnkContactTyp es" in which there is a record for every Contact / Type combination.
tblContacts: ContactID, NmFirst, NmLast. etc...
tlnkContactType s: ContactID, ContactType (which is selected from value list)
For a summary query I need to Show all of the Contact Types for a contact together. I have found a public function called "Conc" (see below) that does a nice job of concatenating all Contact Types for a ContactID from tlnkContactType s into one field.
So tlnkContactType s
ContactID ContactType
1 Resident
1 Super
2 Resident
2 Teacher
3 Teacher
is transformed to qryContactTypes Cnct
ContactID ContactTypes
1 Resident, Super
2 Resident, Teacher
3 Teacher
The problem that I have is that not all Contacts are represented in tlnkContactType s, because a Contact may not have a Type. When I create my summary query based on tblContacts and include qryContactTypes Cnct, I get an error message for every record in tblContacts that is not represented in qryContactTypes Cnct.
I cannot figure out how to make it enter a "blank" value for Contacts that are not in qryContactTypes Cnct. Any advice would be appreciated!
Banderson
For my project, the variables in the function below are define as follows:
Fieldx = ContactType",
Identity = "ContactID" ,
Value = [ContactID],
Source = "tlnkContactTyp es"
I use the public function below in my qryContactTypes Cnct SQL like this:
SELECT tlnkContactType s.ContactID, Conc("ContactTy pe","ContactID" ,[ContactID],"tlnkContactTy pes") AS ContactTypes
FROM tlnkContactType s
GROUP BY tlnkContactType s.ContactID; -
-
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
-
Dim cnn As ADODB.Connection
-
Dim Rs As ADODB.Recordset
-
Dim SQL As String
-
Dim vFld As Variant
-
-
Set cnn = CurrentProject.Connection
-
Set Rs = New ADODB.Recordset
-
vFld = Null
-
-
SQL = "SELECT [" & Fieldx & "] as Fld" & _
-
" FROM [" & Source & "]" & _
-
" WHERE [" & Identity & "]=" & Value
-
-
' open recordset.
-
Rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
-
-
-
' concatenate the field.
-
Do While Not Rs.EOF
-
If Not IsNull(Rs!Fld) Then
-
vFld = vFld & ", " & Rs!Fld
-
End If
-
Rs.MoveNext
-
Loop
-
' remove leading comma and space.
-
vFld = Mid(vFld, 3)
-
-
Set cnn = Nothing
-
Set Rs = Nothing
-
-
' return concatenated string.
-
Conc = vFld
-
End Function
-
-
3 2543 ADezii 8,834
Recognized Expert Expert
I took a different approach, simply because I am at work and needed the quickest possible solution, for me. - Sample Data from tblContacts:
- ContactID ContactType
-
1 Resident
-
1 Super
-
2 Resident
-
2 Teacher
-
3 Teacher
-
4
-
5
-
6 Resident
-
6 Teacher
-
6 Super
-
6 Client
-
6 Doctor
-
6 Lawyer
-
6 Indian Chief
- SQL Statement for Query:
- SELECT tblContacts.ContactID, Conc([ContactID]) AS Types, fTotalOfTypes([ContactID]) AS [Total Types]
-
FROM tblContacts
-
GROUP BY tblContacts.ContactID
-
ORDER BY tblContacts.ContactID;
- Function Definitions:
-
Public Function Conc(lngContactID As Long) As String
-
Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strSQL As String
-
Dim strBuild As String
-
-
strSQL = "SELECT * FROM tblContacts WHERE [ContactID] = " & lngContactID
-
-
Set MyDB = CurrentDb
-
Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
-
-
With rst
-
Do While Not .EOF
-
strBuild = strBuild & ![ContactType] & ","
-
.MoveNext
-
Loop
-
End With
-
-
Conc = Left$(strBuild, Len(strBuild) - 1)
-
-
rst.Close
-
Set rst = Nothing
-
End Function
- Public Function fTotalOfTypes(lngContactID As Long) As Long
-
fTotalOfTypes = DCount("*", "tblContacts", "[ContactID] = " & lngContactID & _
-
" And [ContactType] Is Not Null")
-
End Function
- Output after Query Execution:
- ContactID Types Total Types
-
1 Resident,Super 2
-
2 Resident,Teacher 2
-
3 Teacher 1
-
4 0
-
5 0
-
6 Resident,Teacher,Super,Client,Doctor,Lawyer,Indian Chief 7
@ADezii
ADezii,
This works beautifully! and solves my problem
Thank you so much for the help.
Banderson
ADezii 8,834
Recognized Expert Expert Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Paul Morrow |
last post by:
I have seen the technique where a number of rows in a database are
displayed in an html table so that each column of each row is
editable. They use a single form surrounding the table, where each
field in any given column has the same control name. So for example,
in the last_name column, every row in the table would contain an input
field (of type "text") with the name "last_name".
Is this safe to do? I know that multiple radio...
|
by: Lyn |
last post by:
Hi,
I am working on a genealogy project in which I have two tables:
Person -- one record for each person in the family. Each record has a
unique Autonum field (IDPerson).
Partnerships -- one record for each marriage. Each record has a unique
Autonum field (ID), and also contains two fields IDPartnerMale and
IDPartnerFemale. Both of the latter fields match records in the Person
table via IDPerson.
|
by: ImraneA |
last post by:
Hi there
Have a database, where front-end interface allows user to select a ms
access database. From there, standard tables are linked. Routine,
that creates a spreadsheet, for each table a separate sheet within
spreadsheet is created. Particular fields are selected.
User requires fields to be auto-fitted. Problem, is that some tables
have more than 1200> rows. Code generates error message. Is there a
way of getting around this...
|
by: Dave Hagerich |
last post by:
I'm using a DataGrid with a DataSet and I'm trying to filter the data being
displayed, using the following code as a test:
DataView theView = new DataView(theDataSet.Tables);
theView.RowFilter = "'Record ID' = '0'";
theView.RowStateFilter = DataViewRowState.ModifiedCurrent;
Debug.WriteLine(string.Format("RowFilter = {0}", theView.RowFilter));
RecordDataGrid.DataSource = theView;
RecordDataGrid.DataBind();
|
by: Prabhat |
last post by:
How do I lock a particular record that one user has opened for editing?
If I use the pessimistic type, can other users view the record (but not
edit it) and return a message telling that another person is editing the
record, or does this type lock the record such that it is unavailable
until the editor releases it?
An explanation of pessimistic and optimistic lock types would be really
useful, as would some example code.
| |
by: Elena |
last post by:
Hi,
I am filling in a combobox. I would like to concatenate two fields into the
data combo box and display "last name, first name"
I tried to displaymember = "employee_last_name" & ", " &
"employee_last_name", but it did not like that.
I can fill the combo box with either the first or the last, but I cannot
manage to concatenate it.
|
by: Donald Grove |
last post by:
I want to populate an array with values from an ado recordset
(multiple rows)
I use the absolute position of the cursor in the recordset to define
the row of my array to be populated. I have a workaround for lack of
a way to define the ordinal position of a field (incrementing a
counter variable), but it feels so primitive:
dim Fld as Field
dim rst1 as new adodb.recordset
|
by: The Frog |
last post by:
Hi all,
Maybe I am just missing something simple here, but I seem to have an
issue with a callback function in A97 that is used to fill a Listbox
with values. The first time the callback function is used (when the
form opens) all runs well and everyone is happy. Then comes the
problem - values are added to the recordset (ADO) that the callback
function uses to populate the listbox.
After the new values are added to the recordset, the...
|
by: bonneylake |
last post by:
Hey Everyone,
Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also the parts section an i seem to be having trouble.
When i try to insert into the parts section i get the error Invalid character value for cast specification. But not sure what i am doing wrong. Here is what i am using to insert. All the sections...
|
by: azegurb |
last post by:
Hello, I have one question again
i created one table again and in this table i added some another options
for ex at the previous table there were only one problem sum of the dynamically added rows
in this beside sum i need to sum of the tax
for ex: in column1 i added 300 and into 2-nd column i Chose 10%
i would like when i chose from drop down box 10% Let in the 2-nd column appear 10% of the value which i addee first column
for ex...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |