Combining duplicates into a single multi value field

[JJconfused]

I think I must be missing something, or can't quite wrap my head around the
basic training.

I have a table for employee names, which are unique (Sally, John, Mike). I
have another table for invoices, and there is a field for employee names
(there may be more than one), as well as a field for invoice numbers, which
are unique.

The goal is a table or query that lists each employee, once, and has a
multi-value field that lists every invoice number that has their name
associated with it.

 

[Gina Whipp]

JJconfused,

I think you missed a table in your initial set-up...

tblInvoiceEmployee
ieInvoiceID (FK - relate to tblInvoice)
ieEmployeeID (FK - relate to tblEmployee)

Because it sounds like you have a field in tblInvoice that includes more
then one Employee Name which makes your taks difficult. Is it possible for
you to create a table like the above then use...

http://allenbrowne.com/func-concat.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[JJconfused]

So, in words, you want me to create a third table, that lists each employee
as many times as he/she has invoices, and will end up listing each invoice
for as many employees as it has associated with it.

And then concatenate (thanks very much! Hadn't thought of that).

1.) Is there some reason a simple query wouldn't suffice? Do I need to
actually have a third table, as opposed to query?

2.) I can't seem to get the code to work as a module - triple checked the
field / table names several times, and as they are text, switched in the bit
with the quotes mentioned in the third argument. If I wanted the concatenated
data (as text or as a multi value field, either way) to be a field in a table
or a query, where would I put the code?

 

[Piet Linden]

So, in words, you want me to create a third table, that lists each employee
as many times as he/she has invoices, and will end up listing each invoice
for as many employees as it has associated with it.

And then concatenate (thanks very much! Hadn't thought of that).

1.) Is there some reason a simple query wouldn't suffice? Do I need to
actually have a third table, as opposed to query?

2.) I can't seem to get the code to work as a module -  triple checked the
field / table names several times, and as they are text, switched in the bit
with the quotes mentioned in the third argument. If I wanted the concatenated
data (as text or as a multi value field, either way) to be a field in a table
or a query, where would I put the code?









- Show quoted text -

JJ,

Maybe this will help (I hope!). Let me start in plain old English,
because this part is confusing for beginners...
Your basic "rules" are:
1. MANY Employees can be related to MANY Invoices.
2. MANY Invoices can be related to MANY Employees.

If both statements are true, then you have to have a join table,
because you have a many-to-many relationship. The problem is Access
can't work with many-to-many relationships, so you break the
relationships in half (create two one-to-many relationships for each
many-to-many). So.

Employee---(1,M)---EmployeeInvoice--------(M,1)--------Invoice

CREATE TABLE Employee (
EmployeeID INTEGER PRIMARY KEY,
FirstName TEXT(20),
....)

CREATE TABLE Invoice (
InvoiceNo INTEGER PRIMARY KEY,
InvoiceDate DATE,
....)

CREATE TABLE Employee_Invoice (
eiEmployeeID INTEGER,
eiInvoiceNo INTEGER,
PRIMARY KEY (eiEmployeeID, eiInvoiceNo),
FOREIGN KEY eiEmployeeID REFERENCES Employee(EmployeeID),
eiInvoiceNo REFERENCES Invoice(InvoiceNo));

Employee-Invoice stores the information about which invoices belong to
which employees (one record for each instance of "belongs to"). Then
once you have this setup, you can use Allen's function to concatenate
the Invoice Numbers, so you get
(Employee, ListOfInvoices...) where ListOfInvoices is a concatenated
string. (e.g. "Inv01, Inv03, Inv04")

Hope this helps.

 

[Gina Whipp]

Thank you Piet! Much clearer explanation!!!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

So, in words, you want me to create a third table, that lists each
employee
as many times as he/she has invoices, and will end up listing each invoice
for as many employees as it has associated with it.

And then concatenate (thanks very much! Hadn't thought of that).

1.) Is there some reason a simple query wouldn't suffice? Do I need to
actually have a third table, as opposed to query?

2.) I can't seem to get the code to work as a module - triple checked the
field / table names several times, and as they are text, switched in the
bit
with the quotes mentioned in the third argument. If I wanted the
concatenated
data (as text or as a multi value field, either way) to be a field in a
table
or a query, where would I put the code?









- Show quoted text -

JJ,

Maybe this will help (I hope!). Let me start in plain old English,
because this part is confusing for beginners...
Your basic "rules" are:
1. MANY Employees can be related to MANY Invoices.
2. MANY Invoices can be related to MANY Employees.

If both statements are true, then you have to have a join table,
because you have a many-to-many relationship. The problem is Access
can't work with many-to-many relationships, so you break the
relationships in half (create two one-to-many relationships for each
many-to-many). So.

Employee---(1,M)---EmployeeInvoice--------(M,1)--------Invoice

CREATE TABLE Employee (
EmployeeID INTEGER PRIMARY KEY,
FirstName TEXT(20),
....)

CREATE TABLE Invoice (
InvoiceNo INTEGER PRIMARY KEY,
InvoiceDate DATE,
....)

CREATE TABLE Employee_Invoice (
eiEmployeeID INTEGER,
eiInvoiceNo INTEGER,
PRIMARY KEY (eiEmployeeID, eiInvoiceNo),
FOREIGN KEY eiEmployeeID REFERENCES Employee(EmployeeID),
eiInvoiceNo REFERENCES Invoice(InvoiceNo));

Employee-Invoice stores the information about which invoices belong to
which employees (one record for each instance of "belongs to"). Then
once you have this setup, you can use Allen's function to concatenate
the Invoice Numbers, so you get
(Employee, ListOfInvoices...) where ListOfInvoices is a concatenated
string. (e.g. "Inv01, Inv03, Inv04")

Hope this helps.

 

[JJconfused]

So table Employee_Invoice is populated manually (not a query)?

 

[Gina Whipp]

That table would be populated via the form as a subform where you would
select all the associated Employees via a combo box linked to tblEmployee.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[JJconfused]

Alright, I think I understand. One more question. I can't seem to get that
code to be recognized. I CTRL-G, open a new module, copy it in, debugged it
(nothing happened), even saved the whole file so that I had to name the
module - is there anything else I need to do to define it? The call didn't
work, says the function is undefined.

 

[Gina Whipp]

You would use it in a query, ie...

SpecificationID: ConcatRelated("SELECT SpecificationID FROM qryItemsOnOrder
WHERE ffesVendorID =" & [ffesVendorID] & " And" & " ffesProjectID=" &
[ffesProjectID],Chr(13) & Chr(10))

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm