Access Report - Novice

[DWATS]

Hi All,

New to the forum and new to Access. I am working on an application that converts data into a specific text format. It currently has 2 tables, 1 query-
Tables
1. Formatting characters, separators, carriage returns, etc.
2. Order information imported from an online store - Order #,Part #, Part Description, Qty, etc.
Query
1. combines data from tables 1 & 2 along with a few calculated fields.

the query may contain several rows with the same order # because there are several part #'s per order. I want to pull a report and have 1 order #/customer information, and then list multiple part # and descriptions underneath. Currently have a report that displays order #/customer information with subreport for part #'s, qty, etc. When report is generated it gives me multiples with the same order #. Can anyone help me?

[June7]

With only 2 tables, probably don't even need subreport. Use report Sorting & Grouping features. Set Order # textbox HideDuplicates property to Yes.

If you want to provide db for analysis, follow instructions at bottom of my post.

[DWATS]

Just sent the file. I did bot h your recommendations and did not get the results I was hoping for. It omits just the order #, not the entire set.

[June7]

Report and subreport have same data source. This makes no sense. Why are you doing that? Remove subreport.

Why have header for EXTERNALORDER1 but not put field in there?

Why is DATEEXTERNAL1 a text instead of date/time field?

Why are fields with monetary and quantity amounts text instead of number type?

[DWATS]

Thank you for the guidance, I really appreciate. Just getting started with Access so some of your questions I don't have a good answer for, except I've been troubleshooting to get the result I want and may have changed to experiment/troubleshoot.
I cant figure out the easiest way to have the report generate with ***Start Order***- ***End Customer*** once, but ***Start Products***-***End Products*** in multiples (depending on how many products have been ordered).
I will change the fields back to appropriate. Can you recommend a solution?

[June7]

The OPEN-CLOSETBL is an unusual approach. I have always used report design (spacing, line control, textbox position, text alignment, color/shading) to indicate beginning and ending of records and sections. Instead of this table to provide text for textboxes, could have just built labels in report.

I presume IDCustomer is customer identifier. Looks like each customer can have multiple orders.

When you create a group section, should put fields/controls in that section. So if you have a customer header then put customer info into that section. If you have an order # header then put order info into that section. Then info on associated parts will be in detail section. So put the OPEN-CLOSETBL fields into appropriate group header/footer section as well. Have to activate footer section for each group. Order groups in whatever hierarchy you want, probably customer then order. Then could apply a sort on part identifier if you want.

Should probably use & instead of + for concatenation. The + returns Null when field is null so the prefix text will not show and end up with a lot of confusing white space. Why not using labels instead of concatenating?

Advise not to use all caps in naming convention - it is harder to read. Better would be OrdersTBL, OrdersQRY, OrdersSubrpt. Also should avoid spaces and punctuation/special characters (underscore only exception).

This is not a normalized data structure.

[DWATS]

Understood! This is very helpful. I will work on it tonight and touch base with you when I get it fixed.

[DWATS]

I was able to work on this last night and took many of your recommendations and it's turning out nice, thanks again. One thing I am trying to understand, and the reason I was using + instead of &... I would like the prefix and/or the blank lines (on report) to not show if there is no data. I am having a hard time getting the report to output as text with no blank lines. It's important that the text export does not include blank lines. Any suggestions?

[June7]

Unfortunately, even if controls are blank because of Null or empty string, they still occupy space. Try to minimize by setting textbox CanShrink property to Yes.

So you need to concatenate the parts fields as well?

[DWATS]

Ok. I set them all to CanShrink - Yes. The parts fields also have to concatenate, yes. The text needed for data processing system must include a consistent field name that maps exactly. So, all data needs a set prefix and separator. I feel like the report is working good now. Just need to figure out a way to get text file to look exactly like report (no blank spaces). I am exporting text and opening in notepad ++ and then - find and replace blank spaces. It works okay, but not crazy about the extra step and its omitting a few pieces of data. Not sure what to do next.

[June7]

What do you mean by blank spaces - blank lines in text file?

[Gicu]

So you are exporting an Access report as a text file than manipulate that file in an external program to remove spaces? I would say that is definitively the wrong approach. Can you upload a sample of your desired outcome (text file)? There are many ways for Access to create a text file other than your current one.

Cheers,
Vlad

[DWATS]

Sorry. Yes, blank lines in text file.

[DWATS]

This is how it looks in the access report and how it needs to look when exported as text

This is exported text file. Notice Address1:, AddressZip: and ShipMethod: are missing in addition to all the blank lines?

[June7]

The sample text file shows labels with no data. This contradicts your stated reason for using + to concatenate literal string with field.

My export shows Address1, AddressZip, and ShipMethod

Complex reports will usually not export to Excel or text file satisfactorily. Better to export queries. Another approach is writing to text file with VBA.