Select a field in query using combo box

[hotplate]

I have a table that has fields like: account, jan, feb, mar, apr, may,
jun, jul, etc....

I need to create a query that gets the account and I can choose one of
the remaining fields using a combo box. The problem when I tried that
is every record would say jan, or feb, or mar... depending on what I
selected.

Any suggestions? All help is greatly appreciated.

Thanks

 

[Access Developer]

I have a table that has fields like: account,
jan, feb, mar, apr, may, jun, jul, etc....

If you wish to accomplish your purpose with a query, redesign your table; it
does not follow relational design principles/guidelines, to wit, it is
unnormalized. What you've done is called "committing spreadsheet".

You can _construct_ a query to work with your table in VBA code.

But life will be a lot simpler if you redesign the table with fields of:

Account
BillingMonth
Amount (or whatever value represented in the fields you've labeled
by month )

And create a query, where you provide a parameter for Account and for
Billing Month.

Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

 

[John W. Vinson]

I have a table that has fields like: account, jan, feb, mar, apr, may,
jun, jul, etc....

Then you have an incorrectly structured table. If each Account has many
(bills? payments?) then the proper structure is a one-to-many relationship to
a table with an AccountID, PaymentDate (or if you insist, payment month), and
Amount (or whatever is currently in your Jan field).

I need to create a query that gets the account and I can choose one of
the remaining fields using a combo box. The problem when I tried that
is every record would say jan, or feb, or mar... depending on what I
selected.

With your current structure you will need to write VBA code to construct a SQL
statement using the month chosen from your combo box. Not too hard but
certainly not trivial.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[hotplate]

Thanks for all your replies. I was thinking that those were the
answers I was going to get. Unfortunately the table gets dumped from
an ERP and I have no control over the structure of the table.

Heck, I didn't even write the database. This was a case of someone
starting a database and getting in way over their head. I
straightened out 90% of the database I guess I was trying to find the
easiest way to fix this.

J.

 

[John W. Vinson]

Thanks for all your replies. I was thinking that those were the
answers I was going to get. Unfortunately the table gets dumped from
an ERP and I have no control over the structure of the table.

Heck, I didn't even write the database. This was a case of someone
starting a database and getting in way over their head. I
straightened out 90% of the database I guess I was trying to find the
easiest way to fix this.

J.

I'd suggest creating a properly normalized table and migrating the imported
data into it, using a "Normalizing Union Query". You can create a query in the
SQL window (the grid can't handle it) like

SELECT Account, 1 As TheMonth, [Jan]
FROM wideflat
WHERE [Jan] IS NOT NULL
UNION ALL
SELECT Account, 2 As TheMonth, [Feb]
FROM wideflat
WHERE [Feb] IS NOT NULL
UNION ALL
SELECT Account, 3 As TheMonth, [Mar]
FROM wideflat
WHERE [Mar] IS NOT NULL
UNION ALL
<etc>

You can then base an Append query on this query to populate a tall-thin table
which you can use for your reports.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com