query fields

[Elsie]

hi all, have a table with following fields:

item: item code
cust-seq: ship-to destination code
cust-num: customer code
forecast-year: year which forecast is for
qty-jan: qty for this item in January (followed by qty-feb, qty-mar,
qty-apr, qty-may, qty-jun, qty-jul, qty-aug, qty-sep, qty-oct, qty-nov,
qty-dec)
price-jan: price for this item in January (followed by price-feb, price-mar,
price-apr, price-may, price-jun, price-jul, price-aug, price-sep, price-oct,
price-nov, price-dec)
amt-jan: price for this item in January (followed by amt-feb, amt-mar,
amt-apr, amt-may, amt-jun, amt-jul, amt-aug, amt-sep, amt-oct, amt-nov,
amt-dec)

I am using the appropriate qty, price and amt for a certain month in a query
based on this table, which will be displayed in a report to the user after
some calculations.

but how can I allow the user to do selection of the correct qty, price and
amt?
e.g.: if the user wishes to get data for august, meaning that qty-aug,
price-aug and amt-aug should be selected in my query. if the fields of the
query from the previous month are qty-jul, price-jul and amt-jul, how do I
change these fields to get the august data, without having for me to go into
query and grab the fields from the table?

I have a form whereby users can select current month, last month and next
month.

 

[Van T. Dinh]

Sorry but the most appropriate advice with what you posted
is to re-design the Table completely. You Table at the
moment looks like an Excel spreadsheet which is not the
correct structure for database in this case.

Some of the problems that I can see:

1. You are trying to store 2 distinct entities
(forecasted numbers and prices) in 1 Table. Correctly,
each entity should be store in a separate Table.

2. Your Table is de-mormalised (i.e. Columns that store
similar data, i.e. your forecast olumns and your price
columns). This is why you have problems with picking up
August. Tables need to be normalised so that Access / JET
can work efficiently.

3. You store data in Field names, e.g. the Jan, Feb, ...
are actually DATA and they should be stored in Field
values, not Field name. Access/JET cannot retrieve data
store in Field names. Hence the same August problem.

What I wrote may not make sense to you but the best way
for you is to find out about the Relational Database
Design Principles and Database Normalisation technique and
then apply these to your database.

HTH
Van T. Dinh
MVP (Access)

 

[Elsie]

That table was created by another person. I just use the data in it in my
query, which works well since I can access the database window, but not for
the normal users.
How about if the table is designed as:

<month+year>: year, item, cust-num, qty, price, amt?

eg: 0404: April 2004 and so on

 

[Van T. Dinh]

I am not sure of your notation but if you meant "0404" as the Table name
then you store data in the (Table) name which is a no-no as per my previous
post.

Since you cannot change the Table anyway, why not buy a Relational Database
Design book and give it to the person who has the authority to modify the
design of the Tables / Database. Read it first if you are interested.

 

[Elsie]

Ok, I think maybe I can have 12 buttons for 12 reports, each report for each
calendar month, as temporary solution. so the January report will select
from the fields: item, customer code, qty-Jan, price-Jan and amt-Jan and so
on...

the table was created using code. the code will access a linked table in our
erp database, loop through and extract the data to create that table in
Access as output.

as the linked table has fields as : item, customer code, fiscal
year,quantity, unit-price and amount.

quantity, unit-price, amount are arrays fields. hence when the Access table
was created, the fields will become: item, customer code, year, qty-Jan,
qty-feb....,qty-dec, price-Jan...price-dec and amt-Jan...amt-dec.