Variable report fields

[melody.anne]

So I am working on this database and it has to do with orders. There are about 15 different types of orders. We download data daily, and replace the source of our Access data from Excel as we download it every day. Problem is, some days we have all 15 types of orders, some days we don't have all 15. On the days that we don't have all the types of orders and I try to open up my report, it gives me an error of the report not being able to recognize the field (which is the type of order that isn't there that day).

How can I make it so that it ignores that the type of order isn't there that day? Because it's annoying having to remove/re-add the field(s) as needed. I just want to have all 15 fields, and if it doesn't recognize it because that type of order wasn't taken that day, just ignore it and fill up the rest of the types of orders.

Hope I explained myself properly.

Thanks!

 

[MarkK]

You need to describe the specifics of your data. What are the exact fields? What exact data is missing from those fields that kills your process? How is an "order type" distinguished in your data?

Hope this helps,

 

[melody.anne]

You need to describe the specifics of your data. What are the exact fields? What exact data is missing from those fields that kills your process? How is an "order type" distinguished in your data?

Hope this helps,

My order types are categorized as "OC01", "OC02", "OC03", etc. Those are values that come from a column named "Order Type". It is cross-tabbed so that "OC01", "OC02", "OC03", are turned into headers and under it is just a number counting how many times each order "occur". When Access doesn't find a specific order type in the column it just gives an error stating it doesn't recognize it, and I have to remove the field or replace it with another value that was entered that day.

This is Access 2010, by the way.

 

[MarkK]

When Access doesn't find a specific order type...

Access doesn't know anything about "order type." Any concept of "order type" is one that you implemented, and that implementation is what we need to understand in order to troubleshoot "the problem."

It sounds like you are using a crosstab query, which is not commonly used in processing data, since you can't know what fields will be present. Design a process that doesn't use a crosstab query. Then your field names will not vary, and you won't need to redesign your process to fit your data.

For more help with these issues, you'll need to describe your process in greater detail.

 

[melody.anne]

Design a process that doesn't use a crosstab query. Then your field names will not vary, and you won't need to redesign your process to fit your data.

I get what you're saying. How would I go about counting each order type to fill in the fields if I'm not using a cross-tab query?

 

[MarkK]

Lol, here's the thing: You have not described what you are trying to do overall. You have described a narrow problem that you have a query that returns fields that can't be known until runtime. As a result, your process fails. OK, but what is your process? What are you trying to do? Why do you need to count order types?

...fill in the fields...

Fill in what fields?

Your readers only know what you told them. Look at what you've written. What do you think we need to know to offer more comprehensive help. Can you post the database?

Hope this helps,

 

[melody.anne]

Lol, here's the thing: You have not described what you are trying to do overall. You have described a narrow problem that you have a query that returns fields that can't be known until runtime. As a result, your process fails. OK, but what is your process? What are you trying to do? Why do you need to count order types?

Fill in what fields?

Your readers only know what you told them. Look at what you've written. What do you think we need to know to offer more comprehensive help. Can you post the database?

Hope this helps,

Okay, let me try to explain this better. I am making a report to summarize what types of orders have been made. From that report I will be able to click on the fields (the count values from every type of order) to see the information regarding that specific order type.

I have the table from which I am taking the order types; it has this column that looks a bit like this:
Order Type
OC01
OC02
OC01
OC03

What I want in my summary report is this:

Supervisor | Employee | OC01 | OC02 | OC03
--Person X-|-Person Y-|---2--|---1--|---1--

What I did was make a crosstab query that made the types the headers like I showed above, and count each order type under it in the column. But say that if the column for "order type" I already provided is yesterday's, and today's looks like this:
Order type
OC01
OC01
OC03
OC03
OC01

Then Access will give me an error, because OC02 is no longer there and my summary report already had OC02 as a field (the count total for OC02 is the field I'm referring to); and I would have to remove it from the report to make it work again.

Do you think that if I modified this query in a way that I made a "count" for every single type of order separately (i.e. [count from order type "oc01"], [count from order type "oc02"], [count from order type "oc03"] -I know this isn't the format, by the way-), then if that type of order isn't there on one day it should just show "0" instead of not recognizing it?

If I'm still not clear enough, I'll post a screencap tomorrow.

 

[MarkK]

But if you look at the sections--or groups--in a report, it would be easier there to make a structure like . . .

Supervisor: X
    Employee: Y
        Type:    Quantity:
        OC1      2
        OC2      1
        OC3      1

. . . which has four fixed field names, and if data is missing it just means the row doesn't exist, which is how you want it. Processing a variable number of rows in easy, processing a variable number of columns is hard.

This is database thinking VS spreadsheet thinking. A database is not like a spreadsheet.

Hope this helps,

 

[melody.anne]

But if you look at the sections--or groups--in a report, it would be easier there to make a structure like . . .

Supervisor: X
    Employee: Y
        Type:    Quantity:
        OC1      2
        OC2      1
        OC3      1

. . . which has four fixed field names, and if data is missing it just means the row doesn't exist, which is how you want it. Processing a variable number of rows in easy, processing a variable number of columns is hard.

This is database thinking VS spreadsheet thinking. A database is not like a spreadsheet.

Hope this helps,

Yes but when there's tons and tons of different supervisors and employees it's a very, very long summary. Is there really not a better way to accomodate this and make it look like I mentioned? With the types on top and the quantities under?

 

[Gasman]

PMFJI,

I think what you are trying to achieve would require creating the report dynamically.
I had a similar problem a while back, but fortunately did not have to investigate it any further. (I was thinking ahead )

Have a google for 'dynamic report based on crosstab query access'

HTH

 

[melody.anne]

PMFJI,

I think what you are trying to achieve would require creating the report dynamically.
I had a similar problem a while back, but fortunately did not have to investigate it any further. (I was thinking ahead )

Have a google for 'dynamic report based on crosstab query access'

HTH

Googled, found some things. I think this is exactly what I'm looking for, thanks. I'm probably gonna take a long time to achieve this, but glad to know what I want to do is possible after all

 

[stopher]

You can force your cross-tab query to show all required heading for your report regardless of whether data exists. From Allen Brownes website:

Specify column headings

Since the column headings are derived from a field, you only get fields relevant to the data. So, if your criteria limits the query to a period when Nancy Davolio made no sales, her field will not be displayed. If your goal is to make a report from the crosstab, the report will give errors if the field named "Davolio, Nancy" just disappears.

To solve this, enter all the valid column headings into the Column Headings property of the crosstab query. Steps:
1.In query design view, show the Properties box (View menu.)
2.Locate the Column Headings property. (If you don't see it, you are looking at the properties of a field instead of the properties of the query.)
3.Type in all the possible values, separated by commas. Delimit text values with quotes, or date values with #.

For the query above, set the Column Headings property like this (on one line):

"Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne", "Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret", "Suyama, Michael"

Side effects of using column headings:
•Any values you do not list are excluded from the query.
•The fields will appear in the order you specify, e.g. "Jan", "Feb", "Mar", ...

Where a report has a complex crosstab query as its Record Source, specifying the column headings can speed up the design of the report enormously. If you do not specify the column headings, Access is unable to determine the fields that will be available to the report without running the entire query. But if you specify the Column Headings, it can read the field names without running the query.

An alternative approach is to alias the fields so the names don't change. Duane Hookom has an example of dynamic monthly crosstab reports.

hth
Chris

 

[melody.anne]

You can force your cross-tab query to show all required heading for your report regardless of whether data exists. From Allen Brownes website:

Specify column headings

Since the column headings are derived from a field, you only get fields relevant to the data. So, if your criteria limits the query to a period when Nancy Davolio made no sales, her field will not be displayed. If your goal is to make a report from the crosstab, the report will give errors if the field named "Davolio, Nancy" just disappears.

To solve this, enter all the valid column headings into the Column Headings property of the crosstab query. Steps:
1.In query design view, show the Properties box (View menu.)
2.Locate the Column Headings property. (If you don't see it, you are looking at the properties of a field instead of the properties of the query.)
3.Type in all the possible values, separated by commas. Delimit text values with quotes, or date values with #.

For the query above, set the Column Headings property like this (on one line):

"Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne", "Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret", "Suyama, Michael"

Side effects of using column headings:
•Any values you do not list are excluded from the query.
•The fields will appear in the order you specify, e.g. "Jan", "Feb", "Mar", ...

Where a report has a complex crosstab query as its Record Source, specifying the column headings can speed up the design of the report enormously. If you do not specify the column headings, Access is unable to determine the fields that will be available to the report without running the entire query. But if you specify the Column Headings, it can read the field names without running the query.

An alternative approach is to alias the fields so the names don't change. Duane Hookom has an example of dynamic monthly crosstab reports.

hth
Chris

I wish you'd posted this 3 hours sooner! After a long time researching I figured this out and it did in fact work. Thanks, regardless!

 

[Gasman]

The dynamic method is more flexible for more columns, though a little more complicated, but worth bearing in mind.