Creating a column per value in a field with a query

[cj-1289]

Hi,

I was hoping for a query-based solution to the following problem. I already have a complicated mixed VBA/Report-based solution but it's becoming too cumbersome to manipulate.

I have a table [Invoices], where each record pertains to one invoice, e.g.:

Company / JobID / InvoiceNo / Final_Total
A ltd / JOB100 / INV-234 / £1000
A ltd / JOB100 / INV-567 / £4000
A ltd / JOB150 / INV-890 / £500
B ltd / JOB200 / INV-098 / £2000

I have a query [Summary] which groups all the invoices for each company and jobID (there are often multiple invoices per job), and that works fine using:


SELECT [Invoices].Company, [Invoices].jobID, Sum(IIf([status]='invoice',[final_total])) AS SumOfFinal_Total,
FROM [Invoices]
GROUP BY [Invoices].Company, [Invoices].job_no;

but I also need it to list all the invoice numbers for each company/jobID combination in each record and that's where I'm having trouble. Desired result would be:

Company / JobID / INVOICE1 / INVOICE2 / SumOfFinal_Total
A ltd / JOB100 / INV-234 / INV-567 / £5000
A ltd / JOB150 / INV-890 / / £500
B ltd / JOB200 / INV-098 / / £2000

Can anybody help? Thanks in advance.

 

[jdraw]

Can you show a jpg of your tables and relationships?

 

[cj-1289]

I can't, I'm afraid, no. The real data is confidential, I have no facility for uploading images here, and the real [Invoicing] table contains a lot of fields which are irrelevant for this purpose. There are no relationships involved. I only gave the relevant information and test data which went to the point. The important thing is that in the summary, I need to group rows together by company/jobID and sum up the totals of the rows, while putting each invoice number into a field in that record in the query. So the total for every invoice for that company and job are summed in the row, and every invoice number for that company and job has its own column in that row. I anticipate only five invoices per company maximum, so if the fields have to be fixed, I would want to produce [Summary] as:

COMPANY/ JOBID / INVOICENO1 / INVOICENO2 / INVOICENO3 / INVOICENO4 / INVOICENO5 / SUMOFFINALTOTAL

sourced from [Invoices]:

COMPANY / JOBID / INVOICENO / FINALTOTAL

Sorry I couldn't give you the jpg. Cheers.

 

[jdraw]

I was asking for a table/relationship diagram not the data (confidential data).

This may be key to your issue

There are no relationships involved.

I see
Company
Job
Invoice at least; where
a Company can have 1 or more Jobs
a Job will have 0,1 or many Invoices
A Job relates to 1 Company
An invoice relates to 1 Job

 

[plog]

There's a couple ways to accomplish this. The easiest way is to talk you out of wanting to display your data with invoices listed horizontally. Why won't vertically work? You could create a report based on the query you have now, include the JobID in a JobID header, list all the invoices that fall under each JobID and then total and count them in a JobID footer. It would look like this:

A ltd JOB100
----INV-234, 500
----INV-567, 5000
----A ltd Job100, 2 invoices, 5500
A ltd JOB150
----INV-890, 500
----A ltd JOB150, 1 invoice, 500
B ltd JOB200
----INV-098, 2000
----B ltd JOB200, 1 invoice, 2000

Every other way involves a subquery, VBA, a complex crosstab subquery, and then a final query. You would first need to numerate your invoices--determining which is Invoice 1 of Job 150, Invoice 2 of Job 150, Invoice 1 of Job 200, etc. I'm sure there's a hack that can do it in a query, but off the top of my head, to do that you would create a query of all your invoices use an ORDER BY clause to arrange them properly, create a module to read every record of that query in and spit each record out with the proper invoice number into a new table. You'd then build a crosstab query off that table you made getting the data pretty close to what you wanted: COMPANY/ JOBID / INVOICENO1 / INVOICENO2 / INVOICENO3 / INVOICENO4 / INVOICENO5. Lastly, you would create another query that used that crosstab query and then calculate the SUMOFFINALTOTAL field at the end.

Again, to get the data as you have stated its going to be a pain. If you can accept your data in another manner it can be a whole lot easier and quicker.