HELP!!!

  • Thread starter Thread starter mac
  • Start date Start date
M

mac

My boss gave me a project and I am not sure how at get the answers he wants.
I have a database that has over 150,000 products and the date the products
changed prices. Item # - Products - Quanity - Price per unit. .
I want to get the price closest to the date the product was
shipped if it changed in 2007, if not the lastest date it changed. Any help
would be greatly appreciated.
 
Hi Mac,

ship date is not one of the fields you specified... in fact, there are
no dates at all -- where would the date information be coming from?


Warm Regards,
Crystal

*
(: have an awesome day :)
*
 
Hi Mac,

here is something you can try for the latest date something was changed:

SELECT [Item #], [Date_Fieldname], [Price per unit]
FROM [Tablename]
WHERE [Date_Fieldname] =
dMax("[Date_Fieldname]", "[Tablename]", "[Item #] = " & [Item #])

If [Item #] is a text field, you will need to delimit it with quotes, so
the criteria would be this...

"[Item #] = '" & [Item #] & "'"

~~~

if you specify more of the fieldnames, and other tables, if any, that
have related information, we can help you get the latest date for a
specific ship date

Warm Regards,
Crystal

*
(: have an awesome day :)
*
 
Dear strive4peace:

I have a similar issue to Mac...
I want to create a query from 2 tables. Table1 has fields "changedate" and
"wage." Records are in descending date order. Table 2 has fields "date of
service" and "hours." I want a query that will calculate table2.hours times
table1.wage according to the wage effective at the "date of service." Make
sense?

Let me know if you need more info. Thanks!
--
Julie


strive4peace said:
Hi Mac,

here is something you can try for the latest date something was changed:

SELECT [Item #], [Date_Fieldname], [Price per unit]
FROM [Tablename]
WHERE [Date_Fieldname] =
dMax("[Date_Fieldname]", "[Tablename]", "[Item #] = " & [Item #])

If [Item #] is a text field, you will need to delimit it with quotes, so
the criteria would be this...

"[Item #] = '" & [Item #] & "'"

~~~

if you specify more of the fieldnames, and other tables, if any, that
have related information, we can help you get the latest date for a
specific ship date

Warm Regards,
Crystal

*
(: have an awesome day :)
*

Hi Mac,

ship date is not one of the fields you specified... in fact, there are
no dates at all -- where would the date information be coming from?


Warm Regards,
Crystal

*
(: have an awesome day :)
*
Click to expand...
Click to expand...
 
Hi Julie,

"according to the wage effective at the "date of service." "

make a query based on Table1. you will use this query to look up a date
range

Name --> qWageDateRange
SELECT ChangeDate as DateStart
, Wage
, nz(dMin("[ChangeDate]"
, "[Table1]"
, "[ChangeDate] >#" & [ChangeDate] & "#"),#1/1/2999#) as DateEnd
FROM [Table1]


Now, you can use this query to get the wage that you want given a date
of service

make a query based on Table2 and qWageDateRange

then, on the grid, to get the right wage record:

field --> Wage
table --> qWageDateRange

field --> DateStart
table --> qWageDateRange
Show --> No
criteria --> <= [date of service]

field --> DateEnd
table --> qWageDateRange
Show --> No
criteria --> >= [date of service]


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
oops ... switched the signs on the criteria ... try this instead

field --> Wage
table --> qWageDateRange

field --> [date of service]
table --> Table1
criteria --> between DateStart and DateEnd


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Hi Julie,

"according to the wage effective at the "date of service." "

make a query based on Table1. you will use this query to look up a date
range

Name --> qWageDateRange
SELECT ChangeDate as DateStart
, Wage
, nz(dMin("[ChangeDate]"
, "[Table1]"
, "[ChangeDate] >#" & [ChangeDate] & "#"),#1/1/2999#) as DateEnd
FROM [Table1]


Now, you can use this query to get the wage that you want given a date
of service

make a query based on Table2 and qWageDateRange

then, on the grid, to get the right wage record:

field --> Wage
table --> qWageDateRange

field --> DateStart
table --> qWageDateRange
Show --> No
criteria --> <= [date of service]

field --> DateEnd
table --> qWageDateRange
Show --> No
criteria --> >= [date of service]


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Dear strive4peace:

I have a similar issue to Mac...
I want to create a query from 2 tables. Table1 has fields
"changedate" and "wage." Records are in descending date order. Table
2 has fields "date of service" and "hours." I want a query that will
calculate table2.hours times table1.wage according to the wage
effective at the "date of service." Make sense?

Let me know if you need more info. Thanks!
Click to expand...
Click to expand...
 
Crystal:

Your solution worked wonderfully! Actually, your previous response worked
(with a minor change); this one did not work for me. I was possibly doing
something wrong. But your first response works great!

Thank you so much! This forum is fantastic!
--
Julie


strive4peace said:
oops ... switched the signs on the criteria ... try this instead

field --> Wage
table --> qWageDateRange

field --> [date of service]
table --> Table1
criteria --> between DateStart and DateEnd


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Hi Julie,

"according to the wage effective at the "date of service." "

make a query based on Table1. you will use this query to look up a date
range

Name --> qWageDateRange
SELECT ChangeDate as DateStart
, Wage
, nz(dMin("[ChangeDate]"
, "[Table1]"
, "[ChangeDate] >#" & [ChangeDate] & "#"),#1/1/2999#) as DateEnd
FROM [Table1]


Now, you can use this query to get the wage that you want given a date
of service

make a query based on Table2 and qWageDateRange

then, on the grid, to get the right wage record:

field --> Wage
table --> qWageDateRange

field --> DateStart
table --> qWageDateRange
Show --> No
criteria --> <= [date of service]

field --> DateEnd
table --> qWageDateRange
Show --> No
criteria --> >= [date of service]


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Dear strive4peace:

I have a similar issue to Mac...
I want to create a query from 2 tables. Table1 has fields
"changedate" and "wage." Records are in descending date order. Table
2 has fields "date of service" and "hours." I want a query that will
calculate table2.hours times table1.wage according to the wage
effective at the "date of service." Make sense?

Let me know if you need more info. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Crystal: I hope you can help me again...

In trying to keep my original post simple, I neglected to include that
"Table1" also has a "Staff ID number" field. How do I get my
"qWageDateRange" query to work based also on the "Staff ID number"? I'm
hoping there is a simple answer, and I'm just having a mental block right now.

Thanks!
 
Back
Top