Hi,

I have a question. Is it possible in access to show in a form(Pivot table or CrossTab) all the possible options from a Lookup Field (combo Box), even if there are no records with that option selected.

For instance, Table1--> Field1 and Field 2
each of these fields have 3 lookup options (Restricted to these 3 options only)
This means that if I place the options from field1 in a row and Field2 in a column, I would expect to see 9 possible combinations for them in a chart.
How can I see all 9 combinations, if I do not have 9 distinct records in my database. I find that access only shoes options that have been selected not the ones that haven't.
If I had 3 fields with lookup options the possible combinations get complicated, it isn't practical for me to have test data in my database, so that access could show all the possible combinations in the chart.

Example Disregard the "d" There are total of 12 records with 12 possible combinations.
I would like to see the "e f g h" show up in the chart even if option "h" has never been selected.

d e f g h
a 1 1 1 1
b 1 1 1 1
c 1 1 1 1

Thanks in advance.

Hi,

Please let me know whether there is a way to do this or if my question isn't clear.

My suggestion would be to create a crosstab query using the Wizard. Before you start however, crosstabs require three fields. The easiest way to get the third field into your example, if you don't already have one, would be to create an autonumber field in your table.

Follow the wizard and use the third field, which populates the matrix, as a “Count.” You’ll see what I mean when you get to it.

This should give you all combinations - matches and blanks.

As a favor to yourself once you are successful – open the query in Design View and take a look at the query grid and how it was constructed by the wizard. Then take a look at the SQL and see how that was constructed. This is a good way to learn how to create your own queries.

Good luck.

I have generated a cross tab query, however it only gives me combinations for matches, not for blanks,

for instance:

- x y
a 01
b 10

I can see this, however what about the option of "z" and the option of "c", which do not have a record for them yet.

for instance

- x y z
a 0 1 0
b 1 0 0
c 0 0 0

Note, abc and xyz are being pulled from a field that is a restricte combo box.

I want to know whether access has a way to show matches for combinations that have not yet been created, let me know if this is possible. Thanks.

It's a little difficult for me to visualize matches for combinations that have not been created yet. Could you attach your db?

The crosstab wizard will provide you with a column labeled "<>" for missing data. Does that help?

I can't attach the database because it is not accepted by the forum. However, it seems that there is no way to show all the options, I guess. I know about the "<>" option that a query automatically enters however that is if an existing record does not have an option selected. Thanks for your help.