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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|