A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

need help with query or report



 
 
Thread Tools Display Modes
  #11  
Old January 19th, 2010, 05:10 PM posted to microsoft.public.access.tablesdbdesign
kaw via AccessMonster.com
external usenet poster
 
Posts: 10
Default need help with query or report

The only thing I can think of is maybe go by date. That is the only thing
that I can think of that is the same in all the data.

kaw wrote:
I think that maybe correct. I am trying to use Access as an inventory system
but my files only move once a year. Do you have any suggestions that may work?
I am trying to quit using RedBeam
Check In/ Check Out because it gets quite full and quits working then I have
to pay them to update the database. The last time it took 9 months before
they got it right. I cannot operate like that it gets me behind.

Kaw,

[quoted text clipped - 15 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1

  #12  
Old January 19th, 2010, 05:13 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default need help with query or report

Kaw,

It will require some table modifications... I will work on those and post
back with a Data Model.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"kaw via AccessMonster.com" u57493@uwe wrote in message
news:a259b3c29159c@uwe...
I think that maybe correct. I am trying to use Access as an inventory
system
but my files only move once a year. Do you have any suggestions that may
work?
I am trying to quit using RedBeam
Check In/ Check Out because it gets quite full and quits working then I
have
to pay them to update the database. The last time it took 9 months before
they got it right. I cannot operate like that it gets me behind.

Gina Whipp wrote:
Kaw,

I can see an issue right off the bat... You based your table design on
what
goes in the box, a flat file like doing it in Excel, instead of a
relational
design with normalized data. That is why creating reports is difficult if
not almost impossible because these tables can't *talk* to each other.
Unless I am misunderstanding something...

It looks like to me all these items (your different tables) are in boxes
together and you want a report that says what's in the box? Yes, you can
create a report for table tblLockBox but there is nothing in the other
tables to help you know which Box those are in.... Again, unless I am
misunderstanding something...

Okay here are all four tables Every thing is test box except amount
that
is

[quoted text clipped - 66 lines]
to
print out what is in each box.


--
Message posted via http://www.accessmonster.com



  #13  
Old January 19th, 2010, 05:18 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default need help with query or report

Kaw,

Not by date... sounds like it should be by Taxpayer, then by Year. See if
this sounds right...

I bring in all my papers for you to do my taxes. You know scan, file,
etc... my papers asssociated with my taxes for the year and store in a
file/box. That box gets a FileID and I get my taxes filed.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"kaw via AccessMonster.com" u57493@uwe wrote in message
news:a259c129bb2a2@uwe...
The only thing I can think of is maybe go by date. That is the only thing
that I can think of that is the same in all the data.

kaw wrote:
I think that maybe correct. I am trying to use Access as an inventory
system
but my files only move once a year. Do you have any suggestions that may
work?
I am trying to quit using RedBeam
Check In/ Check Out because it gets quite full and quits working then I
have
to pay them to update the database. The last time it took 9 months before
they got it right. I cannot operate like that it gets me behind.

Kaw,

[quoted text clipped - 15 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1



  #14  
Old January 19th, 2010, 05:31 PM posted to microsoft.public.access.tablesdbdesign
kaw via AccessMonster.com
external usenet poster
 
Posts: 10
Default need help with query or report

I think it would work better by year then. The only files that I receive that
has one taxpayer per file is the audit files. In scanned and lockbox I can
have up to 40 taxpayers in one batch.
Gina Whipp wrote:
Kaw,

Not by date... sounds like it should be by Taxpayer, then by Year. See if
this sounds right...

I bring in all my papers for you to do my taxes. You know scan, file,
etc... my papers asssociated with my taxes for the year and store in a
file/box. That box gets a FileID and I get my taxes filed.

The only thing I can think of is maybe go by date. That is the only thing
that I can think of that is the same in all the data.

[quoted text clipped - 14 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1

  #15  
Old January 19th, 2010, 07:42 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default need help with query or report

Kaw,

But you can have several taxpayers in one box. The contents are seperated
by taxpayers correct?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"kaw via AccessMonster.com" u57493@uwe wrote in message
news:a259f1dbf92cb@uwe...
I think it would work better by year then. The only files that I receive
that
has one taxpayer per file is the audit files. In scanned and lockbox I can
have up to 40 taxpayers in one batch.
Gina Whipp wrote:
Kaw,

Not by date... sounds like it should be by Taxpayer, then by Year. See if
this sounds right...

I bring in all my papers for you to do my taxes. You know scan, file,
etc... my papers asssociated with my taxes for the year and store in a
file/box. That box gets a FileID and I get my taxes filed.

The only thing I can think of is maybe go by date. That is the only
thing
that I can think of that is the same in all the data.

[quoted text clipped - 14 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1



  #16  
Old January 19th, 2010, 09:01 PM posted to microsoft.public.access.tablesdbdesign
kaw via AccessMonster.com
external usenet poster
 
Posts: 10
Default need help with query or report

Let me try to explain more in depth. For example my first lockbox has 100
bundles. Each bundle of papers can have up to 30 taxpayer paperwork. So I
could have up to 3000taxpayers in one box. I divided those bundles into 6
packets. It can be the same for scanned files and manual files. I keep a list
of bundles,since they are numbered, in Excel then exported to RedBeam.
Sometimes a bundle does not go into our system so I have to locate the bundle
and they are reprocessed by rescanning the file. I also need to keep track of
the old and new numbers. When the boxes go to record storage that department
wants a list of what is in each box. We have to keep the boxes for 5 years
then they are shredded per IRS rules.
The Auditors keep their paperwork on a taxpayer in a folder and when the case
is closed I am given the files. I do seperate the files by auditor but one
auditor has filled 11 boxes. Sometimes they will ask for a file and I have a
list in my computer (Excell) to locate the file they need.

Hope this helps it can get complicated very quickly.

Gina Whipp wrote:
Kaw,

But you can have several taxpayers in one box. The contents are seperated
by taxpayers correct?

I think it would work better by year then. The only files that I receive
that

[quoted text clipped - 15 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1

  #17  
Old January 19th, 2010, 11:55 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default need help with query or report

Kaw,

Yes, I believe that helped. I'll be working on a initial table layout and
then we can tweak from there.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"kaw via AccessMonster.com" u57493@uwe wrote in message
news:a25bc6ff0677e@uwe...
Let me try to explain more in depth. For example my first lockbox has 100
bundles. Each bundle of papers can have up to 30 taxpayer paperwork. So I
could have up to 3000taxpayers in one box. I divided those bundles into 6
packets. It can be the same for scanned files and manual files. I keep a
list
of bundles,since they are numbered, in Excel then exported to RedBeam.
Sometimes a bundle does not go into our system so I have to locate the
bundle
and they are reprocessed by rescanning the file. I also need to keep track
of
the old and new numbers. When the boxes go to record storage that
department
wants a list of what is in each box. We have to keep the boxes for 5 years
then they are shredded per IRS rules.
The Auditors keep their paperwork on a taxpayer in a folder and when the
case
is closed I am given the files. I do seperate the files by auditor but one
auditor has filled 11 boxes. Sometimes they will ask for a file and I have
a
list in my computer (Excell) to locate the file they need.

Hope this helps it can get complicated very quickly.

Gina Whipp wrote:
Kaw,

But you can have several taxpayers in one box. The contents are seperated
by taxpayers correct?

I think it would work better by year then. The only files that I receive
that

[quoted text clipped - 15 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1



  #18  
Old January 20th, 2010, 12:22 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default need help with query or report

Kaw,

Preliminary table layout...

tblLockBox

lbLockBoxID (PK - Text)

lbPacketID

lbDepositDate (Month and Year can be gleamed from here)

lbForm

lbFEINID

lbCheck (Yes/No field (Yes - Check - No = No Check)

lbCorrespondence

lbAmount

lbBatchedByID

ldBatchSource

lbRebatchNumber

lbEmployeeCheckout

lbNotes

lbAmount





tblLockBoxDetails

lbdFileID

lbdFEINID

lbdCompanyID (FK)

lbdInDate

lbdDepositDate

lbdFileTypeID (FK - Scan, Manual, Case, etc. See table below)

lbdAuditorID (FK)

lbdQuantity



tblFileTypes

ftFileTypeID (PK - Autonumber)

ftFileType



tblCompanies

cCompanyID (PK - Autonumber)

cCompanyName

etc.



tblAuditors

aAuditorID (PK - Autonumber)

aFirstName

aLastName

etc.


--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"kaw via AccessMonster.com" u57493@uwe wrote in message
news:a25bc6ff0677e@uwe...
Let me try to explain more in depth. For example my first lockbox has 100
bundles. Each bundle of papers can have up to 30 taxpayer paperwork. So I
could have up to 3000taxpayers in one box. I divided those bundles into 6
packets. It can be the same for scanned files and manual files. I keep a
list
of bundles,since they are numbered, in Excel then exported to RedBeam.
Sometimes a bundle does not go into our system so I have to locate the
bundle
and they are reprocessed by rescanning the file. I also need to keep track
of
the old and new numbers. When the boxes go to record storage that
department
wants a list of what is in each box. We have to keep the boxes for 5 years
then they are shredded per IRS rules.
The Auditors keep their paperwork on a taxpayer in a folder and when the
case
is closed I am given the files. I do seperate the files by auditor but one
auditor has filled 11 boxes. Sometimes they will ask for a file and I have
a
list in my computer (Excell) to locate the file they need.

Hope this helps it can get complicated very quickly.

Gina Whipp wrote:
Kaw,

But you can have several taxpayers in one box. The contents are seperated
by taxpayers correct?

I think it would work better by year then. The only files that I receive
that

[quoted text clipped - 15 lines]
to
print out what is in each box.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1



  #19  
Old January 20th, 2010, 03:58 PM posted to microsoft.public.access.tablesdbdesign
kaw via AccessMonster.com
external usenet poster
 
Posts: 10
Default need help with query or report

Thank you for your help. I am creating the table as you have said and trying
it out.

Gina Whipp wrote:
Kaw,

Preliminary table layout...

tblLockBox

lbLockBoxID (PK - Text)

lbPacketID

lbDepositDate (Month and Year can be gleamed from here)

lbForm

lbFEINID

lbCheck (Yes/No field (Yes - Check - No = No Check)

lbCorrespondence

lbAmount

lbBatchedByID

ldBatchSource

lbRebatchNumber

lbEmployeeCheckout

lbNotes

lbAmount

tblLockBoxDetails

lbdFileID

lbdFEINID

lbdCompanyID (FK)

lbdInDate

lbdDepositDate

lbdFileTypeID (FK - Scan, Manual, Case, etc. See table below)

lbdAuditorID (FK)

lbdQuantity

tblFileTypes

ftFileTypeID (PK - Autonumber)

ftFileType

tblCompanies

cCompanyID (PK - Autonumber)

cCompanyName

etc.

tblAuditors

aAuditorID (PK - Autonumber)

aFirstName

aLastName

etc.

Let me try to explain more in depth. For example my first lockbox has 100
bundles. Each bundle of papers can have up to 30 taxpayer paperwork. So I

[quoted text clipped - 29 lines]
to
print out what is in each box.


--
Message posted via http://www.accessmonster.com

  #20  
Old January 20th, 2010, 06:43 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default need help with query or report

Kaw,

We will be here should you need more help!

You're welcome!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"kaw via AccessMonster.com" u57493@uwe wrote in message
news:a265b4c5476c2@uwe...
Thank you for your help. I am creating the table as you have said and
trying
it out.

Gina Whipp wrote:
Kaw,

Preliminary table layout...

tblLockBox

lbLockBoxID (PK - Text)

lbPacketID

lbDepositDate (Month and Year can be gleamed from here)

lbForm

lbFEINID

lbCheck (Yes/No field (Yes - Check - No = No Check)

lbCorrespondence

lbAmount

lbBatchedByID

ldBatchSource

lbRebatchNumber

lbEmployeeCheckout

lbNotes

lbAmount

tblLockBoxDetails

lbdFileID

lbdFEINID

lbdCompanyID (FK)

lbdInDate

lbdDepositDate

lbdFileTypeID (FK - Scan, Manual, Case, etc. See table below)

lbdAuditorID (FK)

lbdQuantity

tblFileTypes

ftFileTypeID (PK - Autonumber)

ftFileType

tblCompanies

cCompanyID (PK - Autonumber)

cCompanyName

etc.

tblAuditors

aAuditorID (PK - Autonumber)

aFirstName

aLastName

etc.

Let me try to explain more in depth. For example my first lockbox has
100
bundles. Each bundle of papers can have up to 30 taxpayer paperwork. So
I

[quoted text clipped - 29 lines]
to
print out what is in each box.


--
Message posted via http://www.accessmonster.com



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.