Combining data into one cell only IF data exists

[Robbks]

Ok, this is a little long winded but I'll do my best to keep it brief

I currently have an access database for tracking company assets and performing maintenance tasks on them.
We also have a number of Stores Stock Items that are controlled under a separate finance package.

Now,
We currently have forms that create individually numbered "Job Cards" in the Job Cards Table
we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'

I have then use a query to export the data i need (Job number, dates, location and Store Stock Items)
But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)



What i want is to use a second table (linked via the "Job Card#" key)
It will be called "Stock Items" which has a separate field for each of our stock items.

the user then just opens second form while creating the job that immediately creates a new record
The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view
Job# __ ItemX __ ItemY
2417_____3_______7


Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel)

What I want to see is a field containing
3x ItemX, 7x ItemY, etc

if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)
I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.

this data is used twofold,
on export to Excel I can split the data into new cells
and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.
So it needs to be done upon saving the record, record by record.

hope this makes some sense

[June7]

Do you want to concatenate data of related records? Review http://allenbrowne.com/func-concat.html

[Robbks]

Basically yes,

Is it possible to call upon the field name itself and include it in the data?

i.e my table looks like this (but with 30 items) and the end result I would like to see in the COLLATED DATA field (probably have to be a "memo" field for the possible length of the data)
COLLATED DATA does not have to be stored in the table, just calculated on the form that is printed and when the query that i use to export data is run.

JOB#	ITEM-X	ITEM-Y	ITEM-Z	COLLATED DATA
12145	3		2	3x ITEM-X, 2x ITEM-Z
12160		17	4	17x ITEM-Y, 4x ITEM-Z

[June7]

I reread your post. I think doing what you want will require code but the ConcatRelated function will not work with that data structure.

What you really should do is normalize your data. A column for each stock item is bad design. You have '30 or more' items and not every Job will have all items. Need a related child table JobDetails:

JOBNum (foreign key)
ItemID (X, Y, Z, etc)
Quantity


Advise no spaces or special characters/punctuation (underscore is exception) in names.

[Robbks]

I can't rely on the users to enter the correct or consistent "item ID" talking about workers here that don't have the time or care factor for data entry.
so i need a column for every item, unless there is another way of doing it?

Doing it this way also allows me to run reports on how many of each item has been booked out in a certain period to make sure that the upload template to my finance package has worked correctly

[June7]

30+ fields is too many to build a single expression in query with conditional IIf's. Need VBA code that opens a recordset filtered to the current record, loops through the fields and concatenates into a single string. This code would be a function called by a query. Something like:

 Function ConcatFields(strJobNum As String) As String
 Dim rs As ADODB.Recordset
 Dim strData As String, i As Integer
 Set rs = New ADODB.Recordset
 rs.Open "SELECT * FROM tablename WHERE [Job#]='" & strJobNum & "'", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
 For i = 0 To rs.Fields.Count - 1
     If Not IsNull(rs.Fields(i)) Then strData = strData & rs.Fields(i) & "x " & rs.Fields(i).Name & ", "
 Next
 ConcatFields = Left(strData, Len(strData) - 2)
 End Function

Call the function from query like:

SELECT *, ConcatFields([Job#]) As AllData FROM tablename;

There are ways to do the item count with a normalized data structure. GROUP BY (Totals) and CROSSTAB queries are two.