Need help with report - Access 2007

I'm not a fantastic Access developer, but I'm trying to help get
Inventory and whatnot organized at work. They are used to 5 year
budget plans that list out exactly what has to be purchased what year
and how much it will cost. Currently these are kept in Excel and look
like this

| Inventory information (who, what, where) | 2008 | 2009 | 2010 |
2011| 2012

The cost for the item is displayed in the column in the year when it
will be replaced,and summed, for department, as well as for the year.

I have all of the information on the item stored in an Access 2007
table, with the who what wheres in their own fields, and fields for
replacement year and replacement cost. What has me stumped is how to
format a report so that it looks like the Excel format I tried to show
above, without having to do a whole bunch of manual tweaking anytime
it is run, AND without having to re-export to Excel.

Is this something that I can pull off in Access? Is anyone willing to
give me a shove in the right direction?

Thanks!

Re: Need help with report - Access 2007

On Jan 15, 2:24=A0pm, LadyIlsebet wrote:
> I'm not a fantastic Access developer, but I'm trying to help get
> Inventory and whatnot organized at work. They are used to 5 year
> budget plans that list out exactly what has to be purchased what year
> and how much it will cost. Currently these are kept in Excel and look
> like this
>
> | Inventory information (who, what, where) | 2008 | 2009 | 2010 |
> 2011| 2012
>
> The cost for the item is displayed in the column in the year when it
> will be replaced,and summed, for department, as well as for the year.
>
> I have all of the information on the item stored in an Access 2007
> table, with the who what wheres in their own fields, and fields for
> replacement year and replacement cost. What has me stumped is how to
> format a report so that it looks like the Excel format I tried to show
> above, without having to do a whole bunch of manual tweaking anytime
> it is run, AND without having to re-export to Excel.
>
> Is this something that I can pull off in Access? Is anyone willing to
> give me a shove in the right direction?
>
> Thanks!

Can you give us your table structure(s) please?

Re: Need help with report - Access 2007

On Jan 15, 2:34 pm, frogste...@yahoo.com wrote:
> On Jan 15, 2:24 pm, LadyIlsebet wrote:
>
>
>
> > I'm not a fantastic Access developer, but I'm trying to help get
> > Inventory and whatnot organized at work. They are used to 5 year
> > budget plans that list out exactly what has to be purchased what year
> > and how much it will cost. Currently these are kept in Excel and look
> > like this
>
> > | Inventory information (who, what, where) | 2008 | 2009 | 2010 |
> > 2011| 2012
>
> > The cost for the item is displayed in the column in the year when it
> > will be replaced,and summed, for department, as well as for the year.
>
> > I have all of the information on the item stored in an Access 2007
> > table, with the who what wheres in their own fields, and fields for
> > replacement year and replacement cost. What has me stumped is how to
> > format a report so that it looks like the Excel format I tried to show
> > above, without having to do a whole bunch of manual tweaking anytime
> > it is run, AND without having to re-export to Excel.
>
> > Is this something that I can pull off in Access? Is anyone willing to
> > give me a shove in the right direction?
>
> > Thanks!
>
> Can you give us your table structure(s) please?

Everything I *think* I need for this report is in the one table.
Name Type Size
AutoNumberKey Long Integer 4
Asset Tag Text 10
Inventory Type Text 25
Assigned Text 50
Description Text 255
OS Text 255
Department Text 255
Location Text 255
Serial Number Text 255
Purchase Date Date/Time 8
Purchase Cost Currency 8
Replacement Year Integer 2
Replacement cost Currency 8
Notes Memo -
WindowsUpdateDate Date/Time 8
WindowsUpdateTech Text 255
VirusDefsDate Date/Time 8
VirusDefsTech Text 255
VirusScanVersionDate Date/Time 8
VirusScanVersionTech Text 255

Is any more info needed? The report will not need to include any
fields listed after "Notes"

Thanks!

Re: Need help with report - Access 2007

On Jan 15, 3:30=A0pm, LadyIlsebet wrote:
> On Jan 15, 2:34 pm, frogste...@yahoo.com wrote:
>
>
>
>
>
> > On Jan 15, 2:24 pm, LadyIlsebet wrote:
>
> > > I'm not a fantastic Access developer, but I'm trying to help get
> > > Inventory and whatnot organized at work. They are used to 5 year
> > > budget plans that list out exactly what has to be purchased what year
> > > and how much it will cost. Currently these are kept in Excel and look
> > > like this
>
> > > | Inventory information (who, what, where) | 2008 | 2009 | 2010 |
> > > 2011| 2012
>
> > > The cost for the item is displayed in the column in the year when it
> > > will be replaced,and summed, for department, as well as for the year.
>
> > > I have all of the information on the item stored in an Access 2007
> > > table, with the who what wheres in their own fields, and fields for
> > > replacement year and replacement cost. What has me stumped is how to
> > > format a report so that it looks like the Excel format I tried to show=

> > > above, without having to do a whole bunch of manual tweaking anytime
> > > it is run, AND without having to re-export to Excel.
>
> > > Is this something that I can pull off in Access? Is anyone willing to
> > > give me a shove in the right direction?
>
> > > Thanks!
>
> > Can you give us your table structure(s) please?
>
> Everything I *think* I need for this report is in the one table.
> Name =A0 =A0Type =A0 =A0Size
> =A0 =A0 =A0 =A0 AutoNumberKey =A0 Long Integer =A0 =A04
> =A0 =A0 =A0 =A0 Asset Tag =A0 =A0 =A0 Text =A0 =A010
> =A0 =A0 =A0 =A0 Inventory Type =A0Text =A0 =A025
> =A0 =A0 =A0 =A0 Assigned =A0 =A0 =A0 =A0Text =A0 =A050
> =A0 =A0 =A0 =A0 Description =A0 =A0 Text =A0 =A0255
> =A0 =A0 =A0 =A0 OS =A0 =A0 =A0Text =A0 =A0255
> =A0 =A0 =A0 =A0 Department =A0 =A0 =A0Text =A0 =A0255
> =A0 =A0 =A0 =A0 Location =A0 =A0 =A0 =A0Text =A0 =A0255
> =A0 =A0 =A0 =A0 Serial Number =A0 Text =A0 =A0255
> =A0 =A0 =A0 =A0 Purchase Date =A0 Date/Time =A0 =A0 =A0 8
> =A0 =A0 =A0 =A0 Purchase Cost =A0 Currency =A0 =A0 =A0 =A08
> =A0 =A0 =A0 =A0 Replacement Year =A0 =A0 =A0 =A0Integer 2
> =A0 =A0 =A0 =A0 Replacement cost =A0 =A0 =A0 =A0Currency =A0 =A0 =A0 =A08
> =A0 =A0 =A0 =A0 Notes =A0 Memo =A0 =A0-
> =A0 =A0 =A0 =A0 WindowsUpdateDate =A0 =A0 =A0 Date/Time =A0 =A0 =A0 8
> =A0 =A0 =A0 =A0 WindowsUpdateTech =A0 =A0 =A0 Text =A0 =A0255
> =A0 =A0 =A0 =A0 VirusDefsDate =A0 Date/Time =A0 =A0 =A0 8
> =A0 =A0 =A0 =A0 VirusDefsTech =A0 Text =A0 =A0255
> =A0 =A0 =A0 =A0 VirusScanVersionDate =A0 =A0Date/Time =A0 =A0 =A0 8
> =A0 =A0 =A0 =A0 VirusScanVersionTech =A0 =A0Text =A0 =A0255
>
> Is any more info needed? The report will not need to include any
> fields listed after "Notes"
>
> Thanks!- Hide quoted text -
>
> - Show quoted text -

1st Get rid of all the spaces in your field names. That is a biog no-
no is database design. It makes a lot of things (queries, code, etc)
a LOT mor complicated.

2nd Make your ReplacementYear length 4 and use the full year. (Again
it will clean things up in the query, report, etc.)

3rd As is often the case there are many ways to skin this cat. You
could build a cross tab query on The AutoNumberKey - ReplacementYear -
ReplacementCost. This will give you a 'grid' of ID - 2008 - 2009 -
2010 - etc with the cost for each.

4th Build a query on your table and query built above to create all
the other data you want in your report or excel extract then crete
whatever report(s) extract(s) you need.

Im sure there are other ways but w/o having your db in front of me
thats what I can come up with for now.

Re: Need help with report - Access 2007

On Jan 15, 2:24=A0pm, LadyIlsebet wrote:
> I'm not a fantastic Access developer, but I'm trying to help get
> Inventory and whatnot organized at work. They are used to 5 year
> budget plans that list out exactly what has to be purchased what year
> and how much it will cost. Currently these are kept in Excel and look
> like this
>
> | Inventory information (who, what, where) | 2008 | 2009 | 2010 |
> 2011| 2012
>
> The cost for the item is displayed in the column in the year when it
> will be replaced,and summed, for department, as well as for the year.
>
> I have all of the information on the item stored in an Access 2007
> table, with the who what wheres in their own fields, and fields for
> replacement year and replacement cost. What has me stumped is how to
> format a report so that it looks like the Excel format I tried to show
> above, without having to do a whole bunch of manual tweaking anytime
> it is run, AND without having to re-export to Excel.
>
> Is this something that I can pull off in Access? Is anyone willing to
> give me a shove in the right direction?
>
> Thanks!

LadyIlsebet,

I'm almost certain we've met before, many years ago, while I was a
student at Oakland U., possibly at an event near Grand Blanc to which
a friend named Anne invited me. Let me know if there's anything I can
help you with. I don't have a lot of extra time, but I know which
side of the mouse to hold, so to speak, when it comes to Access.

James A. Fortune
CDMAPoster@FortuneJames.com

Re: Need help with report - Access 2007

Thanks for the help! I have been able to create the basic report I
needed, and learned ALOT about crosstab queries in the process.