Query Problem!

[Bob V]

How would I query this
ChargeDate,ChargeItem,ChargeAmount
[ChargeDate] >Date()-365
[ChargeItem,ChargeAmount] No Duplicates , 2 fields together No Duplicate

Thanks if you can Help

 

[Steven Chicago,Illinois]

Dear Bob V,

I am guessing that you have a Table that contains:

ChargeDate,ChargeItem,ChargeAmount

1. So the fields in my query would be those three fields (at least those).

2. In the criteria for [Charge Date] you would have >(Date()-365)

3. You would also have another field in the query that is the combination
of the two fields Expression1: [ChargeItem] + "x" + [ChargeAmount]
First, I am guessing this is what you want. Second I am not sure how your
fields are defined Text and Numeric. Third I am not sure how to combine a
Text and numeric field into a 3rd text field, but I bet a MVP would know it,
and I would love to see the answer.
4. How to do No Duplicates? Again I ask a MVP to tell us if we could
eliminate the Duplicates in this query or would we need to do a second query
on the results from the first query?

Does that break out the things you are trying to accomplish?

Good Luck,
Steve

 

[John W. Vinson]

How would I query this
ChargeDate,ChargeItem,ChargeAmount
[ChargeDate] >Date()-365
[ChargeItem,ChargeAmount] No Duplicates , 2 fields together No Duplicate

SELECT DISTINCT ChargeItem, ChargeAmount
FROM [someunspecifiedtablename]
WHERE [ChargeDate] > DateAdd("yyyy", -1, Date())

If you want to display the charge date... and you don't want duplicates for
the ChargeItem and ChargeAmount... what do you want to see if a Left Handed
Monkey Wrench was sold for $29.95 on 4/1 and on 5/13? Do you want to (a) not
see the duplicate, (b) see the two records, one for each date or (c) see the
item and amount without the date (that's what my query will do)?

John W. Vinson [MVP]

 

[Bob V]

Thanks John , The dates are only there so I can go back 1 year the actual
date shown does not matter.
Left Handed Monkey Wrench $29.95
Left Handed Monkey Wrench $39.95
Left Handed Monkey Wrench $49.95

But not
Left Handed Monkey Wrench $29.95
Left Handed Monkey Wrench $29.95****
Left Handed Monkey Wrench $39.95
Left Handed Monkey Wrench $49.95
Thanks for you effort....Bob

How would I query this
ChargeDate,ChargeItem,ChargeAmount
[ChargeDate] >Date()-365
[ChargeItem,ChargeAmount] No Duplicates , 2 fields together No Duplicate

SELECT DISTINCT ChargeItem, ChargeAmount
FROM [someunspecifiedtablename]
WHERE [ChargeDate] > DateAdd("yyyy", -1, Date())

If you want to display the charge date... and you don't want duplicates
for
the ChargeItem and ChargeAmount... what do you want to see if a Left
Handed
Monkey Wrench was sold for $29.95 on 4/1 and on 5/13? Do you want to (a)
not
see the duplicate, (b) see the two records, one for each date or (c) see
the
item and amount without the date (that's what my query will do)?

John W. Vinson [MVP]

 

[Bob V]

Brilliant John, worked fantastic) and I have added my searches to them.
Now is there a way a drop down Combo drop down list can open another Combo
Drop down list, So if I have 7 Combo lists , just one could control which
ones I open, Thanks for all your help.....Bob

SELECT DISTINCT tblAdditionCharge.AdditionCharge,
tblAdditionCharge.AdditionChargeAmount
FROM tblAdditionCharge
WHERE (((tblAdditionCharge.AdditionCharge) Like "*Vet*") AND
((tblAdditionCharge.DayNo)>DateAdd("yyyy",-2,Date())));

Thanks John , The dates are only there so I can go back 1 year the actual
date shown does not matter.
Left Handed Monkey Wrench $29.95
Left Handed Monkey Wrench $39.95
Left Handed Monkey Wrench $49.95

But not
Left Handed Monkey Wrench $29.95
Left Handed Monkey Wrench $29.95****
Left Handed Monkey Wrench $39.95
Left Handed Monkey Wrench $49.95
Thanks for you effort....Bob

How would I query this
ChargeDate,ChargeItem,ChargeAmount
[ChargeDate] >Date()-365
[ChargeItem,ChargeAmount] No Duplicates , 2 fields together No Duplicate

SELECT DISTINCT ChargeItem, ChargeAmount
FROM [someunspecifiedtablename]
WHERE [ChargeDate] > DateAdd("yyyy", -1, Date())

If you want to display the charge date... and you don't want duplicates
for
the ChargeItem and ChargeAmount... what do you want to see if a Left
Handed
Monkey Wrench was sold for $29.95 on 4/1 and on 5/13? Do you want to (a)
not
see the duplicate, (b) see the two records, one for each date or (c) see
the
item and amount without the date (that's what my query will do)?

John W. Vinson [MVP]

 

[John W. Vinson]

Brilliant John, worked fantastic) and I have added my searches to them.
Now is there a way a drop down Combo drop down list can open another Combo
Drop down list, So if I have 7 Combo lists , just one could control which
ones I open, Thanks for all your help.....Bob

Now that you'll need to explain. You can use the AfterUpdate event of one
combo box to change another combo box's Rowsource (which records will be
shown), Visible property (whether you can even see it), or Enabled property
(whether the user can make a selection from it). I'm not at all sure why you
have so many combos (sounds confusing for the user especially if some of them
don't work!)


John W. Vinson [MVP]

 

[Bob V]

Thanks John, Each Combo Box shoes a query that has a search e.g.: Like
"*Veterinary*", Like "*Transport*" and some Date Searches like you just
showed me.
The Perfect Solution would be a table that holds Names Like Veterinary and
Transport , so you would select them from a dropdown list and another drop
down list would show them!. Thanks for all your Help........Bob

 

[John W. Vinson]

Thanks John, Each Combo Box shoes a query that has a search e.g.: Like
"*Veterinary*", Like "*Transport*" and some Date Searches like you just
showed me.

I'm sorry. This isn't communicating. A combo box doesn't "show" (much less
shoe!) a query.

What is the RowSource of the query?

The Perfect Solution would be a table that holds Names Like Veterinary and
Transport , so you would select them from a dropdown list and another drop
down list would show them!. Thanks for all your Help........Bob

Well? Why not? Create the table. Create the combo box.


John W. Vinson [MVP]

 

[Bob V]

Thanks John, I have made a Continuous form from my query that shows all
Charges in the last 2 years, tbAdditionCharge and tbAdditionChargeAmount, I
then created another query from AdditionCharge and made it Unique Records
Value. I then created a combo box above my continuous form, (should it be in
the header?) with the new query I created showing just AdditionCharge(Unique
Records Value). What do I need in AfterUpdate to show all records of what I
select in my new Combo Box...............Hope this makes
sense................Thanks Bob

 

[John W. Vinson]

What do I need in AfterUpdate to show all records of what I
select in my new Combo Box...............Hope this makes
sense................Thanks Bob

One way is to set the Filter property of the continuous form in the
AfterUpdate event of the combo. Something like:

Me.Filter = "[AdditionCharge] = '" & Me.comboboxname & "'"
Me.FilterOn = True

so that the filter string ends up like

[AddtionCharge] = 'Veterinary'


John W. Vinson [MVP]

 

[Bob V]

Thanks John , Brilliant

What do I need in AfterUpdate to show all records of what I
select in my new Combo Box...............Hope this makes
sense................Thanks Bob

One way is to set the Filter property of the continuous form in the
AfterUpdate event of the combo. Something like:

Me.Filter = "[AdditionCharge] = '" & Me.comboboxname & "'"
Me.FilterOn = True

so that the filter string ends up like

[AddtionCharge] = 'Veterinary'


John W. Vinson [MVP]