need help with query or report

[kaw]

I am needing to print out an inventory list that is in a box. I keep track of
paperwork by using numbers from various sources (not able to change the
sources) but each source has a number given to it. I keep each source
serapate from each other in didferent boxes. I need to print out an inventory
list that is in each box. I am just beginning using Access 2007. I have all 4
tables with data from each source ie. scanned forms, audit files and need to
print out what is in each box.

 

[Gina Whipp]

Kaw,

I can't see how you determine what's in a box. A wee bit more information
will be needed... Tables and how they are related to each other. How does
the database know which box they are in? Is there a field that indicates
that?

--
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]

The box is paperwork from taxpayers. For example, we scan forms in house, as
we scan, we give it a number that is the number that I use. I bundle all the
papers together that is processed in one day.
My scanned form table has columns: Batch number, scanned date, deposit date,
form type, number of items, batch source, batched by, amount, rebatched
number, employee check out.
I have not set up any relationships for each table.
I would like to create either a form or query that is either by box number
and or dates. Hope this helps.

Kaw,

I can't see how you determine what's in a box. A wee bit more information
will be needed... Tables and how they are related to each other. How does
the database know which box they are in? Is there a field that indicates
that?

I am needing to print out an inventory list that is in a box. I keep track
of

[quoted text clipped - 7 lines]

to
print out what is in each box.

 

[Gina Whipp]

Kaw,

Thank you for telling what you do and how ou do it. I also see you posted
some table information in your reply to Karl. However, still.. a wee bit
more informtion is needed... Each table with their prospective fields is
needed so we can provide you with a solution and make sure your tables are
set properly to give you the results you desire.

--
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

The box is paperwork from taxpayers. For example, we scan forms in house,
as
we scan, we give it a number that is the number that I use. I bundle all
the
papers together that is processed in one day.
My scanned form table has columns: Batch number, scanned date, deposit
date,
form type, number of items, batch source, batched by, amount, rebatched
number, employee check out.
I have not set up any relationships for each table.
I would like to create either a form or query that is either by box number
and or dates. Hope this helps.

Kaw,

I can't see how you determine what's in a box. A wee bit more information
will be needed... Tables and how they are related to each other. How
does
the database know which box they are in? Is there a field that indicates
that?

I am needing to print out an inventory list that is in a box. I keep
track
of

[quoted text clipped - 7 lines]

to
print out what is in each box.

 

[kaw via AccessMonster.com]

would it help if I emailed screen prints? I am a newbie to Access so not sure
what you need.

Kaw,

Thank you for telling what you do and how ou do it. I also see you posted
some table information in your reply to Karl. However, still.. a wee bit
more informtion is needed... Each table with their prospective fields is
needed so we can provide you with a solution and make sure your tables are
set properly to give you the results you desire.

The box is paperwork from taxpayers. For example, we scan forms in house,
as

[quoted text clipped - 23 lines]

 

[Gina Whipp]

Kaw,

No screen shots won't help... Here's what I am asking for

tblNameOfTable
NameOfField
NameOfField

tblNameOfTable
NameOfField
NameOfField

Do that for all four tables. Indicate if any of the fields are Primary
Keys.

--
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

would it help if I emailed screen prints? I am a newbie to Access so not
sure
what you need.

Kaw,

Thank you for telling what you do and how ou do it. I also see you posted
some table information in your reply to Karl. However, still.. a wee bit
more informtion is needed... Each table with their prospective fields is
needed so we can provide you with a solution and make sure your tables are
set properly to give you the results you desire.

The box is paperwork from taxpayers. For example, we scan forms in
house,
as

[quoted text clipped - 23 lines]

to
print out what is in each box.

 

[kaw via AccessMonster.com]

Okay here are all four tables Every thing is test box except amount that is
currency
tbllockbox (Name of Table)
lockbox number (Primary Key)
Packet number
Deposit date
Form
Check
No Check
Correspondence
Amount
Rebatch Number
Box Number
Employee Check out
(need to print inventory by box number)

tblscanfile (Name of Table)
Batch number (Primary key)
In Date
Deposit Date
Form Type
Number of Items
Batch Source
Batched by
Amount
Rebatched
Notes
Employee check out
(need to print monthly for book)

tblmanualfile (Name of Table)
File number (Primary Key)
Amount
Deposit Date
Form
Employee
Rebatch number
Employee check out

tblcasefile (Name of Table)
Folder Number (Primary key)
Yr Received (use month and year only)
FEIN number
Company name
Auditor name
Box (box with auditor name and number ( 1-10 ))
Employee checkout
(need inventory of what is in box by auditor)

Kaw,

No screen shots won't help... Here's what I am asking for

tblNameOfTable
NameOfField
NameOfField

tblNameOfTable
NameOfField
NameOfField

Do that for all four tables. Indicate if any of the fields are Primary
Keys.

would it help if I emailed screen prints? I am a newbie to Access so not
sure

[quoted text clipped - 14 lines]

 

[Gina Whipp]

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...

--
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

Okay here are all four tables Every thing is test box except amount that
is
currency
tbllockbox (Name of Table)
lockbox number (Primary Key)
Packet number
Deposit date
Form
Check
No Check
Correspondence
Amount
Rebatch Number
Box Number
Employee Check out
(need to print inventory by box number)

tblscanfile (Name of Table)
Batch number (Primary key)
In Date
Deposit Date
Form Type
Number of Items
Batch Source
Batched by
Amount
Rebatched
Notes
Employee check out
(need to print monthly for book)

tblmanualfile (Name of Table)
File number (Primary Key)
Amount
Deposit Date
Form
Employee
Rebatch number
Employee check out

tblcasefile (Name of Table)
Folder Number (Primary key)
Yr Received (use month and year only)
FEIN number
Company name
Auditor name
Box (box with auditor name and number ( 1-10 ))
Employee checkout
(need inventory of what is in box by auditor)

Kaw,

No screen shots won't help... Here's what I am asking for

tblNameOfTable
NameOfField
NameOfField

tblNameOfTable
NameOfField
NameOfField

Do that for all four tables. Indicate if any of the fields are Primary
Keys.

would it help if I emailed screen prints? I am a newbie to Access so not
sure

[quoted text clipped - 14 lines]

to
print out what is in each box.

 

[kaw via AccessMonster.com]

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,

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]

 

[kaw via AccessMonster.com]

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,

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]

 

[kaw via AccessMonster.com]

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.

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]

 

[Gina Whipp]

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

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,

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.

 

[Gina Whipp]

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

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.

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.

 

[kaw via AccessMonster.com]

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.

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]

 

[Gina Whipp]

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

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.

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.

 

[kaw via AccessMonster.com]

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.

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]

 

[Gina Whipp]

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

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.

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.

 

[Gina Whipp]

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

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.

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.

 

[kaw via AccessMonster.com]

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

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]

 

[Gina Whipp]

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

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

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.