Values related to report values not in source query (1 Viewer)

[Vivirtruvian]

Hi all,

Hopefully I can spell out my issue coherently enough for people to follow!

I have a report that is based on a Union Query that is generating an invoice for transport jobs. There are two numbers that I need to quote on the invoice that relate to each individual job (Job no. & Docket no.) that are causing me some issues.

Now, for some context: the database is for a transport/warehouse company that tracks everything on a pallet basis. Every pallet has a 'Job no' that we collected the pallet(s) on, and a 'Delivery no' that is generated by the supplier. On occasion, we may have two 'Delivery' numbers come in on the same truck or job.

I have a union query that gathers up all of the job information by a date range, and calculates the charges applicable for each job through a range of queries (certain charges apply at different thresholds of pallets loaded on a truck). This has to be Group(ed) By 'Job no' to ensure that the job itself is properly calculated and as such, 'Delivery no' is not included in this query - if it were, it starts to split the jobs due to the Group By function of the query further breaking down the fields.

This leads me to the issue with the report: I have the report set up to quote each job number and the charges for that job. What I need to include is a sub-line below each job that will reference each customer's 'Delivery no' for their reconciliation purposes. For example:

Job no 1                                 $0.00
        Delivery no a
        Delivery no b

Job no 2                                 $0.00
        Delivery no c

Job no 3                                 $0.00
        Delivery no d  

And so on.

Unfortunately all attempts to put the Delivery numbers in have resulted in:
a. nothing
b. #Error
c. a grouped list of the Delivery numbers, but all of them ie. not specific to each Job no.

Hope that is making sense. Any help is always appreciated.

 

[jdraw]

I recommend you post a copy of the database (zip format) with just a few records to show the issue. Remove anything private/confidential first.
You could have a few "mocked-up" records with names like Porky Pig, Centerville, UpState.... We are not interested in the details of your data, but need some representative values to understand the logic
surrounding the issue.
Good luck

 

[Vivirtruvian]

Hi jdraw,

I have attached as requested. In terms of how it all functions:

(NOTE: tblProteinPrime is the master database; 'Job No' is the field 'W/V Dkt In and 'Delivery No' is 'Del Dkt In')

1. I run the form 'frmInvoicingTransportInMg' and enter the date range for which I want to run the report (for this example please enter the first of July 2017 and the next text box will automatically add 7 days)

2. Enter whatever you like into the Invoice Number text box; it is simply attaching our invoice number to all of the jobs.

3. Clicking generate invoice runs an update query (Query6) to attach invoice details to the main database and opens the report 'rptInward - Transport Charges - MG Koroit'

Additionally, the query 'qryInward - Transport Charges - MG' is setup due to the fact that our billing has to follow parameters for the spaces taken up on a truck:

- 3 or less large pallets are charged at a single pallet rate
- 6 or less small pallets are charged at a single pallet rate
- Anything above this is charged at a full truck rate

As such, the query is based on 'W/V Dkt In' as it encapsulates a full picture of what was sent on a truck.

(Apologies for my less than optimal naming of items too!)

 

[jdraw]

I don't know the details of your set up but I feel there are many more "entities" involved than your tables (2) indicate.

As for Pallets--- I entered 5--- there is nothing to differentiate small and large that could be an issue in logic.
Having field names with embedded "/" character may cause you syntax issues -you'll have to use them with square brackets"[ ]".

I am attaching a jpg of the report produced.

 

[Vivirtruvian]

Sorry, I forgot to mention that you can just skip past the 'Pallets' part (leave blank). Not sure why that comes up but it may be because in the query 'Pallets' is an expression (Count) of another field.

The pallet size is already stored in the ProteinPrime table ('P Size') when the pallets arrive. The process flow is to enter pallet data and generate the invoice later down the track - so the query is already calculating the load without having to input any data.

The report you generated is correct and is what I would get if I run the report. Where I have put "Placeholder for delivery docket no" is where I need the individual delivery dockets listed.

I had another query in the Dummy database (Example I think I called it). If you run it you can see where job no 'MULTI 1' has two delivery docket numbers next to it. I want the report to reflect that but as you can see, if I were to add delivery docket number to the source query of the report, I would get a second record for MULTI 1 and that would double-charge for that job.

 

[jdraw]

My gut feeling is that your structure does not truly reflect your business set up -as I said in my earlier post. I don't know your business details, but it seems there are many entities "hidden/combined???" in your table(s). (non normalized structure)
Having embedded spaces in table field names will lead to syntax errors--sooner or later.

You might get more focused responses/assistance if you could describe your business and processes in plain English. A paragraph or 2 to put your table, queries, form and report and into context.

Good luck.