M
Martin
Forgive me if this is rather long.
I have an Access database that is created by a state agency and is
protected. One of the tables has detail information by company. This table
is linked into another database I created from which various queries are run.
It is in this database that my question starts.
Depending on the query, a given company can be in a different group. For
example, Company A could be in a group called Sales in one query, but in a
group called Dist in another. I created a table that provides a "mapping"
between the company and the assigned group by query. Because each company
can be seen in about 20 different queries, I have a table with 20 fields by
company. For example, my REPORTS table would look like this:
COMPANY RPT1 RPT2 RPT3 ........RPTXX
Joe's Bar Sales Dist Retail ........
19th Hole Dist Retail Sales ........
When I run the queries, everything to this point is working fine. Data is
being sorted ascending by the group stored in the above table.
Now somebody decided that they do not want the queries in order by group.
They want them in some non-standard order. For example, instead of
Dist/Retail/Sales they want the order to be Sales/Dist/Retail. The only way
I could come up with to do this was to create another table that has a record
for each group and a field indicating the order in which they should be
sorted. To continue my example, my SEQUENCE table would look like this:
GROUP SEQ
Dist 2
Retail 3
Sales 1
I set up the relationship between the REPORTS table and the SEQUENCE table
via the relationships window.
Here is my problem: When I add the SEQUENCE table to my query, and add the
new SEQ field to my query for the sort, no data is retrieved when I run it.
The only obvious difference is the addition of this new table.
Am I missing something in this new addition? Or do I need to revisit the
design of this to allow for this non-standard sort?
Thanks.
I have an Access database that is created by a state agency and is
protected. One of the tables has detail information by company. This table
is linked into another database I created from which various queries are run.
It is in this database that my question starts.
Depending on the query, a given company can be in a different group. For
example, Company A could be in a group called Sales in one query, but in a
group called Dist in another. I created a table that provides a "mapping"
between the company and the assigned group by query. Because each company
can be seen in about 20 different queries, I have a table with 20 fields by
company. For example, my REPORTS table would look like this:
COMPANY RPT1 RPT2 RPT3 ........RPTXX
Joe's Bar Sales Dist Retail ........
19th Hole Dist Retail Sales ........
When I run the queries, everything to this point is working fine. Data is
being sorted ascending by the group stored in the above table.
Now somebody decided that they do not want the queries in order by group.
They want them in some non-standard order. For example, instead of
Dist/Retail/Sales they want the order to be Sales/Dist/Retail. The only way
I could come up with to do this was to create another table that has a record
for each group and a field indicating the order in which they should be
sorted. To continue my example, my SEQUENCE table would look like this:
GROUP SEQ
Dist 2
Retail 3
Sales 1
I set up the relationship between the REPORTS table and the SEQUENCE table
via the relationships window.
Here is my problem: When I add the SEQUENCE table to my query, and add the
new SEQ field to my query for the sort, no data is retrieved when I run it.
The only obvious difference is the addition of this new table.
Am I missing something in this new addition? Or do I need to revisit the
design of this to allow for this non-standard sort?
Thanks.