Sorting by Month problems with Combobox

[Elizabeth]

I have a problem that probably has a very simple
solution. I will probably post this tot he "report"
newsgroup also.

I have a look-up list (combobox) for Month, which is based
on a fiscal year. The data in the list looks like this:

Col 1 Col2
1 July
2 August
3 September
etc.

I can get it to sort by month number, displaying the name
in text in a QUERY. However, when I try to create a
report based on the query, it lists the months in
alphabetical order, NOT the way it was displayed in the
query. I have tried to change the bound column number, I
feel like I have tried everything. Could someone respond
with an easy solution (if there is one) soon?

Thank you so much!

Elizabeth

 

[Guest]

Edit the grouping and sorting in the report, it's under
the view on the file menu.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the query for the report include the sort column. In this case the
Col 1 shown in your example. Then in the report's Grouping/Sorting
dialog box sort the month number (Col 1, or whatever you named it), not
on the month name.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQI6764echKqOuFEgEQJsUQCg0/dwUnht6I2TXv0N8QjqR9Ri59AAoKjG
LaFT3bA9J78NEE/lAVOyRMUO
=Cd51
-----END PGP SIGNATURE-----

 

[Elizabeth]

I had tried that, but it still did not work. I think I am
missing something...

 

[Elizabeth]

Thank you for your response. I am, however, still
confused. I don't know if I explained myself well
enough. I have a drop-down/look-up list box for the
fiscal month. It has two columns in it: Column 1 is the
number (1, 2, 3) . ALongside it is Column2, which lists
the names asscoiated with those numbers: July, August,
September. I do not think I know how to sort a column
within a combo box. I a regular user of Access, but with
limited capabilities.

If you coulod respond, that would be great!

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the query for the report include the sort column. In this case the
Col 1 shown in your example. Then in the report's Grouping/Sorting
dialog box sort the month number (Col 1, or whatever you named it), not
on the month name.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQI6764echKqOuFEgEQJsUQCg0/dwUnht6I2TXv0N8QjqR9Ri59 AAoKjG
LaFT3bA9J78NEE/lAVOyRMUO
=Cd51
-----END PGP SIGNATURE-----

I have a problem that probably has a very simple
solution. I will probably post this tot he "report"
newsgroup also.

I have a look-up list (combobox) for Month, which is based

 

[John Vinson]

I can get it to sort by month number, displaying the name
in text in a QUERY. However, when I try to create a
report based on the query, it lists the months in
alphabetical order, NOT the way it was displayed in the
query.

Put both the month name *and* the month number in the Query; in the
Report's Sorting and Grouping section sort by the month number, but
display the month name on the report.

 

[Elizabeth]

I am feeling really dumb. The month name and month number
are the same variable "Month." How would I spplit them
apart to use them in a query, or report? I am so sorry if
these questions are driving you nuts...

 

[John Vinson]

I am feeling really dumb. The month name and month number
are the same variable "Month." How would I spplit them
apart to use them in a query, or report? I am so sorry if
these questions are driving you nuts...

Well... no. They're not.

It seems you're yet another victim of Microsoft's misdesigned,
misleading, and obnoxious Lookup field type. The Month you see in your
table (the lookup field) DOES NOT EXIST in your table! It appears to,
but you're actually seeing the looked-up field.

Try changing the table definition for this field; on the Lookup tab
change Combo Box to Textbox. You'll now see the ID in the table.

Your Report should be based on a Query joining the main table to the
months lookup table. You will then have both field names.

 

[Elizabeth]

Thanks, John. I'll give it a whirl and let you know.

 

[ELizabeth]

Ok, I do have both field names--it worked! I joined the
main table to the month table on the MonthName(in Main
table) and MonthData (Month Table). It is sorted nicely
in the query. Yet again, though, when I tried to create a
report based on it, it does not display correctly. If I
use the "Report Wizard" it does, however the formatting is
terrible. I have tried in vain to figure out what I am
doing wrong. I am using several books, too. Do you have
any suggestions?

Thanks.

Elizabeth

 

[John Vinson]

Ok, I do have both field names--it worked! I joined the
main table to the month table on the MonthName(in Main
table) and MonthData (Month Table). It is sorted nicely
in the query. Yet again, though, when I tried to create a
report based on it, it does not display correctly. If I
use the "Report Wizard" it does, however the formatting is
terrible. I have tried in vain to figure out what I am
doing wrong. I am using several books, too. Do you have
any suggestions?

What are the datatypes of MonthName and MonthData? What fields exist
in the Month table, and what are their datatypes? What doesn't
"display correctly" - what do you see?

AND... have you gotten rid of your LOOKUPS yet!? <g>

 

[Elizabeth]

HELP!!!!!!!!!!!!!

I have been struggling with this for so long and my boss
is starting to wonder if I am competent (I think).
Anyway, to answer you questions...

1. MonthName has been changed from a "lookup" to a text
box per your instructions. It is part of a main table
called JPCourts which has other variables (a lookup field
for court names...uuugh), and then just several fields to
enter data in as numbers (i.e. number of translations,
number of interpretations).

2. MonthData, on the other hand, is now in its own table
(Months), but is a lookup field. This should not be,
should it? This is the only field in this table.

3. When I push the "report button" or choose to create a
report, both "MonthName" and "MonthID" are displayed as
April for the first month. Everytime I try to group by
MonthID, it still comes out as either 10 (the tenth month)
or April (the same thing). I need to have a month
grouping. The correct way it should be sorting is from 1 -
12 (one being July, 12 being June). I also would like a
month footer so the interpreters can see how many
translations and interpretations they did per month, per
court, for a given year. Is this too much to ask of
Access? Crystal Reports would be so much easier...

Do you understand what I have said? I am waiting
patiently (not) for your wisdom. Thanks!!

Elizabeth

 

[John Vinson]

1. MonthName has been changed from a "lookup" to a text
box per your instructions. It is part of a main table
called JPCourts which has other variables (a lookup field
for court names...uuugh), and then just several fields to
enter data in as numbers (i.e. number of translations,
number of interpretations).

Again... you didn't answer my question. I'm GUESSING (since you don't
say) that MonthName is a Text field containing text strings "July",
"August", etc.

2. MonthData, on the other hand, is now in its own table
(Months), but is a lookup field. This should not be,
should it? This is the only field in this table.

And is this a Numeric field or a Text field? or (given the oddities of
the Lookup misfeature) is it a numeric field concealed behind a text
field?

3. When I push the "report button" or choose to create a
report, both "MonthName" and "MonthID" are displayed as
April for the first month. Everytime I try to group by
MonthID, it still comes out as either 10 (the tenth month)
or April (the same thing). I need to have a month
grouping. The correct way it should be sorting is from 1 -
12 (one being July, 12 being June). I also would like a
month footer so the interpreters can see how many
translations and interpretations they did per month, per
court, for a given year. Is this too much to ask of
Access? Crystal Reports would be so much easier...

You're MAKING IT MUCH HARDER THAN IT NEEDS TO BE.

Your main table should have a *long integer* numeric field MonthID. It
should not contain the text of the month. It SHOULD NOT contain a
Lookup field of any description, if you ask me <g>...

Your MonthData table should have two fields: MonthID, long integer,
values 1 to 12; MonthName, text, values "July", "August", etc.

Your Form should have a Combo Box with:

RowSource: SELECT MonthID, MonthName FROM MonthData ORDER BY MonthID;
Bound Column: 1
Control Source: MonthID
ColumnWidths: 0, .75"

This will store the MonthID while displaying the month name.

Your Report should be based on a Query joining your main table to the
MonthData table. Include both the MonthID field from your main table
and the MonthName field from MonthData in the query; use MonthID for
your sorting and grouping so it sorts chronologically but display the
MonthName in textboxes to see a human-readable month name.

 

[Elizabeth]

Thanks so much, John. I always seem to make things
harder than I have to! I ended up figuring it out (I
don't know if I used the same sequence you did, though)
and let out a whoop of happiness. Tomorrow I will check
to see if I did just what you said. If I did not, I will
do so.

When you say "form" are you referring to the data entry
form?

THANKS!!!!!!

Elizabeth

 

[Elizabeth]

Thanks, John! Works great!

-----Original Message-----


Again... you didn't answer my question. I'm GUESSING (since you don't
say) that MonthName is a Text field containing text strings "July",
"August", etc.


And is this a Numeric field or a Text field? or (given the oddities of
the Lookup misfeature) is it a numeric field concealed behind a text
field?


You're MAKING IT MUCH HARDER THAN IT NEEDS TO BE.

Your main table should have a *long integer* numeric field MonthID. It
should not contain the text of the month. It SHOULD NOT contain a
Lookup field of any description, if you ask me <g>...

Your MonthData table should have two fields: MonthID, long integer,
values 1 to 12; MonthName, text, values "July", "August", etc.

Your Form should have a Combo Box with:

RowSource: SELECT MonthID, MonthName FROM MonthData ORDER BY MonthID;
Bound Column: 1
Control Source: MonthID
ColumnWidths: 0, .75"

This will store the MonthID while displaying the month name.

Your Report should be based on a Query joining your main table to the
MonthData table. Include both the MonthID field from your main table
and the MonthName field from MonthData in the query; use MonthID for
your sorting and grouping so it sorts chronologically but display the
MonthName in textboxes to see a human-readable month name.


.