How can I create a list query?

[Guest]

Hi, I need a query that will allow me to enter a list of names, current
weight and measurements then query the database to sort the list in order of
weight gain or loss compared to each other.

I am able to create a query that will allow me to call up a single name with
current weight but not a list of names with current weights. Can anyone walk
me thru this?

 

[Guest]

There are a bunch of ways but you need to consider how often you will be
doing it and how many names at one time. If one or two times and just a very
few names then just putting the names as criteris in a query is the easiest.

But if you will do it a lost of times forever an a whole lot of names the
build a reference table of names. You might want to include one that is for
all. If using names then have an asterisk or if using an ID number use 0
(zero). Have a Yes/No field to pick name.

Build a select query to feed a subform not linked to the main form. The
subform will be in datasheet view with the names and check boxes for select.

The main form will have an On-Load event to set all of the Yes/No fields of
the reference table to No. It will also have a command button to open you
orginal query or form.

In the data pull query have your main table and the reference table not
joined. Have the reference table as criteria for the Name/ID field like this

 

[Gary Walter]

Hi, I need a query that will allow me to enter a list of names, current
weight and measurements then query the database to sort the list in order
of
weight gain or loss compared to each other.

I am able to create a query that will allow me to call up a single name
with
current weight but not a list of names with current weights. Can anyone
walk
me thru this?

Hi Lost,

The hardest part when getting started is
to provide what someone needs to help
you.

For help with a query, it is almost always
a good idea to provide your table(s) structure
and sample data, then sample results from
the query you need help with.

For example.....

Help! I have the following tables

tblNames
NameID (pk Autonumber)
LastName Text(50)
FirstName Text(50)

tblWeights
WtID (pk Autonumber)
NameID (points back to tblNames)
RecWt Long
RecDate Date/Time

some sample data

NameID LastName FirstName
1 Simpson Homer
2 Simpson Bart
3 Simpson Lisa

WtID NameID RecWt RecDate
1 1 245 6/10/2006
2 2 65 6/10/2006
3 3 45 6/10/2006
4 1 250 6/17/2006
5 2 70 6/17/2006
6 3 46 6/17/2006
7 1 255 6/24/2006
8 2 71 6/24/2006
9 3 45 6/24/2006

what I would like to do is compare
latest weights (recorded on 6/24/2006)
with weights from previous week
(recorded on 6/17/2006)
and sort by WtDiff

FirstName CurWt PrevWt WtDiff
Lisa 45 46 -1
Bart 71 70 1
Homer 255 250 5

So...if the above were your question,
this might be the response:


Click on "Create Query in Design View"

In the Show Table dialog box,
click on "tblNames"
click on Add
click on "tblWeights"
click Add,
click on Add again,
and then click Close.

You should now show 2 copies of "tblWeights"
in the query designer.

Right-mouse click one of these tables
and choose Properties.
In the Alias row, type in
Older
then close the Properties dialog box.

Right-mouse click on the other "tblWeights" table
and choose Properties.
In the Alias row, type in
Newer
then close the Properties dialog box.


Click and hold down on "tblNames"
NameID field
and "drag and drop"
over on a Older table's NameID field.

You should now have a (join) line
connecting the 2 tables going
from tblNames.NameID to Older.NameID

Click and hold down on "tblNames"
NameID field
and "drag and drop"
over on a Nwer table's NameID field.

You should now have a (join) line
connecting the 2 tables going
from tblNames.NameID to Newer.NameID


Drag and Drop FirstName field from
"tblNames" table down in to field row
of first column of grid.

Drag and Drop RecWt field from
Newer table down in to field row
of next column of grid. That column
will look like:

Field: RecWt
Table: Newer
Sort:
Show: <checked>
Criteria:
Or:

give it a field alias by adding
"CurWt:" to start of its Field row, i.e,.

Field: CurWt: RecWt
Table: Newer
Sort:
Show: <checked>
Criteria:
Or:

Do the same with RecWt from
Older table

Field: PrevWt: RecWt
Table: Older
Sort:
Show: <checked>
Criteria:
Or:

use these field aliases to
create a calculated field
in the next column of the grid, i.e.,

Field: WtDiff: [CurWt] - [PrevWt]
Table:
Sort: Ascending
Show: <checked>
Criteria:
Or:


drag and drop RecDate from
Newer table down to Field row
in next open column of grid

uncheck Show

In its Criteria row, type

#6/24/2006#


do same with Older RecDate
but in its Criteria row, type

#6/17/2006#

the 2 columns would look like:


Field: RecDate RecDate
Table: Newer Older
Sort:
Show: <unchkd> <unchked>
Criteria: #6/24/2006# #6/17/2006#
Or:

save your query, and run it.

 

[Guest]

Thank you so much Karl for your valuable reply and explanation it will
resolve a major headache for me. As it will be ongoing knowing people will
drop out of the programme as others will join so the latter part of your
advice will be most effective. Thanks again! Chris (Lost in Query)

Gary, my tbls are almost identical to your presentation. I will remember
your advice for any future questions I might have. Thank you so much for
carefully walking me thru this. I had never considered your approach and it
will be better than I could have hoped. You guys are superb! Thanks so much!

Chris (Lost in Query)

Hi, I need a query that will allow me to enter a list of names, current
weight and measurements then query the database to sort the list in order
of
weight gain or loss compared to each other.

I am able to create a query that will allow me to call up a single name
with
current weight but not a list of names with current weights. Can anyone
walk
me thru this?

Hi Lost,

The hardest part when getting started is
to provide what someone needs to help
you.

For help with a query, it is almost always
a good idea to provide your table(s) structure
and sample data, then sample results from
the query you need help with.

For example.....

Help! I have the following tables

tblNames
NameID (pk Autonumber)
LastName Text(50)
FirstName Text(50)

tblWeights
WtID (pk Autonumber)
NameID (points back to tblNames)
RecWt Long
RecDate Date/Time

some sample data

NameID LastName FirstName
1 Simpson Homer
2 Simpson Bart
3 Simpson Lisa

WtID NameID RecWt RecDate
1 1 245 6/10/2006
2 2 65 6/10/2006
3 3 45 6/10/2006
4 1 250 6/17/2006
5 2 70 6/17/2006
6 3 46 6/17/2006
7 1 255 6/24/2006
8 2 71 6/24/2006
9 3 45 6/24/2006

what I would like to do is compare
latest weights (recorded on 6/24/2006)
with weights from previous week
(recorded on 6/17/2006)
and sort by WtDiff

FirstName CurWt PrevWt WtDiff
Lisa 45 46 -1
Bart 71 70 1
Homer 255 250 5

So...if the above were your question,
this might be the response:


Click on "Create Query in Design View"

In the Show Table dialog box,
click on "tblNames"
click on Add
click on "tblWeights"
click Add,
click on Add again,
and then click Close.

You should now show 2 copies of "tblWeights"
in the query designer.

Right-mouse click one of these tables
and choose Properties.
In the Alias row, type in
Older
then close the Properties dialog box.

Right-mouse click on the other "tblWeights" table
and choose Properties.
In the Alias row, type in
Newer
then close the Properties dialog box.


Click and hold down on "tblNames"
NameID field
and "drag and drop"
over on a Older table's NameID field.

You should now have a (join) line
connecting the 2 tables going
from tblNames.NameID to Older.NameID

Click and hold down on "tblNames"
NameID field
and "drag and drop"
over on a Nwer table's NameID field.

You should now have a (join) line
connecting the 2 tables going
from tblNames.NameID to Newer.NameID


Drag and Drop FirstName field from
"tblNames" table down in to field row
of first column of grid.

Drag and Drop RecWt field from
Newer table down in to field row
of next column of grid. That column
will look like:

Field: RecWt
Table: Newer
Sort:
Show: <checked>
Criteria:
Or:

give it a field alias by adding
"CurWt:" to start of its Field row, i.e,.

Field: CurWt: RecWt
Table: Newer
Sort:
Show: <checked>
Criteria:
Or:

Do the same with RecWt from
Older table

Field: PrevWt: RecWt
Table: Older
Sort:
Show: <checked>
Criteria:
Or:

use these field aliases to
create a calculated field
in the next column of the grid, i.e.,

Field: WtDiff: [CurWt] - [PrevWt]
Table:
Sort: Ascending
Show: <checked>
Criteria:
Or:


drag and drop RecDate from
Newer table down to Field row
in next open column of grid

uncheck Show

In its Criteria row, type

#6/24/2006#


do same with Older RecDate
but in its Criteria row, type

#6/17/2006#

the 2 columns would look like:


Field: RecDate RecDate
Table: Newer Older
Sort:
Show: <unchkd> <unchked>
Criteria: #6/24/2006# #6/17/2006#
Or:

save your query, and run it.