Generate report based on checkbox value

[accessn00blet]

I apologize if this has been asked and resolved however it's been over a decade since I've used Access and have been researching this for a little over an hour with naught to show for it.



I have a database of service providers. These providers can be in any combination of up to 16 different networks.

Ideally I would like to create a single report wherein I choose one of the 16 networks and a corresponding list of providers will be spit out. Alternatively, I am just as comfortable with creating 16 individual reports, one for each network.

I would like the output to resemble a Word mail merge where the column rows of the database are the "merge field" and any empty fields are hidden. I would prefer the output to be in a single column if that makes any sense. Basically it would look as below:

• State
• City
• Neighborhood1
• Neighborhood2
• Name1
• Name2
• Address1
• Address2
• Phone
• Other1
• Other2
• Other3
• Other4
• Other5

This output would be critical in how we format these providers in our published materials for the marketing team.

Any assistance would be greatly appreciated.

Thanks!

[June7]

What do you mean by 'combination of up to 16 different networks'? Show example of raw dataset.

Should not need 16 report objects, just 1 report and apply filter criteria.

You want fields arranged vertically? An empty field will probably leave blank space because the control is still there.

[accessn00blet]

Hu June7.

Thanks for your response.

I tried attaching both a small database and an excel file but I'm having issues. I can provide later if needed.

There are 6 providers, each belonging to at least one of 5 networks. In my full dataset there are 16 but I felt 5 would suffice for this example. The way we display our providers is in vertical columns on the back of a brochure. There may be four or five columns but they run from bottom to top instead of left to right.

Looking at the data set, if I were to pull a report for the Full Network I would like to have the following results delivered:

New York
Queens
Astoria
Provider #1
123 Main Street

Corona
Provider#2
123 Main Street

Douglaston
Provider #3
Third Floor
123 Main Street

Manhattan
Upper West Side
Provider #4
123 Main Street

Harlem
Provider #5
123 Main Street

New Jersey
Essex
Newark
Provider #6
123 Main Street

As you can see there is a hierarchy associated. State then County then Neighborhood.

If I instead wanted a list for the DC37 network I would expect the following:

New York
Queens
Astoria
Provider #1
123 Main Street

Manhattan
Upper West Side
Provider #4
123 Main Street

Harlem
Provider #5
123 Main Street

New Jersey
Essex
Newark
Provider #6
123 Main Street

Since Provider #2 and Provider #3 are not in the other network they are of course omitted from the report.

I know that in a Word Mail Merge you can suppress any empty fields. Is that an option here?

Thanks again.

[June7]

I have seen threads discussing that issue. Setting the textbox CanShrink property to Yes might resolve.

http://en.allexperts.com/q/Using-MS-...pty-fields.htm

[accessn00blet]

Thanks June7. Any guidance on how I can create a single report which will deliver results based on the desired network?

[June7]

Maybe set up report to group on the networks and new page after each and/or apply filter criteria that references a textbox on form when the report is opened. Something like:

DoCmd.OpenReport "report name", , , "NetworkID = " & Me.tbxID

I don't know your data structure so can't be specific.

[accessn00blet]

Not sure if this helps but I'm using Yes/No checkboxes to indicate which network the provider is in.

I've uploaded a screenshot to show what the data entry form looks like. As I scroll through the various provider entries I can edit things like their address, name, phone, etc. I can also add or remove them from various networks by filling or clearing the appropriate box.

I still can't upload my database even though it contained just the empty table and the form.



Thanks again.

[June7]

14 checkboxes - does that mean 14 networks? Each of these checkboxes is bound to a field? Each record can be associated with only one network? This is not a normalized data structure and will give you lots of headaches. Should be one field with 14 choices selected from a combobox or listbox.

If each record can be associated with more than one network, having 14 fields is still problematic.

[accessn00blet]

Hi June7.

Yes, there are 14 networks however each provider can be in any one of them.

When I created the table I set the Field Names to what you see in the image. I then went into Design view and set the Data Type to "Yes/No" for all the networks.

I was finally able to get the db uploaded. GVS Providers Database.accdb

Is there a better way to construct this?

Worse comes to worse I'll just have to keep it all in an excel workbook and do mail merges to create the "reports".

Thanks.

[June7]

If each record can have only one network, then should be a single field with 14 choices, not 14 yes/no fields. That is the better way to construct.

[accessn00blet]

Unfortunately that's not the situation as one provider can be in several different networks. It looks like the best way to do this is to just use MS Word to do a directory mail merge for each individual network.

Thanks!

[June7]

Then normalized data structure would be a related table for the network associations.

What you have could probably be managed with some creative query techniques and/or VBA.