Selecting different fields in a query

  • Thread starter Thread starter Michaelkw
  • Start date Start date
M

Michaelkw

Novice at access, have different month fields in a table that have a yes/no,
have a query where I need to change the Months I use in that query,ie run the
query just looking to see which records have a yes in Dec, then run it a
different time to see which records have a yes in Nov. have been changing
the fields in the query each time. Was just wondering if there is way to
have a drop down list to select which field I want to query use to make it
more user friendly. A report is also relies on this query.

Thanks
mike
 
have different month fields in a table that have a yes/no,
You did not say what the rest of the data in your table was but it seems
like you have a spreadsheet layout instead of a relational database function.
Post your complete table structure with field names and datatype. Also a
sample of the data in your fields for more help.
 
It is customer records, name, address etc. along with orders that they have
paid for and need to pick up. So, Customer A has ordered and paid for a Dec
shipment so the paid field is check yes, if they have not paid its no. There
is also a Dec picked up field that is yes or no to determine if they have
picked up their order yet.

Looking to run a single month query at a time. do not need to show all the
months in the query but be able to select the different months I want the
query to run on.

and yes it is like spreadsheet layout. is there a better way? We need to
store all the monthly info for each customer for about a year.

Fields include:
Name (text), Address, phone, December order Paid(yes/No), November order
Paid (yes/No) December Order Picked Up (yes/no) November order Picked Up
(yes/no) etc
 
Use two tables in a one to many relationship.
Customer ---
CustID - Autonumber - primary key
Name (text)
Addr1 (text)
Addr2 (text)
City (text)
State (text)
Zip (text)
Phone (text)
etc.

Orders ---
OrderID - Autonumber - primary key
CustID - number - long integer - foreign key (associated w/ customer table)
OrderDate - DateTime
Paid - Yes/No
Picked_Up - Yes/No
etc

You could have another table that is the order details that is related to
the order table.

Use a form/subform (Customer/Order) for data entry, editing, etc with
Master/Child links on CustID.

You can use query with criteria to display a months records or using
additional criteria show only one customer.

You should be able to store a lot more that one years records as Access size
limit is 2GB.
 
It is more of a reoccuring billing than an order, we bill the same or new
customers automatically each month, a wine club. So do not really take an
order. will this still work?
 
Yes it will and then you probably do not need the third table with details
showing product/service, quality, price, etc.
 
Back
Top