RE: Query Problem, Please Help!!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi.

> the problem is that i need
> this table to be laid out the way it is. The reason for this is that it is a
> subform in a form and it is more user friendly that way.

The reason that experts recommend using a query as the Record Source for a
form instead of a table is because the table is so inflexible. The query
will allow the designer to display the data in just about any way needed. If
you store the data in Normalized tables, SQL is a very powerful programming
language and will allow you to manipulate that data for display purposes.
SQL queries are very flexible and can even make calculations and display
those calculations for your forms and reports when those values aren't even
stored in the tables.

> The way that your
> telling me to do it wouldnt be user friendly in my main form, all it would
> show would be numbers.

You need to join more than one table together in a query to display the
"text" values associated with those numbers in the main table. Make the
subform's Record Source Property this query, and then it will be very
user-friendly.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


"HOW DO I CREATE A SEARCH FORM????" wrote:

> I completely understand what you two guys are telling me and trust me i'm not
> trying to complicate my life on purpose here, but the problem is that i need
> this table to be laid out the way it is. The reason for this is that it is a
> subform in a form and it is more user friendly that way. The way that your
> telling me to do it wouldnt be user friendly in my main form, all it would
> show would be numbers.
>
> "'69 Camaro" wrote:
>
> > Sorry. I neglected to include the month criteria. The corrected SQL
> > statements would look like this:
> >
> > With the month name in the table:
> >
> > SELECT *
> > FROM tblST_1
> > WHERE ((checkmark=no) AND (STMon = DatePart("m", Date())) AND (STYear =
> > DatePart("yyyy", Date())));
> >
> > With a foreign key for the month name:
> >
> > SELECT STYear, MonthName, checkmark, ClientID
> > FROM tblST_1 INNER JOIN tblMonths ON tblST_1.STMon = tblMonths.ID
> > WHERE ((checkmark=no) AND (STMon = DatePart("m", Date())) AND (STYear =
> > DatePart("yyyy", Date())));
> >
> > HTH.
> >
> > Gunny
> >
> > See http://www.QBuilt.com for all your database needs.
> > See http://www.Access.QBuilt.com for Microsoft Access tips.
> >
> > (Please remove ZERO_SPAM from my reply E-mail address, so that a message
> > will be forwarded to me.)
> > Beware to those who use munged addresses: known newsgroup E-mail harvesters
> > for spammers are Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx
> >
> > - - -
> > When you see correct answers to your question posted in Microsoft's Online
> > Community, please sign in to the Community and mark these posts as "Answers,"
> > so that all may benefit by filtering on "Answered questions" and quickly
> > finding the right answers to similar questions. Remember that questions
> > answered the quickest are often from those who have a history of rewarding
> > the contributors who have taken the time to answer questions correctly.
> >
> >
> > "'69 Camaro" wrote:
> >
> > > Hi.
> > >
> > > > how would i do this???
> > >
> > > First, you would redesign your table into a normalized table. In this
> > > redesign, you would avoid time-wasting bug-makers, such as Reserved Keywords
> > > (like "year"), and non-alphanumeric characters in table names and field
> > > names, such as spaces and dashes. The one exception is the underscore
> > > character.
> > >
> > > You would eliminate the 12 month columns and replace them with two columns,
> > > one to hold the month (preferably not the name of the month, but the foreign
> > > key of a lookup table containing the names of the months), and one to hold
> > > whatever value (if there is one) which is currently being placed in any of
> > > the current 12 month columns for each record. If the current 12 month
> > > columns only hold a value to indicate which month the record is for, then you
> > > can skip creating this second column.
> > >
> > > You would change the name of the "year" column to something like "STYear"
> > > and change the data type to number (size can be Integer or Long).
> > >
> > > If you did these things, the query would be super easy, such as the following:
> > >
> > > SELECT *
> > > FROM tblST_1
> > > WHERE ((checkmark=no) AND (STYear = DatePart("yyyy", Date())));
> > >
> > > If you used a foreign key to store the months, then the query could join the
> > > two tables and the SQL statement would look like the following:
> > >
> > > SELECT STYear, MonthName, checkmark, ClientID
> > > FROM tblST_1 INNER JOIN tblMonths ON tblST_1.STMon = tblMonths.ID
> > > WHERE ((checkmark=no) AND (STYear = DatePart("yyyy", Date())));
> > >
> > > HTH.
> > >
> > > Gunny
> > >
> > > See http://www.QBuilt.com for all your database needs.
> > > See http://www.Access.QBuilt.com for Microsoft Access tips.
> > >
> > > (Please remove ZERO_SPAM from my reply E-mail address, so that a message
> > > will be forwarded to me.)
> > > Beware to those who use munged addresses: known newsgroup E-mail harvesters
> > > for spammers are Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx
> > >
> > > - - -
> > > When you see correct answers to your question posted in Microsoft's Online
> > > Community, please sign in to the Community and mark these posts as "Answers,"
> > > so that all may benefit by filtering on "Answered questions" and quickly
> > > finding the right answers to similar questions. Remember that questions
> > > answered the quickest are often from those who have a history of rewarding
> > > the contributors who have taken the time to answer questions correctly.
> > >
> > >
> > > "HOW DO I CREATE A SEARCH FORM????" wrote:
> > >
> > > > The situation is as follows:
> > > >
> > > > I created a table that is called "ST-1", it has 14 fields total, 12 of them
> > > > labeled after every month of the year, 1 labeled "year" and the last one is a
> > > > "client id" field.
> > > >
> > > > "client id" and "year" fields are for text input, while the january -
> > > > december fields are yes/no or checkmark fields.
> > > >
> > > > I'm trying to create a query from this table that shows me only the records
> > > > that contain the current year and where the current month checkmark="no".
> > > >
> > > > how would i do this???
> > > >
> > > > i hope that i was clear enough, if anyone needs further clarification on
> > > > what i'm trying to do please let me know.
> > > >
> > > >
> > > >
.

Relevant Pages

  • Re: Reverse last name first name
    ... Excuse my ignorance. ... > There is never any reason to have to query these names> from this table, other than just to display them. ...
    (microsoft.public.sqlserver.programming)
  • Re: Reverse last name first name
    ... > Excuse my ignorance. ... I was just clarifying that the reason I did not ... >> There is never any reason to have to query these names ... other than just to display them. ...
    (microsoft.public.sqlserver.programming)
  • SQL Pivot in Access
    ... I have a table in access that is laid out like the following: ... I want a query to display results that produces the following: ... averages Sold Price and calls it avg_sold_amt ...
    (microsoft.public.access.queries)
  • Re: access report does not display all records in a query when fil
    ... format it in the query is that I could not format the field in the report. ... I generally find no good reason to format a date within a query. ... as is it will display all records. ...
    (microsoft.public.access.reports)
  • Re: Dynamic Site Methodology
    ... Make all the pages php and query and create pages on the fly as needed ... >but cache the results and display the cached result for all subsequent ... >another reason for keeping the pages at least partially dynamic. ...
    (comp.lang.php)