Report selecting detail records in Query

[ralphjramirez]

I am unable to figure out why a report that selects the data from a query is including the "Invoice Total" from the main table and the "Total" from the detail table. I am using 3 linked tables, one has the invoice fields, one has the detail fields and the other has vendor information. I am only selecting the "Invoice Total" and not the detail "total" but both fields are included in the report. Nowhere in my report layout is the field "Total" included. Can someone explain to me what the heck is going on? I have included a link to a screen capture of the report query so you can be understand what I am saying. My mind and fingers don't always agree when typing my problem.

Any help will be greatly appreciated.

Thanks............Ralph



Here is the link to the jpg screen capture: https://farm4.staticflickr.com/3752/...b74924ee_b.jpg

[June7]

I cannot view the image here (blocked). You can attach or embed images to post.

Really need to see report design. If you want to provide db for analysis, follow instructions at bottom of my post.

[ralphjramirez]

I cannot view the image here (blocked). You can attach or embed images to post.

Really need to see report design. If you want to provide db for analysis, follow instructions at bottom of my post.

Not sure why you can't see the posted link, I guess this forum blocks it. I will try to embed the image herel

I can give you a screen shot of the report in design form, will that do?

Thanks..........Ralph

[June7]

No, farm4 site blocked here by my network.

The Total field is available to the form because of the wildcard: Elegance Invoices 2.*

There must be a textbox on report that references the field.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Nor reserved words as names. Date is a reserved word.

[ralphjramirez]

Hello and thanks for responding. I am confused as I am not selecting that field. I am running a report using a query to select the fields that I need for the report and "Total" is not selected, Invoice Total is the field used that is in the "Elegance Invoices table 2". The input fields in my report does not include the "Total" field which is in the "Elegance Invoices Detail 2" table. Can you please explain what you mean by wildcard? As you can see by the embedded image I provided that Elegance Invoices 2 contains detail records that I need, specifically Description and Vendor name, only those 2 boxes are checked. The other fields are used to select or reject records per the criteria provided. Hope this makes sense.

I am also sorry it took me so long to respond but I got tied up with Grandkid duties.

Regards.............Ralph

[June7]

Sorry, misread table names. Total field is in Elegance Invoices Detail 2, not Elegance Invoices 2.

The first column of query grid is not fully displayed but appears to have Elegance Invoices 2.* in the field cell. * is wildcard. This tells query to pull all fields from that table.

Yes, I am also confused by this issue. Again, need to review report design.

[ralphjramirez]

Hello again and thanks for responding, I really appreciate it. I am sorry I should have extended the table names as they are so similar and not totally visible. I have included a screen capture of the report design, perhaps that will help. I did move overlapping fields because of the layout so the names aren't complete but I think quite obvious. I did move them around to make sure it somehow was inadvertently included buy not sure how that could happen since that data shouldn't have been available. I will probably have to code a work-around, if possible as it just doesn't make sense.

Thanks again......Ralph

[ralphjramirez]

Hello again, please note that the field in the date footer is a a date field followed by a label named "Total". This gives me a total by date and is not the field "Total".

Ralph

[ralphjramirez]

Hello again with good news, my report is no longer adding the field total. I made no changes whatsoever yet the problem is gone. I am sorry for all the inconvenience I have caused you. I am really baffled but relieved that the problem is gone. I couldn't find how I mark this thread closed so assume it is not required.

Best Regards and Thanks again.............Ralph

[ralphjramirez]

Hello, bad news as the report still gives the detail total as well as the invoice total. I inadvertently entered the date incorrectly and the record didn't show, I should have scrutinized the report more thoroughly. I will have to figure out a work-around. This is crazy or I am having a lot of senior moments.

Regards.............Ralph

[ralphjramirez]

Last bit of info. I have embedded a screen capture showing the fields that are available to this report, "total" is not one of them. Very puzzling..............Ralph

[ralphjramirez]

Hello again. I decided to create another query, identical to the one in this thread but instead of using it in a report I used it to create a new table, not any simpler than that. It still selects the "Total" field. This is driving me nuts. Please, can someone help on this one. I thought perhaps the Report was the problem which it turns out is not the case.

Hoping someone can help............Ralph

[IrogSinta]

I don't follow the report you're showing here. Where do you see the Total field? All I see are Invoice_Total fields and 1 label called Total. Are you saying that the amount in Invoice_Total is the same amount as Total? If so, could it be that you have one or more records where this is actually the case?

Ron

[ralphjramirez]

I don't follow the report you're showing here. Where do you see the Total field? All I see are Invoice_Total fields and 1 label called Total. Are you saying that the amount in Invoice_Total is the same amount as Total? If so, could it be that you have one or more records where this is actually the case?

Ron

Sorry, for the confusion. What I have is one table holding the invoice_total field and another table holding the detail records that accumulate the "Total" for one item in the invoice. The field "Total" is for one item of my purchase but in many invoices I have more than one item,1 to many relationship. In this report I am selecting fields that do not appear in the Elegance Invoices 2 table (primary table), Description and Vendor Name, these are in the Elegance Invoices Detail 2 table (detail table). The query continues to select the field "Total" that is in the detail table even though it is not selected. The label Total in the report is just the label in the heading for the summary total, in this case for the month. I also use the fields category, deductible and vendor code in the Detail and Vendors tables to filter out specific records and also the date field to select a date range each time the report is executed. All is working well except that it is including the field "Total" which would have the same total as in the Primary table as it contains item price, quantity and tax totals which in my report includes each item. All of this is caused by the query. I have created a duplicate of the query except it is a Make table query thinking this would be input to the report but the table contains the detail records as well. Hope this makes sense, I am going nuts trying to figure this out. Hope you or someone can find what is going on.
Thanks for responding...........Ralph

[June7]

Post the database.