Parameter with Dates Help!!

  • Thread starter Thread starter Jules
  • Start date Start date
J

Jules

My problem is I have a table with different software
titles,versions and dates purchased.My problem is all the
different versions, dates purchased at different times. I
need to make a report that will let the user enter
between dates to see how much money was spent during
certain times for what.


Thanks a bunch in advance :- )

Jules
 
My problem is I have a table with different software
titles,versions and dates purchased.My problem is all the
different versions, dates purchased at different times. I
need to make a report that will let the user enter
between dates to see how much money was spent during
certain times for what.
Click to expand...

Use a Query with a criterion on the datefield of

BETWEEN [Enter start date:] AND [Enter end date:]
 
But, I have 20 different dates and I don't want to enter
the parameter 20 different times. I was hoping for one
date field I tried what you suggested and I got all data
not just what I had ask for. I hope you understand what
i'm asking. :- )))

Thanks a Bunch in Advance :- )
-----Original Message-----
My problem is I have a table with different software
titles,versions and dates purchased.My problem is all the
different versions, dates purchased at different times. I
need to make a report that will let the user enter
between dates to see how much money was spent during
certain times for what.
Click to expand...

Use a Query with a criterion on the datefield of

BETWEEN [Enter start date:] AND [Enter end date:]



.
Click to expand...
 
It sounds like you have very un-normalized data. What are the fields in your
table? If they are un-normalized, what are the chances that you can
normalize them?

--
Duane Hookom
Microsoft Access MVP


But, I have 20 different dates and I don't want to enter
the parameter 20 different times. I was hoping for one
date field I tried what you suggested and I got all data
not just what I had ask for. I hope you understand what
i'm asking. :- )))

Thanks a Bunch in Advance :- )
-----Original Message-----
My problem is I have a table with different software
titles,versions and dates purchased.My problem is all the
different versions, dates purchased at different times. I
need to make a report that will let the user enter
between dates to see how much money was spent during
certain times for what.
Click to expand...

Use a Query with a criterion on the datefield of

BETWEEN [Enter start date:] AND [Enter end date:]



.
Click to expand...
Click to expand...
 
But, I have 20 different dates and I don't want to enter
the parameter 20 different times. I was hoping for one
date field I tried what you suggested and I got all data
not just what I had ask for. I hope you understand what
i'm asking. :- )))
Click to expand...

No, I don't. If your table has 20 date fields that you want to search,
a) it's incorrectly normalized and b) you'll need to search all of
them anyway.

Could you post a description of your table, and the SQL of the query?
 
My fields are PC_AdobeAcrobatVersion,
PC_AdobeAcrobatDatePurchased,
PC_AdobeAcrobatAmountPurchased,
PC_AdobeAcrobatAmountCost, PC_AdobeAcrobatType,
PC_AdobeAcrobatComment. For all 20 software purchased we
have so far. The reason I have all the information
together is They want to know how much money was spent
between what dates and version of what software and where
it is located, how many has been loaded and where. They
want all the information in One Report. I really can't
split the tables up because of time constrants I have a
deadline. Please Help. Thanks a Bunch. : - )




-----Original Message-----
It sounds like you have very un-normalized data. What are the fields in your
table? If they are un-normalized, what are the chances that you can
normalize them?

--
Duane Hookom
Microsoft Access MVP


But, I have 20 different dates and I don't want to enter
the parameter 20 different times. I was hoping for one
date field I tried what you suggested and I got all data
not just what I had ask for. I hope you understand what
i'm asking. :- )))

Thanks a Bunch in Advance :- )
-----Original Message-----
On Thu, 23 Oct 2003 13:12:53 -0700, "Jules"

My problem is I have a table with different software
titles,versions and dates purchased.My problem is all the
different versions, dates purchased at different
Click to expand...
times.
I
need to make a report that will let the user enter
between dates to see how much money was spent during
certain times for what.

Use a Query with a criterion on the datefield of

BETWEEN [Enter start date:] AND [Enter end date:]


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps? loc=us&access=public
.
Click to expand...
Click to expand...


.
Click to expand...
 
My fields are PC_AdobeAcrobatVersion,
PC_AdobeAcrobatDatePurchased,
PC_AdobeAcrobatAmountPurchased,
PC_AdobeAcrobatAmountCost, PC_AdobeAcrobatType,
PC_AdobeAcrobatComment. For all 20 software purchased we
have so far. The reason I have all the information
together is They want to know how much money was spent
between what dates and version of what software and where
it is located, how many has been loaded and where. They
want all the information in One Report. I really can't
split the tables up because of time constrants I have a
deadline. Please Help. Thanks a Bunch. : - )
Click to expand...

There's an old Ozark saying: "If you cain't take time to do it right,
you'll have to take the time to fix it up".

Storing data in fieldnames IS SIMPLY NOT GOING TO WORK. It can be made
to work, sort of, with a great deal of effort; but you would be MUCH
better off to take the time to split your data into three tables: your
current table (less all the software specific fields); a 20-row table
of programs; and a table

SoftwareInstalled
Program << link to the Programs table
Version
DatePurchased
AmountPurchased
Cost
Type
Comment

You can use a "Normalizing Union Query" to migrate the data from your
current spreadsheet (I can't in good conscience call it a table):

SELECT "Adobe Acrobat" AS Program, PC_AdobeAcrobatVersion AS Version,
PC_AdobeAcrobatDatePurchased AS DatePurchased,
PC_AdobeAcrobatAmountPurchased AS AmountPurchased,
PC_AdobeAcrobatAmountCost AS Cost, PC_AdobeAcrobatType AS Type,
PC_AdobeAcrobatComment AS Comment
WHERE PC_AdobeAcrobatAmountPurchased IS NOT NULL
UNION
SELECT "Microsoft Office" AS Program, PC_OfficeVersion AS Version,
PC_OfficeDatePurchased AS DatePurchased,
<etc. etc.>

Your Report can be based on a Crosstab query and/or a Totals query.

You can also spend twice as much time and effort getting your current
non-normalized table structure to work. Take your pick.
 
The reason why I can't do the tables like you suggested
is because I also have a field Called PC_TagNo and I have
been Ordered to NO duplicates of the TagNo however doing
it the way you suggested the TagNo would have more than
one software title on it. Any other suggestions? I
appreciate all the help :-)
-----Original Message-----


There's an old Ozark saying: "If you cain't take time to do it right,
you'll have to take the time to fix it up".

Storing data in fieldnames IS SIMPLY NOT GOING TO WORK. It can be made
to work, sort of, with a great deal of effort; but you would be MUCH
better off to take the time to split your data into three tables: your
current table (less all the software specific fields); a 20-row table
of programs; and a table

SoftwareInstalled
Program << link to the Programs table
Version
DatePurchased
AmountPurchased
Cost
Type
Comment

You can use a "Normalizing Union Query" to migrate the data from your
current spreadsheet (I can't in good conscience call it a table):

SELECT "Adobe Acrobat" AS Program,
Click to expand...
PC_AdobeAcrobatVersion AS Version,
 
The reason why I can't do the tables like you suggested
is because I also have a field Called PC_TagNo and I have
been Ordered to NO duplicates of the TagNo however doing
it the way you suggested the TagNo would have more than
one software title on it.
Click to expand...

Again:

YOU NEED TWO TABLES.

You're using a relational database; use it relationally!

You will have one table in which each record corresponds to a PC. This
table will have PC_TagNo as its Primary Key, no duplicates.

You will have a *DIFFERENT TABLE*, related one-to-many to this table,
using PC_TabNo as a Foreign Key. This table will have multiple records
for each PC, one record for each installed program.

To enter data into these tables you will probably want a Form based on
the first table, with a second form based on the "software installed"
table.
 
Back
Top