# Customizing Primary Key



## VANESSA1

I did read previous threads but its not helping me. I have access 2007 microsoft office xp. I work in a government office and keep track of businesses for tax purposes. The unique identifier for these businesses is called a control and/or parcel number with a format of xx-x-xxxxx. How can I format it as a primary key as well as a foreign key?


----------



## OBP

VANESSA1, welcome to the Forum.
It will depend on the structure of the Control and whether or not you have control over the numbering, it may be possible to do it using a Formatted Autonumber. Other than that you have to use a VBA Recordset to do so.
But ask yourself why you need to use it as the Key and foreign key.
You can just as easily use a simple Autonumber field for the Key Field and a Number type Long for the foreign keys, that is what they are for.


----------



## VANESSA1

The format is a number given by the main server. The business may be out of state which has assets in my jurisdiction; so ABC Company in GA may have assets as DEF company. Both companies report different assets so they file their own return for assets they own. Also, since I am such a newbie at this I don't know what a VBA is. I'm sorry to be so naive at this


----------



## OBP

Well providing you have the control and/or parcel number field in the table it is always possible to find one.
If you use a simple autonumber Key field you find any related data using that instead of the control and/or parcel number. It is how Access is usually structured.

VBA Code is the basis of Macros in Access, Excel, Word, Powerpoint and Outlook to automate tasks in the background.


----------



## OBP

By the way the Users do not normally see an Autonumber key field as it is not necessary for them to do so.

Also the control and/or parcel number field can be Formatted using the Format Property.


----------



## OBP

Do you type in the control and/or parcel number or is the Access Database linked to the Server?


----------



## VANESSA1

The access database I am trying to create is not tied to the main server, although it gives the unique identifier for the businesses. I keep up with the data that is not in the main server, more detailed & specific information. Anyhoo, getting to the format, would that be in the table under design view. I tried doing it with a hyperlink but i still have to put in the hyphens. Geez I feel like an idiot this should be easier or I'm making it more complicated than it needs to be


----------



## OBP

You should put it in the Table's Field in Design view, second item in the lower section in Access 2003 and in the Form as well.


----------



## VANESSA1

Hey I'm so frustrated. So are you saying that the autonumber can be stand alone and I can add a field with the formatted control number? So how will this work out when I'm trying to set up relationships? Also, is there a way I can send the file so you can look at it?

I can make tables and forms fine. But its the relationship(s) and queries that I'm struggling with


----------



## OBP

Vanessa, it can be very tough learning Access when trying to do a real world database for your job.
What bosses & managers usually fail to understand is how Access works best. ie the Parcel number does not need to be the key field and Key fields are only there to create "Unique" identifiers that Access uses to further create the Relationships between the tables. These are then used by Queries to bring the data in the various tables back together for Input or presentation purposes on Forms/Subforms & Reports.
I will private mail you my email address so that you can send me a copy of what you have.
It does not need to use real data, just some data in the tables to work with.
You will have to reformat it as Access 2000-2003 for me to work with.
The easiest way to achieve this is to use the blank database that I will attach to this post, download & unzip it, open it and then Import the tables and queries from your database. Rezip it and then send it to me.


----------



## VANESSA1

Forgive me I have a zipfile in the access 2000-2003 format. trying to figure out how to send it to you.


----------



## VANESSA1

I figured it out here ya go


----------



## OBP

Vanessa, can I suggest that you remove the database now that I have a copy.

Now to the database, which comes first the Asset, the Maillocation or the Physical Location?
You could use the PID to link the tables, but I would prefer to use an Autonumber where possible, linked to a Number type long field in the other tables, which means that you would not need to duplicate the PID format and number.


----------



## VANESSA1

Hey, well the maillocation would be first then the physical location if different from mailing location and any assets which may be reported at a physical location not necessarily at all of them. While waiting to hear from you I did play with the relationships that seems to work. Its not logical to me but Access seems to like it. I set up different autonumber fields for each table. Mailloc-Link; Physloc=ID and tblasset=id. I linked "Link to Physicalloc and ID in Physcloc to Id in asset. It set up the one to many relationship in the mail location to the physcal location table which is okay but not in the asset table and i'm not understanding that cause i can have many assets at one location. Now based on that, just working with two table Mailloc and physloc can I set up a query when I ask if mail location is different than physical location to open the physical location table/form? I would also like this same logic for the physical location to assets. Now here is the interesting part. I'm trying not to type duplicate data because physical location is most of the times a mailing location also for different companies. So an example description is Garfield Inc has assets at Xavier Ltd and Xavier Ltd has its own assets to report. So how do I cut down on typing duplicate information?


----------



## OBP

Vanessa, I am glad that you are making some progress, can you post what I have by re-using the blank 2003 database?
Can you have more than one Physical for one emaillocation?
One thing that you do not want to do is use Autonumber for both Key Field and Sub Key Field as you can only have a one to one relationship between 2 Autonumber fields.
. The main tabel (email location) should have an Autonumber, but a sub table should have an ordinary Number type Long field
The sub tables can have their autonumber fields for "joining" to other Sub tables or if they are used for general data, like a table of City & State names that can be used with the Physical location.

As to not duplicating data, that is the main reason for using the Key Field, once you join a sub table, none of the main table data needs to be in the Subtables except for the subkeyfield foe joining.

The way to control the data input is to use a Mainform with "Tabbed" or untabbed Subforms for entering the sub table data. The "Master/Child" link between the Main & sub forms takes care of the Key field linking.

Similarly once the data is in the tables queries bring it together using the Key/subkey fields for displaying.


----------



## VANESSA1

Good Morning,

I'm not crazy about the attachment process it takes forever for me to figure out. The rest of the tables you are seeing are going to be action queries at a later date. I want to get these two tables working first.
Yes, there can be several physical locations attached to a single mailing location. For example, Company A can have assets at Companies B,C & D with these companies reporting their own assets separately. Now this isn't 100% the case. Some companies do have physical locations here with their Mailing addresses being at another location usually out of state.

Now the autonumbers is where logic gets lost to me. I figured a one to many relationship between the Mailing location and the Physical Location. I did set the field up in the physical location table as a Long integer

In linking master and child fields, does that happen on the Property sheet of the Mail location of physical location table? And does the Master link use my unique identifer, autonumbers or relationship connections?

As far as forms, I kinda like to idea of the subforms in with the main form so I can see everything.

You also state that once the data is in the table queries can join together. Well this is going to be a problem early on again with entering duplicate data I have over 3200 returns and that can add up to thousands to physcal locations and an even greater amount of asset information. So I kinda want to get these two tables running smoothly then move from there


----------



## Cookiegal

Vanessa,

Do your attachments contain any real data that could be considered sensitive (i.e. naming companies, etc.)?


----------



## VANESSA1

What I submitted is information that is considered public information by the state of Indiana


----------



## Cookiegal

OK, thanks for the clarification.


----------



## OBP

Vanessa, I have looked at your current version and I think it is close to what you need.
I have changed the Link from the tblAsset to tblphysloc to tblphysloc to tblAsset.

I also don't think your other 3 tables are necessary, you can just have a field in the tables to indicate New/Closed/Retired etc,


----------



## VANESSA1

Can you send me what you did so I can see it and see if I understand it?


----------



## OBP

I will create you a simple Mainform/Subforms to show you one way to do the input side before I post it.


----------



## VANESSA1

Cool! Thanks!


----------



## OBP

Vanessa, here is one example of how it can be done.
I have not formatted the forms and fields to make them pretty, just functional.
Use the Find a Control Numebr Combo to select PID 12-2-01039 or 12-2-01480 to see the Assets for the Location.
To see the mail details click the Mail Data tab.
Let me know what you think.

I have not deleted the spare tables, or the spare PID fields in the tblassets & tblPhysLoc tables, queries and forms.

PS, I don't know what Screen size or resolution you have, so the forms may be too large.


----------



## VANESSA1

Hey I LOVE IT!!! I can make it look "pretty" that isn't a problem. Curiosity question; where is the property settings for the tabbed windows just wanted to see what it looks like. I'll play with it some more and if there are other questions, you are the person of the day hour hey year!


----------



## VANESSA1

Yeah you're right the screen is huge! How do I get it smaller? Also, how can it set the forms up to be able to search on multiple fields? If I wanted to search by company name and/or company address on any of the forms?


----------



## OBP

Unfortunately the adjustments to form and Tab size have to be done manually in design view, close up fields to reduce size etc.

You can only search by company or Address if they are form. You should only need to search the form that contains the data. So the simplest way is to create "Find Combos" like the PID one on the Mainform.


----------



## VANESSA1

Okay, I'll work on that. I searched the threads again for this one but no concrete answer. In my assets, they may have a redundant description but the asset numbers and site codes are different. Is there a way I can set up an autocomplete so I don't have to type the full description out? I read something on a combo box. Would this be my route?


----------



## OBP

Yes you can have a combo of all current Descriptions, that has a "Jump to" facility that moves to the word that meets the letters as you type them in.
Is this to enter the Description to set it as "Redundant" in the table?


----------



## VANESSA1

I'm sorry don't really understand the question. So when I set up this combo box will it mess with the existing data already entered? I'm saying this because if I like this set up I may do it for more than one field


----------



## OBP

Well you would have to add the combo and also have the description field as well.
But i am not sure where you are thinking of putting it or how you will use it at the moment.


----------



## VANESSA1

Well for example(s). A company may have more than one "Vending Machine". They have different asset numbers. So, instead for typing "Vending Machine" in the description over and over again, I would like the field to recognize the entry and finish it for me. This would also apply to fields such as the PID, ACQ DATE Pool. I would like it to finish it unless I put in a different one.


----------



## OBP

OK, what you need is an Asset Description Table, that stores one record for each distinct Description. You then choose one of those for your asset table and just store the AssetDescriptionID (ie not the actual description).


----------



## OBP

I will have to go soon, I have already been on here over 8 hours.


----------



## VANESSA1

I understand. You have been wonderful and patient. But I thought that it would be easy like in excel and word. But I'll continue to plug away here. You have a wonderful and peaceful weekend. I got a baby shower for my newest grandchild this weekend so I won't be thinking about this as much. Talk to ya later


----------



## OBP

There is an Autocorrect option in Main menu>Tools>Options>Spelling>Autocorrect Options but I have never used it.
However for good database design anything that is likely to be repetitive should be in a Table for the user to Enter Once & then Select it afterwards. I know it means a lot more work for the designer but it makes data entry much more efficient & accurate, as it tends to avoid users entering the same things spelt differently. Sometimes it is even useful to split the Description in to 2 fields and have 2 tables, i.e 
Table1---------------------------Table2
Printer---------------------------Laser & Dot matrix 
Printer/Photocopier---------------Laser

Do you have any Lists of Asset types?


----------



## VANESSA1

No, unfortunately, the descriptions come in such a wide range, it would be impossible to nail down to a finite number. That's why I was kinda liking the idea of creating the field data as I entered.


----------



## OBP

EWell that is what you do with the Table, but you offer the user the option to select somethiung that is already there., if it isn't there add it.


----------



## VANESSA1

Okay got that, like it. So create another table called 'description'; the relationship would be one to many from the assets table; would I set up another autonumber as the primary field that links them? Also since the other autonumbers and links are not really a critical field to be displayed for data entry, does it have to be on the form(s)?
Also at this time the database does now have sensitive information, so how can I send this to you?


----------



## OBP

You can either post it on here as an attachment or send it via email.
The table should have an Autonumber and the Autonumber linked to the Asset Description field in the Main table.
Also change the Asset Description field to Number type Long so that it accepts the Autonumber form the combo.


----------



## VANESSA1

I would like to send via email so where would I send it and also do I have to reformat it back to the previous version of access as we have done before?


----------



## VANESSA1

Okay as I work through this with the data, I've come across another scenario. I have companies that own assets at physical locations we consider as exempt. These would be municipalities like government offices. These are not required to file a return so they would not have a "Control Number". I was wondering what would be the best way to confront this - either add another field in the main contact form called "exempt" and/or create a bogus control number that I know will not be used in the near future? 
I also see this situation coming up in a few months as far as the new businesses. They will not have a control number right away until they either file a return or are sent a notice of a estimated assessment.


----------



## OBP

It should not be a problem as you are linking via the ID Key field. You could add "Exempt" to the Status field or have anew Exempt Yes/No field.


----------



## VANESSA1

So if I have to do a search where would I search by company name since it woudn't have a control number?


----------



## OBP

Do you want the Search on the ona current form or on a special Search form?


----------



## VANESSA1

I would like to keep as simple as possible so on the current form. I have added the exempt status to the list box.


----------



## OBP

Which form & Table have the Company Name in them?


----------



## VANESSA1

tblMailLoc


----------



## OBP

You should be able to just add a Find Combo, have you created one before?
You use the normal Combo Wizard but at the first interaction with the Wizard you do not accept the default "I wnat the combo to look up values in my tabel", you select the last option
"Find a record on my form based on the value I selected in my combo"

But if you have more than one record, then you need to use different VBA in the Combo to set the Form's Filter to the value slected in the combo instead of finding the first record. That would list all of the Records with that Company Name.


----------



## VANESSA1

No I haven't done it before but I'm seeing where I would need something along that line in more than one instance. The physical location form more than likely needs to have list boxes as well for the Name and the PID Where I would enter the PID and/or name and it brings up the rest of the information on the form. UUUGH!!! I'm getting all confused again


----------



## OBP

Take it one small step at a time. get it working and the next one will be easier.


----------



## VANESSA1

I did an attachment but it looks like it went to post #24 dated 11/10/10 at 1:19pm


----------



## OBP

Something like this?


----------



## VANESSA1

Good Morning

Yes thank you. You're so good. Did you see my post from last night about the attachment?


----------



## OBP

Yes that was the one that I used.


----------



## VANESSA1

I must have sent the wrong file because there's stuff missing on this one. Can you tell me what you did and I can duplicate it?


----------



## OBP

You use the normal Combo Wizard but at the first interaction with the Wizard you do not accept the default "I want the combo to look up values in my table", you select the last option
"Find a record on my form based on the value I selected in my combo" and use the Mname


----------



## VANESSA1

Okay did it. The next thing I want to do is set up a parameter so when a control number and/or name isn't found it gives an message "Not found". That would trigger that its a new entry.


----------



## OBP

OK, click on the combo, in the properties find the "Limit to List" and set to "Yes".
Now find the "On not in List" event procedure and click on it and choose "Event Procedure", click on the 3 small dots to open the VBA editor and paste this in
MsgBox "This Company requires a new record"
Save the form.


----------



## VANESSA1

Okay I'm looking at the VBA code. You're saying "paste in" the message but I have no idea where


----------



## OBP

You paste it between 
Private Sub Combo47_NotInList(NewData As String, Response As Integer)
and
End Sub


----------



## VANESSA1

Mine says Private sub Combo45_NotInList (NewData As String, Response As Integer). I entered as you said. Yay it works! Okay I'll Do the same procedure for the Company Name.


----------



## VANESSA1

Okay next baby step. When I search on the company and/or control number is there a way I can get both the fields to be displayed simultaneously?


----------



## OBP

Yes of course, but you can only use one of them in the VBA search.
The easiest one to do would be to add the name to your Control Number combo.
To do this you click on the combo in design view, click on the "Row Source", click on the 3 dots and it opens the combo's SQL query. Drag the Mname down to the query and then click the close cross, Access will ask if you want to save the change, say Yes.
Now on the combo properties change the "Column Count" from 1 to 2, add ;2.54cm to the "Column Widths" and change the List width to 5.54cm.
You can play with the sizes to get the data to fit.
It will now display both Control Number and the Company Name for selection.
After selection it will only display Column 1, if you want to display both after selection it takes more work. Plus of course they can't both be in Numeric/Alphabetic order because they are related in the record.


----------



## VANESSA1

Okay did it and it works Thanks. Now to physical location versus assets. I came across a situation yesterday when data entering. I have an out of state company owning assets at two locations. Now I have already entered one of them on my main form as they were already reported by a different company. The main location is the same as the physical location. Now the asset list scrolls to a blank or new record. How do I get the physical location to do the same thing because it feels like I'm overwriting data. Also if the PID already exits will it put in the company name and other info from mail loc?


----------



## OBP

It won't put the other data in, but you can get it to do so using VBA, or perhaps you should have a "Same as Main Location" check box?
That is how sellers tend to handle Billing & Shipping addresses.


----------



## VANESSA1

Okay noodling that one. In the meantime, how do I delete a record? I have a typo where its the start of information but never finished therefore creating a duplicate/incomplete record in the main form


----------



## OBP

When on the actual record just press the delete key.
You can of course create a "Delete" button to do the same thing and add a "Are you sure" message to it.


----------



## VANESSA1

Tried hitting the delete key but when I look at drop down list its still there.

Another problem, when I'm trying to enter a new record with the "company name" it tells me that name isn't on list and won't let me enter it without the message. Should I change the combo list to that other check mark "I will enter the values I want"?


----------



## OBP

No, don't change the combo type at all.
If you know it is a new Company you can enter it in the Form rather than the combo, but you can aslo use vBA to put the new value in the table, form & combo.

The reason that the combo still shows the "deleted" record is because it has not been refreshed. That is one advantage of the Delete button you cna refresh at the same time.


----------



## VANESSA1

Hitting delete key and refreshing not working

Also in the main contact form the DBA Name is a lot of times different from the Company Name. I guess I'm not understanding how to do it. Right now I'm not able to enter anything because of this minor glitch, so if I can get that done we'll be done for today.


----------



## OBP

Sorry, I have to go, I have been on here hours already, I have to have some tea & then go out.
I will talk to you again tomorrow.
It would help if you could post your latest version in 2003 or email me a copy.


----------



## VANESSA1

Good Morning,

Did you get my email?


----------



## OBP

Vanessa, good afternoon here in the wet UK. I have received your email and I will download the database in a while, I am busy doing other things at the moment.


----------



## OBP

OK, now what is it you want it to do that it isn't doing?


----------



## VANESSA1

A few things. There is a record in the Company Name field called McDowell. It does not have anything else connected to it so I want to delete it. There is a verified company called mcdowell enterprises that has pertinent information attached.

I have an out of state company owning assets at two locations, I have already entered one of them on my main form as they were already reported by a different company. The main location is the same as the physical location. Now the asset list scrolls to a blank or new record. How do I get the physical location to do the same thing because it feels like I'm overwriting data. Also, if the PID already exists will it put in the company name and other info from the mail loc? I think using what you suggested using the check box might do.

When I am trying to enter a new record with the "company name" it tells me that name isn't on list and won't let me enter it without the message. Also, in the main contact form the DBA Name is a lot of times different from the Company Name. I guess I'm not understanding how to do it. I tried to enter stuff and now I have a couple of companies that have incorrect Parcel/Control Numbers. I also have a control number that I have stuff in the main contact information but nothing showing up for the Company Name field.


----------



## OBP

Vanessa, first of all the Company Name Combo box is not set up as a "Find" combo, I am going to set it up as one for you, but I have a question, what is the difference between 
MNAME 
and 
MDBANAME
Fields?

I have corrected the combo and also added a Delete This Record command button to the form that has an "are you sure message" and refreshes the 2 Combos afterwards.


----------



## VANESSA1

Many of the companies have a common 'company name' like IHOP (International House of Pancakes) but their corporate name or DBA name is Flipmeastack (true name). It makes a difference with my medical facilities, Like Elkhart General Hospital (DBA Name) has several different satellite locations all with different names. You should have some of that data so you can see what I mean. Does this answer for okay?


----------



## OBP

OK, now on to the problem of Locations and adding a Physical Location. Currently there are 2 sets of Navigation Buttons at the bottom of the forms, You may have to scroll down to see them if they are not currently visible.
The lower one is for email locations and the upper one is for Physical locations, as it doesn't look very obvious I normally replace one by adding "Next", "Previous", "First", "Last" & "New Record" Buttons on the Subform so that it is obvious what they are for.
Do you think that would be a good idea?


----------



## VANESSA1

Sounds good. Let's try it


----------



## OBP

I will email you the updated version.


----------



## VANESSA1

Okay, Look forward to it


----------



## VANESSA1

I went to add location and a VBA error came up: "Compile Error: Method or Data member not found"

Its pointing to Private Sub Command39_Click()


----------



## OBP

OK i will take a look.


----------



## VANESSA1

Hey, I'm leaving for the day, hope to hear from you tomorrow


----------



## OBP

Sorry about that, I copied the buttons and code from another database and forgot to remove these 2 lines

Me.Combo27.Enabled = True
Me.Combo27.SetFocus

So please delete them.


----------



## VANESSA1

Good Morning and a good afternoon to you. I did what you ask and a message comes up "are you sure you want to add a physical location? This is not for the Current date."


----------



## OBP

OK, the current date part should not be in the message, it should just ask if you are sure that you want to add a location.
You can delete the whole message if you think it un-necessary.


----------



## VANESSA1

How do i change it


----------



## OBP

Click on the Add Location button, in the Properties click on the "On Click" event procedure and the 3 dots to open the VBA Editor.
This line
response = MsgBox("Are You Sure You Want to Add a Physical Location?" & vbNewLine & "This is not for the Current Date", vbYesNo + vbExclamation + vbDefaultButton2)
should be
response = MsgBox("Are You Sure You Want to Add a Physical Location?", vbYesNo + vbExclamation + vbDefaultButton2)


----------



## VANESSA1

Okay I click, it brings up the message. How do I get to the property sheet where this code is located?


----------



## OBP

You need to click on it in design view.


----------



## VANESSA1

I went to design view, highlighted the detail bar of the physical location subform and I'm not seeing anything that I have been previously seeing on the property sheet


----------



## OBP

You need to go down the form and click on the Add Location command button to see it's properties.
The message is in the same place (just before) the extra code that you removed before.


----------



## VANESSA1

I finally found it


----------



## VANESSA1

Okay still tweaking. I'm noticing something. when I first open the database on the subform tabs, physical location is first. Is there a way I can get the Main Contact Info Tab to be first? It would make data entry much smoother

Can I edit a record? During the data entry process I have two companies with the same control number which is a no no. Do I just delete the record and re-enter it?


----------



## OBP

You can change the order of the Tabs, open the form in Design View, click on the Email Location tab and in the "All" poperty section set it's Page Index to 0..

yes you can Edit any record.


----------



## VANESSA1

Good Morning,
I would like to add a field to the main contact form. a list field but I don't know if its going to mess with anything already done. Is it a go?


----------



## OBP

No it won't mess anything up. I will add it to the database and send it back to you. I have also added some code to ensure that the 2 search combos show the same as the current record.
I assume it is to be added to the tblMailLoc table as well?
Do you need a table for the list?

To answer your question in the email, you only need the Add Location Buton visible when there is already one location, to add the first location you just fill in the blank Record on the form. You can also overwrite (Edit) the records at any time.

I will have to go grandchildren sitting, as I was yesterday afternoon.


----------



## VANESSA1

I don't think I'll need a table. The list has only three in it which are; 103-N, 103-O and 103-T. This will make it easier when genrating reports later.

When you send me your updates, is there a way I can add it on to what I already have so it won't clear out the extra data I have put in since the last update I sent you. Sorry, kinda under pressure here to show progress. Hey its Friday, yay!


----------



## OBP

You can Import the Form with the changes, but the Field you will have to add to your table or you will loose the data that you have added.

What do you want to call the field?


----------



## VANESSA1

FormType


----------



## OBP

Do you want a List box, or a Combo?


----------



## VANESSA1

A List Box


----------



## OBP

Can you add a List box called FORMTYPE to your tblMailLoc?


----------



## VANESSA1

Yeah I think I can do that. I'll get back to you and let you know


----------



## VANESSA1

Okay its in the table, how to I get it to the form?


----------



## OBP

I have attached a database called Form, it has a new tblMailLoc Query, a New tblMailLoc form & a new tblPhysLoc form in it.
Unzip it and put it in the same Folder as your database.
Open your database and on the Main Menu>File>Get External Data>Import browse to the Form Database and Import the query & 2 forms.
It will add a 1 on the end of the name, if you remove the 1 it will ask if you want to replace the original, say yes.
Or you can delete your originals before doing the import.


----------



## VANESSA1

Okay I made a backup before I did this. What I am seeing however, is tblPhyLocQuery2 with the double table symbol and tblPhyLocQuery1 with the exclamation point pencil-looking symbol. The forms came out okay.


----------



## VANESSA1

Okay I think I got my combo box types mixed up. I wanted it done the same and the new/current/closed field.


----------



## VANESSA1

Hey I'm out of here for the weekend. Talk with you Monday


----------



## OBP

Take the 2 of the end of tblPhyLocQuery2 and replace tblPhyLocQuery.

Open the form in Design mode and right click on the List Box, click on "Change To" and change it toa Combo.


----------



## VANESSA1

Happy Monday!

I changed the list box to a combo box but when I go back to form view, its not selecting anything.


----------



## VANESSA1

I am trying to delete a duplicate record but it tells me "the record cannot be delted or changed bacause table 'tlbPhysLoc' includes related records".


----------



## OBP

First of all, does the combo list the items?
To overcome the "the record cannot be delted or changed bacause table 'tlbPhysLoc' includes related records". you need to change the Relationship between the Mail and Phys tables. It is currently set with Referential Integrity enforced, but not "Cascade Deletions", so
Click on Main Menu>Tools>Relationships then right click on the relationship line between the 2 tables and Edit Relationship then tick the "Cascade Delete Related Records".


----------



## VANESSA1

Yes the combo list the items but nothing comes up on entry portion of form.

I took care of the relationships issue


----------



## VANESSA1

I'm dealing with the assets listing. Before the modification of adding locations, assets were data entered under one physical location. so I have the wrong assets in the physical location. Is there a way I can delete those assets that don't belong?


----------



## OBP

The easiest way would be to overwrite the Asset description with something like "Awaiting correct data".
What is the Control Source of the List box that is now a combo box?


----------



## VANESSA1

The control source says "FORMTYPE". When I check the Data Tab and All Tab it says the same thing. On the Row Source of the Data Tab it has the data I'm looking for.

On the Asset description, once I enter "awaiting correct data", would a delete query be ran?


----------



## OBP

You could run a delete query, but I would just leave the record there to be overwritten with the correct data.

I am not sure why the combo is not working, can you actually select something.


----------



## VANESSA1

Well I guess on the delete query. Anyhoo, on the combo, I click on the combo drop down list, try to select something with no results


----------



## OBP

All I can suggest is that you go in to the table and change the List Box to a Combo box, save it.
Delete the combo from the form and then put it back on again from the field list.


----------



## VANESSA1

Okay I did the changes in the table following the same pattern as the new/open/closed field. I deleted it from the form in design view. But when I attempt to add it from the field list I get an message "Access will create a new query and embed it directly into the form's or reports's RecordSource property. The form or report will no longer be based on the 'tbleMailLoc Query' query. Do you want to accept this change?"


----------



## OBP

OK, make sure that the field is in tbleMailLoc Query before adding it to the form.


----------



## VANESSA1

Okay did that, put field from list on form in design view, went back to form view, still doesn't work. Do I have to run the query or some thing?


----------



## OBP

Try my version and see if that works, it does for me.


----------



## VANESSA1

I unzipped tried to open the MailLoc Form and it says the MailLocQuery does not exist


----------



## VANESSA1

Hey, don't know what happened but it works now


----------



## OBP

You had to import the form in to your database before opening it.


----------



## VANESSA1

Is there a way I can take data from the database and put it in a certain place on an excel spreadsheet? What I would like to do read the ACQ DATE, the ACQ COST and POOL and calculate to the appropriate line in excel. Any suggestions?


----------



## OBP

You can, but why use Excel?


----------



## VANESSA1

I don't know how short to explain it. But when the taxpayer files in more than one taxing jurisdiction (we have 4) on one return, I literally have to have to go thru their asset listing and put the correct cost(s) with the right jurisdiction. I have a spreadsheet I do this in which is updated every year to reflect the current reporting year. It is basically a replica of the state-required form the taxpayer fills out when reporting. I can send you a copy if you'd like.


----------



## OBP

With this database will it still require manual adjustments?


----------



## VANESSA1

Hmm good question. What I'm looking to do is to be able to run reports showing the assets, costs and assessed values in the correct jurisdictions to the taxpayer(s) with the corrected value that should be reported, especially those who report erroneously. What would you recommend?


----------



## OBP

Well you could probably duplicate your Excel Sheet using a Report, it takes a lot of playing around with Formatting, but it can be done.
Can you post a blank Excel sheet?


----------



## VANESSA1

I guess I'm confused on what you mean by 'post'.


----------



## OBP

Attach it on here.


----------



## VANESSA1

Here ya go


----------



## OBP

Vanessa, that is some worksheet, it would take quite a bit of building up, but it may be possible to reproduce something similar, but one thing you need to know is that Access does not possess a "Fit to Page" like Excel, so I don't think it would print on one page, although one District at a time would.
Is all the data in the Excel sheet also going to be in the database?


----------



## VANESSA1

I would like for it to be. One district at a time would work. The dates as you can see change every year but the percentages pretty much stay the same. I would like some kind of way to get the excel spreadsheet to work with the asset listing like if I put in a date, cost pool it would calculate the assessed value.


----------



## OBP

Access can do that calculation for you, if not in a query it can be done using VBA.
I would just need to know where the data comes from. ie. which fields in which tables.


----------



## VANESSA1

Really? Cool! Okay it comes from the tblassetlist - acqdate, acqcost, pool. the result is the assessedvalue field


----------



## OBP

Is the assessedvalue field Calculated?


----------



## VANESSA1

I know it was set up as a currency field. The equation is the acqcost *TTV%= assessed value.


----------



## OBP

You can either put the value in on the data entry form or in a query.
Is the data in your old database that we worked on before?


----------



## VANESSA1

Yeah there should be data there. I just went back and double-checked. There should be plenty to test data there.


----------



## OBP

Ok, I will set up a query and se if that is a starting point to get where you need to go.


----------



## VANESSA1

Cool Thanks


----------



## OBP

I have created a simple query, but there seems to be a lot of Duplication in the Records, is that correct?
I can't really see how to go from this query to the Excel sheet, so I have attached a copy of it in the Form Database.
Import it and have a look and then come back and tell what you want to do with the Structure of the data.


----------



## VANESSA1

When I try to open the Form.mdb I see the query but it says it can't find the tblassetlist. There is also another locked file Form.ldb but its only 1KB.


----------



## OBP

You need to Import the Query in to your database and run it there.


----------



## VANESSA1

Verified it and it works. Do I have to run it again because its not showing for all the assets


----------



## OBP

Sorry, it doesn't have any Filter on it, it just lists the records, so I don't know why any would be missing.


----------



## VANESSA1

Hey OBP back from the holiday (thanksgiving here). Hear the weather sucks there. Anyhoo, When I run the query and look at the datasheet it has calculations also, Is there anything else i can do?


----------



## OBP

My problem in advising you is that I don't know what you need compared to what you have now.


----------



## VANESSA1

Hmmm, I guess I'm stuck too. I tried looking at the code to see if I can see the math portion of it but couldn't see it. When I looked at the datasheet at first I thought maybe it was one pool calculation missing but that's not it either. I was wondering if maybe I didn't specifiy the dates with the TTV% because that would make a difference too. For example, on my spreadsheet in Pool 1, I have an asset acquired between 3/2/08 - 3/1/09 at a cost of $1000. So I would enter that $1000 at 50% which the spreadsheet would calculate a TTV amount of $500.


----------



## OBP

So what is it actually doing?


----------



## VANESSA1

Well, when I put in the Acqdate, Acqcost & Pool, I figured it would put in the assessed value but its hasn't done it since you sent the updated query. And I just ran the query to see the datasheet and I'm seeing where there are AcqDates and Acqcosts and Pools, but approximately half the assessed values haven't calculated. The ones that did calculate are in pool 2 and only one calculated in pool 1 so its weird to me. There aren't any calculations in pool 3. I know I haven't done any in pool 4 yet.


----------



## OBP

Where does it do the calculation?


----------



## OBP

I will take another look tomorrow.


----------



## VANESSA1

Well, that's kinda what I was looking for. I know on my spreadsheet if I put in a number at the adjusted cost (column C) it calculates the percentage and gives the result. I think I can see where you would be confused too with the spreadsheet. I'm sorry about that. When I look at these things its so sub-conscious to me these days. So the calculation is Column C * TTV = TTV on the spreadsheet.

On a side note regarding this site. When I get messages from you I usually read them from my email. Is there a way that your responses come up automatically while I have the site up. Usually I have to leave the post and come back in to see it.


----------



## OBP

But where is TTV in the database?

The Forum updates the Thread as soon as I put the post in.


----------



## VANESSA1

Good Morning From Indiana

Column C is the acqcost TTV% is on the spreadsheet and the TTV is the assessed value on the database.

As far as the forum when I keep the site open I never see the updates oh well no big deal just curious


----------



## OBP

OK, I assume that you are talking about the Tooling Sheet in the Workbook.
Where are the %ages stored in the Database?
The current Assessed Value appears to have already had the calculation done.

Do you want the data grouped and summed like the tooling sheet?

The Forum only updates when you refresh the page.


----------



## VANESSA1

Not the Tooling sheet. That's a later horse. Look at the 2010 sheet where the four taxing districts are. I only need one of those. Its where the data looks redundant to you understandably. If it helps you to understand a little how the numbers coordinate. take a look at the first number on the control/parcel number and look at the taxing districts. That's why I only need one


----------



## OBP

OK, lets go through the 2010 columns, what is the equivelent database Field names for columns a to e?
Now Column f has the TTV% in it, where is that in the database?


----------



## VANESSA1

Ok, column B refers to the ACQDATE. Column E refers to ACQCOST. The (POOL) (1-4) as you can see are up to 4 with a range of dates for each which would use the ACQDATE field. The TTV% does not exist in the database. I'm not sure if I'm explaining clearly or not for you let me know


----------



## OBP

If the TTV% cost isn't in the database, shouldn't it be in there to make your calculation?
I am obviously missing something in my Understanding, as I am sure I could do what you need if I could get it.


----------



## OBP

Where does the Tax District come from?


----------



## OBP

Also do you need the date periods 3-2-09 TO 3-1-10 rather than Years?


----------



## OBP

Import and look at this query, which I have used grouping on.


----------



## VANESSA1

I have a Debug error problem in the database. It only happens with one company I try to search. What would you like me to do?

The spreadsheet calculations was something I didn't think I could do in the database. So would another table have to be set up for this? If so, the fields are going to be interesting to name ;-)


----------



## OBP

Access is pretty good at doing calculations, so I would go for it, but I need to know what extra data is required to carry them out to know where to put them.
Have you looked at the new Query?

What Company gives the debug error?


----------



## VANESSA1

I haven't looked at query yet. Was going to when I came upon debug error

You know what? This thing is like a car. It acts up until I tell someone. Let me run that query real quick


----------



## VANESSA1

Okay I see what was done. This one gives a summary for each year and each pool. I can see needing this at some point.

The taxing district has a different tax rate. My jurisidiction has 4 tax rates each defined by a geographical area.

Yeah, the date periods is more of what I am looking for in each pool. The pool stands for the life years of an asset as defined by our Internal Revenue. The spreadsheet is a duplicate of the state return that the taxpayers file each year but on a local governmental level.

So as I'm looking at the spreadsheet, I can't figure out for the life of me how to name the fields where the date ranges and TTV%'s are. I just have a logic in my head; Company A has an asset in which the acqcost is $1000 reported in Pool 3 with an acqdate of August 5,2006 its TTV%=45% which would have an assessed value of $450.


----------



## OBP

Is the TTV% based on the Asset, the company, the area?


----------



## VANESSA1

The TTV% is a depreciation schedule of an asset assigned by our local government regulators who monitor what we do. The percentages have been the same since I've been employed here. Only the dates are updated every year.


----------



## OBP

So you could add the TTV% field to the Asset Table and enter the values for each Asset?
Or do you have Asset & TTV% tables supplied by the government?


----------



## VANESSA1

The companies provide the asset information, acquisition costs and dates. They also report which pool they are reporting in. I guess I'm looking at the asset list wondering how would the TTV%'s be represented when their are over 30 date variations divided within the four pooling schedules.


----------



## OBP

Well they must represented or you wouldn't be able to use them in Excel.
I don't know how the 30 date variations/Pools & TTV% interelate, but it sounds a bit like you need a date/Pool/TTV% table where you can select a Date period and then a Pool and that gives you the required TTV%.
Getting the Relationships right is the most important part of Relational Database design and controls how it will function.


----------



## OBP

It sounds like you also need a Govenrment Area Table as well.


----------



## VANESSA1

Okay well how does this sound. I create four tables POOL1, POOL2, POOL3, POOL4. The dates however change every year to add on the new year. Will that be a problem?


----------



## OBP

Not 4 tables 1 table, otherwise that would get a bit unweildy.
Can you show me how the Pools, dates and TTV% are related?
Do you get some kind of document that you post or send me?


----------



## VANESSA1

I just emailed you the form that some the taxpayers fill out along with their asset listing. Usually I can look at a return to determine what pool they are reporting in. For example this company mostly has copiers. These assets according to the IRS have a 5-8 year life on average before they are retired and/or disposed.


----------



## VANESSA1

The reason I was thinking 4 tables because of the percentages. Pools 2,3, & 4 start out with the same percentages, but then change further down


----------



## OBP

Great, got that. So you get this filled in and you want to put this in your Database?
If so, do you check that they have put the Asset in the correct Pool?
I will have a read of it and get back to you tomorrow.
I may put together some sample tables for you to look at as well.


----------



## VANESSA1

Yes exactly, many times they report in the wrong jurisdiction, wrong taxing districts wrong pool for the asset etc. So I what I have been doing on spreadsheet is putting the assets where they belong. Hey by the way my coworkers say you have been officially drafted into our world as a honorary assessor - the unpursued profession. Have a great day


----------



## OBP

Does an honorary assessor get a Salary? 

Can you confirm the Date format in the Multiple TD Excel Sheet, are they d/m/yy or m/d/yy

Do you put the Assessed Value in or the person being assessed?


----------



## VANESSA1

Good Morning its been crazy here. The date format is d/m/yyyy. On the spreadsheet I put it in as verification of what they report on the forms.


----------



## OBP

Good morning (nearly 4:00pm here in the UK).
I have a concren when looking at the Assets table that the Same link Nimber has more than one PID, this is especially true of Link 23, is this correct?

Also what is the intention with this database, do the Assets stay in the database for next years assessment, if so do you update the Assessed value or so you want the Database to do it when you add next years date period?


----------



## OBP

ie. should you have a 2010 assessed value and a 2011 Assessed value next year, 2012 Assessed value the year after?


----------



## VANESSA1

Is there a way I can go to that link?

Yes the assets stay unless the company reports that they are no longer inactive. So they need to stay for the next years assessment and the assessed value will have to be updated. It would be nice if the database can do it when I add the next year. Be aware however as the years update the TTV% will also change. Everything will move down a line. Also be aware, however, that on the spreadsheet, very last line where it says "Prior to....". Any costs basically accumulate at this level.


----------



## VANESSA1

Once the asset is inactive however is there somewhere I can move the information so it doesn't calculate on the active list?


----------



## VANESSA1

Is there a way I can see the link?


----------



## OBP

Which Link? 
If it is Link 23 you can see it in the Asset Table and also in the PhysLoc table, it should be Autonumber 23 in the Physloc table.


----------



## OBP

You could store the Assessed Value for this Year and then store next years as well, I understand that it will change as the age slides down the time periods.


----------



## OBP

It could be that I am working with an old version of your database as it doesn't appear to be making sense data wise.


----------



## VANESSA1

Okay I'm looking at the datasheet for the asset list table and as I scan the entire record it is different because the asset numbers are different. This is what I was talking when one company has more than one asset at a physical location


----------



## OBP

Can you post what you are working on now, or is any of that data confidential/personal?


----------



## VANESSA1

I can email to you. The stuff is sensitive right now


----------



## OBP

OK, that would be good.


----------



## OBP

That is much better, it all makes sense now. Do you actually need the PID number in the Asset Table?


----------



## VANESSA1

It help because when I do run reports it will help the taxpayers on both ends in matching up their tax bills


----------



## VANESSA1

I've come across several problems importing. Some name(s) that couldn't be found. Which ones should I be deleting? And I'm not seeing assessed values for many in the datasheet on the asset list

I didnt do a backup before I ran queries and all of my assets entered are gone crap!

Okay I see the assets on the other queries and forms but not in the main form

I'm not touching anything else today until I talk with you tomorrow Nite


----------



## OBP

Vanessa, I didn't do anything with your tabels or forms at all and you shouldn't need to import anything as your database was returned in total.
Unless of course you have added more data since you sent it.
I have returned your original database with the one with additional tabels, queries etc.

I just wanted you to look at the new form to see how to enter the VTT for the poll time periods.
You should also have a copy of your original database as an attachment to the email you sent.


----------



## VANESSA1

I'm sorry I panicked probably because the type of day it was yesterday. Okay, I will reload it and look the new database


----------



## VANESSA1

Okay deep breaths everything back to normal. Hey it looks good. So I guess my questions are if i select the date period does it give the correct TTV%? Usually I'm used to seeing the most recent date period on the top, here its in the middle. And your dates are a little backwards. Take a look at the spreadsheet. The usually time frame is 3/2/xx to 3/1/xx I'm also looking at your date period links and I see consecutively 11-14 but then it jumps to 17. Can I ask why?


----------



## OBP

It was just the way that I entered them, I entered the ordinary dates first and then the before and after dates.
once the data is in for a pool they can be re-ordered to suit.
Did you try selecting some pools and date periods in the form to enter some TTV%?
The TTV table is where the overall values will be stored like the Excel sheet. 
I then need to find a way to use the data in the query to do your calculation for you.
If you want to store old Assessed Value for this Year when you get to next year they will need a table of their own. Otherwise they will get overwritten with next years values.


----------



## OBP

I won't be able to do much today, I have the Grandchildren coming.


----------



## VANESSA1

I'm in the Pool form and yeah I can select the Pool Number and the Date Period but nothing changes in the TTV%. I entered sample data into one of the forms putting the amount in Pool 1 but it does not calculate the assessed value. Where else do I need to enter data to test it?


----------



## VANESSA1

Awww that's okay. Hey how's the weather there anyway? Our east coast got slammed so I figured maybe stuff was heading your way


----------



## OBP

Reference post # 210, the form is for entering the Excel VTT% values in to the Database and changing them next year of course. It doesn't do any calculations yet. I still have to arrange that.

Most of the UK is under Snow, but here in South Wales we are clear, it is cold.


----------



## VANESSA1

I'm not expecting any results soon on this one but I got another scenario. Remember on the workbook the tab Tooling? Well, now its coming into play as I enter data. The date ranges are this; for the current assessment period 3/2/09 - 3/1/10 the TTV% is at 30%. Any tooling prior to these dates are at 3%. There is no pooling for these types of assets.


----------



## OBP

OK, I think that you should have a simple Tooling TTV% table, with just the 30% and 3%.
With a table at least you can change it if changes in the future.
Did you try putting the other TTV% values in to the new form?


----------



## VANESSA1

Okay put them where?


----------



## OBP

Select a Pool, select a date period and enter the relevant TTV% in the TTV% field.


----------



## VANESSA1

Good Morning TGIF Did you enjoy grandchildren?


----------



## VANESSA1

Hey is this to be imported or just looked at?


----------



## VANESSA1

Okay I looked at it and saw the message. Pretty cool. And you're right on that TTV%. I'm glad I don't get alot of costs at that TTV% in that pool.


----------



## OBP

If that calculation is suitable, I will have it update the Assessed value field instead of providing a message?

But you didn't answer my question about storing previous years assessments.


----------



## VANESSA1

When you look at the spreadsheet all the costs of the active assets move down a notch. So if I had $1000 in Pool 3 at 45% the following year that $1000 would be at the TTV of 37%. The acqdate doesn't change, just the TTV%. 

Now, if a taxpayer informs me that an asset is no longer active, I don't want to lose the information, I just want to move it to another table which is in the database tblretiredassets. I just figured when that time came and update query could be ran to move that info to that area.

I have the same idea for closed businesses.


----------



## OBP

You do not actually need to move any records, just have a Status field, active or retired.


----------



## VANESSA1

So, when I run a report for current assets for a taxpayer if those fields are checked its reflected on the design of the report? I'm not sure if I'm wording it right


----------



## OBP

Yes, anything ticked as Retired would only show on Retired Asset Reports etc.
I will be sending you a new version shortly.


----------



## VANESSA1

I got it. I'm verifying data now


----------



## VANESSA1

Okay it did fine on some that I checked. But there are some records that the assessed value is incorrect and/or did not calculate the assessed value at all. Is there something I need to do?


----------



## OBP

Vanessa, can you tell me which ones it did not update.
It will not update them if the ACQDate, ACQCost or the Pool number is missing,as it uses all 3 to establish the VVT% or the Assessed Value.


----------



## VANESSA1

Okay look at these control numbers: 11-2-01045 did not update AV; 09-2-00385 the AV is incorrect; there are several more with these scenarios. Did you want all of them?


----------



## OBP

With regard to 11-2-01045, the 1st PhysLoc, records 1 & 3 Updated correctly, but record 2 has no ACQ Date so it can't be updated.
With regard to 09-2-00385, it updated corretly, the original value is incorrect for pool 2 and 1999.

At least that is what I get.


----------



## VANESSA1

I'm looking at the 11-2-01045 I can see where and why the second record didn't update but I'm still looking at blank AV's for the 1st and 3rd. Any suggestions?


----------



## OBP

Did you re-enter the Pool Number?


----------



## OBP

09-2-00385 gave me $63.86.
11-2-01045 gave me £436.06


----------



## VANESSA1

Okay, on the 9-2-385 I must have typed that AV in myself cause 15% would have the AV at $64. The value that is there will show itself later when we get through this portion. Is there a way to update it?


----------



## VANESSA1

Okay it calculates when I re-enter the Pool Numbers. Does that have to be done for all of them?


----------



## OBP

Just re-type in the Pool Number.


----------



## VANESSA1

Control Number 12-2-00074 physical location #4 when i re-enter pool it doesn't update.


----------



## VANESSA1

Yeah on that control number I just mentioned, there are several physical location records not updating when i re-enter the pooling


----------



## OBP

There is a very good reason for that.
From your Excel sheet
3-2-07 TO 3-1-08
3-2-06 TO 3-1-07

is 3-1-07 3rd of January or 1st of March, in the UK it is the 3rd of January, so for the dates that I have entered there is a gap between 3rd of January and the 3rd of February in the next period, which 01/02/2007 falls into


----------



## VANESSA1

Which Pool are you looking at?


----------



## OBP

All Pools are the same


----------



## VANESSA1

LOL that's funny especially when the date format is mm/dd/yyyy. Why should it get confused?


----------



## VANESSA1

Okay here's one. Control number 12/2 02654 P/L location #2 one of the records has a acqdate of 1/31/06 in Pool 2 that's not updating. Is this because of that same gap?


----------



## OBP

Because the UK date format is dd/mm/yyyy.
So what are those date periods in the Excel sheet
3-2-07 TO 3-1-08 - starts the 2nd march
3-2-06 TO 3-1-07 - ends the 1st march?
If so I or you need to re-enter the Dates in the Date Period Table to reflect the incorrectness introduced by me taking them as UK Format

Once entered Access has trouble changing them to match the change in Interantional date formats.


----------



## OBP

I just changed the date in question around to the correct mm/dd/yyyy format and it now works in the Calculation giving $67.15 for the 4th PhysLoc Record.


----------



## VANESSA1

Well I haven't incorporated it into the main database yet and I really don't know where to do the updates at.


----------



## OBP

OK, l will upadte the Table and send you a new copy.
It is going to be harder to incorporate the changes I am making to the design the longer you leave them.


----------



## VANESSA1

Our assessment date is March 1st of any given year. So if a company reports acquistion of an asset on March 2nd it would be reported on the following assessment year


----------



## OBP

New version sent.


----------



## VANESSA1

Okay looks good. So what's going into the main database?


----------



## OBP

That would depend on what is already in there.


----------



## VANESSA1

Morning its Monday already. So do I need to look at both databases and import from your latest version to mine?


----------



## OBP

Good Morning.
yes you need to see what is already in your database.


----------



## VANESSA1

Okay I've backed up the main database. Ive got both opened. Do I just select the tables queries and forms in your version that I don't have in the main database?


----------



## OBP

Yes, plus the tblassetlist form, which contains the new VBA.


----------



## VANESSA1

Okay did that. Do I need to re-run queries?


----------



## OBP

Did you replace the old tblassetlist form?

You should just be able to use it.


----------



## VANESSA1

Yep you're right it updates just fine. Thanks. Ready for the next step? This I think this will be in a report, of course, I've been wrong before. This also is from the multiple spreadsheet. If you look at the last 3 lines where it totals all pools, takes 30% of the acqcost total and compares to the total assessed value. Whichever is greater is the reportable assessed value. Why you ask? Because there is a 30% cap that the state of Indiana says that is the minimum on an asset the taxpayer can report.


----------



## OBP

Yes a Report is normal, but you can also show it in a Form as well.


----------



## VANESSA1

Well I would like to be able to get it into a format I can send to the taxpayer so when I change their assessment they would have detail as to why. So a format would be the Main Company, physical location companies, the asset detail for that physcal location company, totals per pool, grand totals and final assessed value.


----------



## OBP

So I assume that you want to select the Taxpayer?


----------



## VANESSA1

Now referring to posts #213 & 214. The tooling. There is only a acqdate and acqcost. It does not have pooling. Any tooling acquired for the current assessment year woud be at 30% (3/2/09 - 3/1/10). Anything prior to that would be at 3%. The 30% cap does not apply to tooling. This would also have to be generated in a report to the taxpayer in a similar manner as last post


----------



## VANESSA1

Yeah I would like to select the taxpayer


----------



## OBP

How does the data identify that an Asset is Tooling?


----------



## OBP

Taxpayer by Control Number or By Company Name or both?


----------



## VANESSA1

Well on the main form, the FORM TYPE = 103-T


----------



## OBP

Taxpayer by Control Number or By Company Name or both?

Summary by MailLoc or PhysLoc?


----------



## VANESSA1

Summary by physical location


----------



## VANESSA1

Company Name, Control Number


----------



## OBP

Step 1.
Import the Physloc Summary query from the attached database and run it to see how an access query summarises the data. See if it tallies with any of your results.


----------



## VANESSA1

I get very few SumOfASSESSED Value results approx 25 results


----------



## OBP

But are the totals correct for the Company?

How many assessed values did you expect to get, remembering that the the data has to be in the Tables.


----------



## VANESSA1

I don't know what company is reporting them. I need the main location. Those would be the ones where FORM TYPE = 103-O


----------



## VANESSA1

Okay I figured some out. The numbers I verified on the smaller asset listings are correct


----------



## OBP

This version has the Form Type added.


----------



## OBP

I will post a version with a Selection form tomorrow.


----------



## VANESSA1

Okay Nite


----------



## OBP

Good Morning, a slight change of plan. I have added a Button called Summary to your tblMailLoc form (as it already has the Selection Combos), modified the Physloc Summary Query and Created a Report of the same name, which the button opens for the Record Currently displayed.
So you need to import the Query, Form & Report from the attached database.


----------



## VANESSA1

Good Morning, I noticed this yesterday but the zipped file is coming with a folder called Desktop. Is there a reason for this and will it create any problems?


----------



## OBP

It shouldn't be any problem, that is where I keep your database and the Form database.


----------



## VANESSA1

Okay done. do I need to rename the files that have the '1' on the end? Its it PhysLoc Summary query


----------



## OBP

Yes remove the 1 and replace the original ones.


----------



## VANESSA1

I'm looking at the report. Is this just generic right now?


----------



## OBP

It is just to show you how to create a report based on a query which has it's record selected by a form.
You could have that on a another Report (as a subreport) which has a header containing the Company data etc.
Or you could have it as the summary of a report showing a listing of the "Pools" data.


----------



## VANESSA1

Okay cool. Hey I'm coming across another scenario since the database is growing with data. The FIND icon and the Search at the bottom doesn't work for what I want to do. Is there a way to do searches on several fields in both the Main Contact and/or the Physical location. I would like to results to be displayed as a listing where I can select the record that i want. Most of my searches would include the Control Number,Company Name and address(es). Once again I'm not sure if I'm explaining myself clearly.


----------



## OBP

You can search for anything, but it does simplify things a lot if you do the Searching on the form that holds the data, ie. search Physloc for physloc fields.
If you want a seperate Search Form you can do that as well.


----------



## VANESSA1

When I do the search at the bottom I select physical location, but when I start typing, it goes back to main location. When I use the Find Icon it tells me that the command isn't available now.


----------



## OBP

What does this mean "When I do the search at the bottom"?
Search at the bottom of what?


----------



## VANESSA1

Okay sorry. at the bottom of the form where the record numbers are there is the little search box


----------



## OBP

There is?
Can you show me a screen print?


----------



## VANESSA1

Okay just a minute


----------



## VANESSA1

Here you go.


----------



## OBP

Sorry I look at Word 2007 docs.

But I think you might mean the Record entry slot where you can jump to a record number. It is not for searching.


----------



## VANESSA1

Sorry here it is again in the older format


----------



## OBP

Right, that looks like an Access 2010 addition of using the old Edit>Find type of search.
I normally create a dedicated search using VBA to set the Form's Filter or run a Search Query.
Do you want to search one field at a time or more than one field?

I will have to go soon.


----------



## VANESSA1

If you don't get to it today that's fine. I got a meeting in a bit anyway. But I would like to be able to search on multiple fields if at all possible.


----------



## OBP

OK, talk to you tomorrow.


----------



## OBP

Morning, can you give some examples of searches that you would like to be able to do?


----------



## VANESSA1

Good Morning. Sorry I was out of the office yesterday. I would like to search by Name, address and Control Numbers on both the mailloc table and the physloc table


----------



## OBP

OK, I think I will add a "Search Tab" and see how that goes.


----------



## VANESSA1

Cool. Hey I love the xmas icon


----------



## OBP

That is the Forum's doing.


----------



## OBP

Vanessa, while testing the Search facility with my version of the database I have identified some anomalies.
Record 117, Control Number 09-2-01544 ADT SECURITY SERVICES INC has as it's second PhysLoc record ID76, PID 09-2-01316 address 23719 US HWY 33.
But 09-2-01316 is also a MailLoc in it's own right, so you have a MailLoc and a PhysLoc with the same Control Number and data.
Is it the same in your version of the database?


----------



## VANESSA1

Hi I'm here, read what's going on. Got alot of fires this morning will get to this asap


----------



## VANESSA1

This is saying that ADT (record 117) has assets at A & M Food Mart (record 21). So, A & M Food Mart has its own business which reports its assets. That's kinda what I want the search to do. If I look up a company like A & M the results would be any company(ies) that have assets at that location. Vice versa would be if I search ADT it would give me listing of all their physical locations information.


----------



## OBP

I will send you the search to look at.


----------



## VANESSA1

Okay when I do searches I get one error message "You must enter a value in the tblMidLoc.Mid field. It happens when I try to scroll down when it says record 1 of xx; when I click on Go to this record.


----------



## OBP

What did you search for?


----------



## OBP

I do not get any error messages.


----------



## VANESSA1

Okay its doing the car thing again. Its not doing what it did earlier. Sorry


----------



## VANESSA1

Can it be imported yet? If so, what needs to be done?


----------



## OBP

You need the Search Query, the Search Results form & the tblMailLoc form. You will need to replace your tblMailLoc form with the new one.

Does it do what you were thinking of?


----------



## VANESSA1

Most of. When it goes to this record, did we talk about it showing the asset information if available or was that to be in a report?


----------



## OBP

The assets are still shown on the Physloc Tab, as it is linked to the MailLoc tab. 
If you want more detail it would probably take a new form to display it.


----------



## VANESSA1

Good Morning. I see what it does and that will work. It's doing what I need it to do. I'll work on getting stuff imported. 

Did we ever finish talking about the tooling? Those are the assets that will not be in pooling and only have the 30% and 3% TTV.


----------



## OBP

We did, but I have been so busy I have forgotten.
I will look at it agian.


----------



## VANESSA1

Okay, I went to import and its telling me 'tblMailLocQuery' specified on this form or report does not exist


----------



## VANESSA1

I fixed it


----------



## VANESSA1

Good Morning How are things going?


----------



## OBP

OK, lots of Snow. I will take a look at Tooling for you.


----------



## OBP

Vanessa, can you remind me how you identify an Asset as Tooling?


----------



## VANESSA1

On the Main Contact Form, If Form Type = '103-T'


----------



## OBP

Do you have any data in your database now that has 103-T in it?
As the version that I have does not have any?


----------



## VANESSA1

Yes I do


----------



## VANESSA1

Do you want me to email it to you?


----------



## OBP

Yes please


----------



## VANESSA1

Let me know if you got it I got some weird messages on this end


----------



## VANESSA1

Sorry I know you didn't my mistake


----------



## OBP

Vanessa, I received it OK, but unfortunately the Zip file only contains your desktop link to the database, not the database itself.


----------



## VANESSA1

Well poo lets try this again


----------



## VANESSA1

Good Morning, How was your holiday?


----------



## OBP

Nice & quiet thanks.
I am working on the database.


----------



## OBP

For tooling you wrote "The date ranges are this; for the current assessment period 3/2/09 - 3/1/10 the TTV% is at 30%. Any tooling prior to these dates are at 3%."
What about after 3/1/10?


----------



## VANESSA1

Good Morning. That starts a new assessment year so 3/2/10 to 3/1/11 would be at 30% . BTW sorry I wasn't here yesterday. Have a new grandchild.


----------



## OBP

Congratulations on the Grandchild. So does "3/2/09 - 3/1/10 the TTV% is at 30%" still stand or is that now relegated to 3%?


----------



## VANESSA1

The 3/2/09 to 3/1/10 is going to be at 3% since there is a new assessment year approaching


----------



## OBP

I have emailed you a new version. I have used it to update the assets for 12-2-00059


----------



## VANESSA1

Okay Thanks I'll look at it now


----------



## VANESSA1

I'm looking at it and of course a little confused. I notice the combo 74 is identical to form type in main form. When I go to a company that I know the form type is 103-T that field is blank until I select it at top. Just want to know what's the difference?


----------



## OBP

Vanessa, sorry, I should have set that one to "Visible" = No. It is used by the VBA to check which formtype the main Record has, I needed it visible to do the tests.


----------



## VANESSA1

Ah I see. Well from what I see the math is doing good. Do you want me to hide it or do you? If so tell me how. Also, guess what? We're down to just reports now I think and this baby will be done. Whoowoo!


----------



## OBP

You can hide it, click on the combo in design view and find the Visible property and set it to No.
Yes we are getting there slowly but surely.


----------



## VANESSA1

Okay that worked. So what do I need to import?


----------



## OBP

The Table "ToolingTTV"
The Query "ToolingTTVQ"
The Main form "tblMailLoc"
The Subform "tblassetlist"


----------



## VANESSA1

I noticed something. The versions are identical. Can I change your file name to a different one so I don't overwrite my current one?


----------



## OBP

Yes of course.


----------



## VANESSA1

Okay don't know why I'm having this problem. I imported, can see the files I imported but when I check the data, its not updating on my version. Can you think of any reason why?


----------



## OBP

Did you replace the old Forms etc?


----------



## VANESSA1

Okay there are problems. I see the components that I imported. However, when I do a search, usually when it says 'go to this record' no problem but now it doesn't go there. Also, the pooling isn't updating for form type 103-O. The message says "Microsoft Office Access can't find the field 'l' referred to in your expression". So right now this kinda stops me in my tracks for today. I will email you this version. Go to Coca Cola Enterprises and retype what ever is above and see if you get the same message.


----------



## VANESSA1

Okay the forum is telling me you're gone for the day


----------



## VANESSA1

Good Morning. I got it and it seems to be fine now. Thank you. Now for the final phase. The report we worked on earlier. I like this format but can I make it more detailed? I removed the FormType. Where can I put the company who reporting them?


----------



## VANESSA1

Hey I thought it was working. I'm still getting that "I" error message


----------



## OBP

You will get the error because you have removed the FormType combo from the form.

What detail do you want on the Report?
When you say "Where can I put the company who reporting them?", do you mean the Report or the Form?


----------



## VANESSA1

But its not calculating the pool data.


----------



## OBP

OK, I have sent you a new version.


----------



## VANESSA1

Here is what in my head right now on the report format. You can also refer to the pooling spreasheet looking at lines 56 - 58.


----------



## OBP

Can you make it an XLS file?


----------



## VANESSA1

Lol I was way ahead of you


----------



## VANESSA1

Oops just saw that. resending


----------



## VANESSA1

Okay seems fine now


----------



## OBP

So you want to list the PhysLocations in the Summary and sub total them?


----------



## VANESSA1

Yeah, let's try it and see how it looks


----------



## OBP

What about the "NEW/CURRENT/CLOSED" status?


----------



## VANESSA1

The status is current


----------



## VANESSA1

I just thought about the sorting. It needs to be (1) Company name (2) Acquistion date from most recent to oldest


----------



## OBP

What about all the blanks in the database?
Do you only want to list those with asset Values?


----------



## VANESSA1

Okay some blanks can be due to if Form Type = 103-T. Is there a way I can get a listing of them? Sort of like an error/exception report.


----------



## OBP

At the moment the records have lots of blank data, with no assets listed, they are the ones that I am talking about.


----------



## VANESSA1

The ones that have no assets the form type should be 103-N. The ones I want on the report form type should be 103-O


----------



## OBP

OK, that has dramatically reduced the number of blanks, the list will be useful for you to see where the missing data is.


----------



## VANESSA1

Cool


----------



## OBP

Vanessa, here is wishing you a Healthy, Happy & Prosperous New Year.
Interesting about the Birthdays.


----------



## VANESSA1

Good Morning and same to you. sorry I was off friday. I'm taking a look at stuff now


----------



## VANESSA1

Okay, maybe its tuesday, me or both but when I click on either the summary on the main form or the report its not showing me anything in the data.


----------



## OBP

The report MailLoc Main gives me 129 pages.


----------



## VANESSA1

What are you clicking on. I'm still not coming up with anything


----------



## VANESSA1

I'm not seeing a report called MailLoc


----------



## OBP

Are you using PERSPROP v 0.2.mdb


----------



## VANESSA1

The version I'm looking at says Persprop v0.1zip that was sent on last friday


----------



## OBP

Like I said, the actual database inside should be PERSPROP v 0.2.mdb and it works for me, does your zip file have PERSPROP v 0.2.mdb?


----------



## VANESSA1

Okay I see it....extracting files...opening...looking at reports....still don't see it


----------



## VANESSA1

Okay I know its me but when I look at the details on the file, it has last modification date is December 30 2010 at 3:09 pm. Didn't you send me a later modification? I could have sworn I saw one modified on the 31st


----------



## VANESSA1

I'm sorry OBP. Its frustrating today. Can you send it again?


----------



## OBP

It is not you, it is the zipped copy I sent you, it is not the same as my copy.
I will send you my copy.


----------



## VANESSA1

I got it thank you!!!!


----------



## VANESSA1

Hey I got a situation. I have a new business that will not have a control number yet. There will be many of these in the near future. How can I enter into the database?


----------



## OBP

Open the tblMailLoc table in design view and set the Required property to No for the MID field.
Any records requiring a MID will appear at the top of the Find Combo.


----------



## VANESSA1

Which tab? I'm not seeing 'required' anywhere on the tabs.


----------



## OBP

It is in the table, with the table in design view, click on the MID field, it should be the 8th property down in the General Tab.


----------



## VANESSA1

Duh I found it


----------



## VANESSA1

Good Morning. How are you today?


----------



## OBP

OK and you?


----------



## VANESSA1

Speedtyping right now. How are report(s) coming?


----------



## OBP

Well, I was hoping for some feedback on the one that I sent you, I know it needed a lot of formatting, but was it the sort of data that you wanted?


----------



## VANESSA1

I'm sorry, I guess I misunderstood. Let me look at it again (a weekend has passed) and get back with you.


----------



## VANESSA1

Okay it looks good so far. Question. If I wanted to print a report for just one company, will that do it on this report?


----------



## OBP

We can use that report as the basis for printing one company, do you need to be able to do both?


----------



## VANESSA1

I don't think so. Usually they'll call wanting to know what was on the asset listing when I change what was orginally reported. So I think I'll only need would be able to print one company at a time.
Also, in looking at it, i noticed the main location printed on one page and the asset listings printed on another. Is there a way to get that information put together? I think I remember saying I wanted a page break for each company, but I probably communicated it wrong.

On another note, I'm already coming across this on my daily workday. Is there a way I can flag a company I have been in touch with and needs follow up. Normally I keep the information in my notes field. But I got a call the other day from a company I called and couldn't find the information I called about. Also, is there a way I can attach a PDF file?


----------



## OBP

I will provide the Report by the company on the Form and se what I can do about the format.

Yes you can set up flags and comments for each company or Location if you want. Access can list them for you each morning (or when you open the database) so that you know they require follow up, I have supplied that to others in the past.
You can "Link" to any file including pdf and display it as required if you want.


----------



## OBP

I have sent you the version that only prints the summary report for the Company on the form.


----------



## VANESSA1

I have a problem on one of my records. I think it was part of the test data when the database was first created. Its giving me that expression error again and not calculating pooldata. This is the first one I've entered today that has assets.

On the report, where would it show the mailloc information?


----------



## VANESSA1

I deleted the record and re-entered it and its okay now


----------



## VANESSA1

Hello there. Looking at the report(s), the Overall report has 96 pages of the physical location, the others are coming up blank. Is there something I'm missing?


----------



## OBP

If you open the mainform and go to record 19 (control number 09-2-00080) which has 4 phys locations each with an Asset, click the Summary button and you will get 1 page with those 4 records on it.


----------



## VANESSA1

Okay I went through the records and it looks like what i need. I see a few things. Some records where formtype = 103-O is not printing the summary information (see 11-2-00688;12-2-00074;12-2-00416;12-2-00551;12-2-01805;12-2-02570). I made it to record 530 before I hit a wrong button and reset it back to record 1. I also see typos on my part where the form type is blank. Is there a way I can get a listing of these so I can update them on my main database? I also so that I may need the same information on the record type 103-T. And is there a way I can tell it what record# I want it to go to?


----------



## VANESSA1

I'm just full of requests today. But this happened this morning. A taxpayer reported a set of assets in the wrong taxing jurisdiction. I need to delete from the wrong jurisdiction to put in correct jurisdiction. Is there an easier way than deleting all the information indivudally like a copy/paste?


----------



## OBP

Form types 103-O are Printing the Data, but for some obscure reason the assets start on page 2, 12-2-00074 has 9 pages and 11-2-00688 has 5 pages. When I have a bit more time I will try and correct that.
I have a form listing all the missing form types.
Remind me what denotes the Jurisdiction?

I am not sure what this means "And is there a way I can tell it what record# I want it to go to?"


----------



## OBP

I have sent you a new version.


----------



## VANESSA1

Taxing jurisdictions here are boundaries in the city that have different tax rates. Taxpayers are billed according to that particular tax rate and the state determines our budgets/levies/tax rates according to the amount of assessed value reported for each jurisdiction. My township has 4 of those jurisidictions. 

As far as the record search, I was on record #539 of 740 according to the bottom of the database. When I try to go back to that number, it just goes to the last record


----------



## VANESSA1

Okay, I'm looking at the Missing Form types tab. Does it go away when I put in the correct form type?


----------



## VANESSA1

Just answered my own question, entered a few and it refresh and they go away


----------



## OBP

Regarding the Jurisdiction, is that controlled by the main location? Should we have a field for it, so that you can use that?

That is odd about the record count, because it is normally accurate, I just put in 539 and it went to 539 OK.


----------



## VANESSA1

Okay the tab isn't showing up. Do i need to replace tblMailLoc with tblMailLoc1?


----------



## OBP

Yes.


----------



## VANESSA1

The jurisdiction is the first digits in the control number ( 09-11-12-13)


----------



## VANESSA1

When i tried to replace I get a message "You can't delete the table "tbleMailLoc' until its relationships to other tables have been deleted" Then it asks Do you want Microsoft Office Access to Delete the relationships now?


----------



## VANESSA1

I'm still trying the record count and its not doing it for me


----------



## VANESSA1

The Missing form types tab isn't showing up either on my copy


----------



## OBP

You should not be deleting the Table only the tblMailLoc Form.


----------



## VANESSA1

I have tblMailLoc1 form and tblMailLoc form.


----------



## VANESSA1

What could I be doing wrong?


----------



## OBP

Replace tblMailLoc form with tblMailLoc1 by removing the 1.


----------



## VANESSA1

Okay everything looks okay now. I don't know what I did


----------



## OBP

So to get back to your problem of tax district, would it mean changing the MID control numbers?


----------



## VANESSA1

No. For this partcular company the control number isn't wrong because they do have assets located in that jurisdiction. If I change the PID to the correct number, is there a way to move the items to the correct company/jurisdiction? I'm not sure if I'm being clear.


----------



## OBP

Providing the Link points to the MID control number, the relationships will be OK.
If changing the PID puts them in the correct jurisdiction then that should be OK too.


----------



## VANESSA1

Good Morning. Having my first sips of coffee trying to understand what you said. So, this company Dell financial 09-2-01142 reports Dumor Water 09-2-00195 which has 10 assets needing to be moved to the Dell MID number 11-2-00998 and the Dumor Water PID of 11-2-0636. So what would need to be done?


----------



## VANESSA1

Are we talking the Link autonumbers and the ID autonumbers?


----------



## VANESSA1

Good Morning. How is it going?


----------



## OBP

Good Morning, I haven't got any of those Mid or PID numbers in the database that I have (my PERSPROP v 0.4.mdb)


----------



## VANESSA1

Hmmm, well do I need to send my copy again? I would say it would be easier just to delete and retype every but unfortunately this is a recurring problem with leasing companies so I would like a procedure that would make it easy just for me to shift the assets


----------



## OBP

If you can send me a copy of the one with the problem I can take a look for you.


----------



## VANESSA1

Just sent a copy


----------



## VANESSA1

I think I figured out why some assets are showing up on page 2. I just got ready to enter a physical location under wrong jurisdiction and when I caught it, I just delete the information. But when I do a search I notice that the ID is blank for physical data but I don't know how to make it go away.


----------



## OBP

You could put "Awaiting Data" in the Name field, which would show up and can just be overwritten when you do get some data to put in.


----------



## VANESSA1

So when a report is done will that show up on the listing?


----------



## OBP

Yes, unless we tell it no to show it, but I am not sure if you will get a blank page unless we try it.


----------



## VANESSA1

Good Morning,
So you can print the report to suppress the blank and/or the "waiting for data" pages? Is there a report that can be ran to show these blanks?


----------



## OBP

Morning, can you give me the MID number where it needs a Waiting for Data?


----------



## VANESSA1

Okay, for some reason, I'm not finding it right now.

What about the other problems that were found?


----------



## OBP

I haven't had time yet.


----------



## VANESSA1

Okay that fine, I've had to kick it in high gear here. Starting on mailing for new personal property season yay


----------



## VANESSA1

Hey by the way, I'm off for a holiday for my birthday, what are you doing for yours?


----------



## OBP

I don't bother to do anything these days, I will be on here as usual, but Happy Birthday for Monday.


----------



## OBP

I have sent you an updated version.


----------



## VANESSA1

Hey this is WAY Cool! This will definitely help with the way these companies transfer assets around. So, what do i need to import?


----------



## OBP

The mainform tblMailLoc should do it.


----------



## VANESSA1

The zip file has the same name as a previous file. Do I just overwrite it?


----------



## VANESSA1

Good Morning, any answers on that zip file?


----------



## OBP

The old zip file can be deleted or renamed if you want to keep it.


----------



## VANESSA1

Okay everything is in place thanks


----------



## VANESSA1

Curious question. How does access sort the database? I have noticed when I data entering. I am on the F's and when I check the last record it gives me one I entered in the C's. Not logical to me.


----------



## OBP

When you Access, are you talking about in a Table or Form?


----------



## VANESSA1

When I am entering data into the form, the bottom record selector, when I tab to the last record it doesn't show the last record I entered


----------



## OBP

Ok, the Form is ordered by the ordering in the tblMailLocQuery
Mid
then by
MNAME
then by
MDBANAME
You can order it by whatever you want.


----------



## VANESSA1

Ahhh, Ok


----------



## OBP

Queries order from the lefthand side, where sorting is seelcted.


----------



## VANESSA1

Oh I see so I'm prbably being really stupid right now but how do I change the sort order?


----------



## OBP

What do you want to sort by?


----------



## VANESSA1

Nothing major but if I wanted to sort alpha on main company name, can I just go to create reports?


----------



## OBP

I don't know what "create reports" does, but I always do the dorting for Forms in the Query and sorting for Reports in the Report's Sorting & Grouping.


----------



## VANESSA1

How are the other reports coming?


----------



## OBP

Sorry, am I working on "other Reports"?


----------



## VANESSA1

We started talking about it way back at post #350. I'm sorry I guess it was one report. It was a report that I could type in the company name it would give me a summary of the PID/Pcompany asset information with the summary. It also would have the calculations as it was on the spreadsheet. I guess at the time you were working on the fomatting issues but I haven't heard anything else unless I missed something.


----------



## OBP

The last time we spoke about it, we discussed putting "no Data" in the blank records.
But I am not sure how you want it to look.
Have you ever "formatted" a report?


----------



## VANESSA1

Nope Ive never done any formatting. I'm thinking bout layout unless you have any suggestions. The only thing coming to mind is the excel spreadsheet I sent a little times back. I'm looking for the testcopy you sent me of the summary report.


----------



## OBP

It was not in the last working version that you sent me, but it is in the version 0.4 that I sent you and it is called MailLoc Main and the Subreport that goes on it is called OverallSummary and dated 10th January.


----------



## VANESSA1

There is a version sent on 1/11/11 that had new reports in it. Mailloc Main, Overall Summary and Physloc Main


----------



## VANESSA1

Are we talking the same one?


----------



## OBP

That should be the one, you should be able to open the Report from the mainform using the summary button.
The report needs tidying up.


----------



## VANESSA1

I'm sorry, I haven't imported any of this into my copy. Didn't want to mess with it because I'm not sure if any of it is tied up in the VBA.


----------



## VANESSA1

Okay nite nite


----------



## OBP

You can import the 2 reports and tghey should work Ok with the currrent mainform.


----------



## VANESSA1

Good Morning, I'll try it


----------



## VANESSA1

Okay everything came over okay but when I hit summary button the error message "The record source "tblMailLocMain" specified on this form or report does not exist."


----------



## OBP

You need the query of that name as well.


----------



## VANESSA1

There are several queries with similar names, which one(s)?


----------



## OBP

tblMailLoc Main for the main report and tblMailLoc Summary for the subreport.


----------



## VANESSA1

Good Morning,
I have everything up and running as far as reports. As far as formatting, remove the link & ID's. The ACQDATE needs to be from current to oldest; on the page I'm looking at more space is needed for ACQCOST. PNAME needs to be put portion needs to be alphabetical. The summary for each of those asset costs needs to be put in an format that adds them together and the ACQCOST sum and ASSESSEDVALUE sum needs to be compared for the 30% the same as the spreadsheet I first sent you, the final assessed value lines 56-58.


----------



## OBP

Good Morning, I will take a look at it when I have time, I am working on an old poster's database at the moment.


----------



## VANESSA1

It's okay. Still working on entering this a.m. and the mailing this afternoon. I wanna be an old poster


----------



## OBP

You aren't doing too bad this has been running 4 months now. The guy I am working with is from South Africa and I have been working on that database for about 3 years. The longest I have worked with someone is a guy in the US and it has now been on & off for 5 years.


----------



## VANESSA1

Lol I guess I'm just a young pup then


----------



## VANESSA1

But I do have a problem. That new transfer of assets tab? Well I'm trying to transfer a set of assets and its not finding the company with assets. Do I need to run an update? And if so, how?


----------



## VANESSA1

I got another situation. A company is under new ownership so their control number changed. However on the asset listings for several leasing companies its under the old control number. Is there a way to do a mass update of the control numbers (PID) with these assets?


----------



## OBP

Sorry I missed your previous post, did you resolve it?
I can build a similar one to the Asset transfer.


----------



## VANESSA1

Good Morning

Nothing solved as far as the asset transfer. What can I do?
As far as the new company, I guess sure, go for it.


----------



## OBP

Can you send me the latest version and the Company name?


----------



## VANESSA1

Okay


----------



## VANESSA1

Just sent you latest


----------



## VANESSA1

You do have the correct name of GE CAPITAL INFORMATION TECH SOLUTIONS. The name of the company that has the assets is GE Capital Commercial Inc 12-2-10679 with a PNAME of Oaklawn Psychiatric Services (PID)12-2-01183. These assets need to be transferred to GE CAPITAL INFORMATION TECH SOLUTIONS 12-2-01743 which has an asset already linked with the PName of Oaklawn Psychiatric Services. I just wanted to add the rest of the assets to that PID,


----------



## VANESSA1

The new entries that don't have control numbers should be status = New. These are going to be added to for mailing labels at a later date.


----------



## OBP

So 12-2-01183 - OAKLAWN PSYCHIATRIC CENTER INC needs transferring to 12-2-01743 GE CAPITAL INFORMATION TECH SOLUTIONS?
But there isn't a Physloc of 12-2-01743, only a Mailloc, which dosn't have the Assets.
You can't transfer assets from 12-2-01183 to 12-2-01183 which both Physloc Pnames of OAKLAWN PSYCHIATRIC SERVICES and OAKLAWN PSYCHIATRIC CENTER use.

As you can see I am confused.


----------



## VANESSA1

Okay its Friday. When I do a search on the 12-2-01743 and look at the physical locations P/L 54 of 54 ID & Link of 925 it says Oaklawn and has 1 asset Link 1755. 
Now when I search on 12-2-10679 and look at the physical location P/L 4 of 4 ID 924 & link 1736-1740 which is also Oaklawn. These are the assets hat need to be transferred to 12-2-01743 the P/L 54 of 54 starting with Link 1741.
Oaklawn Psychiatric Services and Oaklawn Psychiatric Center in are one in the same


----------



## VANESSA1

I'm getting ready to leave, we'll talk on Monday.


----------



## OBP

I have modified the Combo boxes to include the ID number of the Physloc company which you need to make the selection to make that transfer. I have transfered the Assets form ID 925 to ID 924.


----------



## VANESSA1

Happy Monday OBP
Okay, I've had a few sips of coffee and a couple of cookies. It looks like the transfer is backwards. The assets were supposed to go to 12-2-01743 not to 12-2-10679. Also, there are gaps between assets. Is this where I need to put that note at? Or can I just make it inactive for now?


----------



## OBP

The gaps between assets probably come from the Blank Record that you have in the Assets table for those locations.
You can just reverse the transfer.


----------



## VANESSA1

Okay, which updates to I need to brign to my version?


----------



## OBP

Just the tblMailLoc form.


----------



## VANESSA1

okay


----------



## VANESSA1

I did the update but I'm not seeing on mine what you have on yours with the ID numbers


----------



## OBP

Did you replace the tblMailLoc form with the Imported one?


----------



## VANESSA1

Duh its okay now


----------



## VANESSA1

Everything is okay now. On post #484 I talked about a company being under new ownership but reported under old PID number. You stated you could do something similar to the asset transfer.


----------



## OBP

OK, I will take a look, I assume we just need to re-assign Physlocations, so it would be similar to the Asset Transfer.


----------



## VANESSA1

Good Morning. We got a blizzard warning starting at 1pm today so don't know how long we'll be here if they decide to close down the county offices.


----------



## OBP

Hello, I haven't done anything today I have been Grandchildren sitting.
I hope the blizzard is not too bad, are you in for the "Ice rain" before the snow?


----------



## VANESSA1

Yep they say that's first then the snow. We were supposed to go out of town tomorrow for a class but that ain't gonna happen now. Supposed to get up to 20 inches of snow


----------



## OBP

What is the name of the Company that needs its Physical Locations moved from and where do they go to?


----------



## VANESSA1

Good Morning. Blizzard passed. They had a state of emergency yesterday so no one was on the roads except the plows. This morning I got stuck once and dug my way out so I'm here. Looking for that company now.


----------



## VANESSA1

Hey OPB. Can't find that company right now for some reason. So I guess we can get back to the report(s). There is one I would like to test before it gets too big. On the companies that have no control number and status = new I would like to do labels for them.


----------



## OBP

What do the labels need on them?


----------



## VANESSA1

Dba name, address1, City, State, Zip


----------



## OBP

Is taht the MNAME or MDBANAME?


----------



## VANESSA1

It's the MDBANAME


----------



## OBP

See the attached database, it has a Query & Report both called "New Companies with no CN".
Import them in to your latest database and open the Report.
I do not know what sort of labelling you are using, I have just set it up with 2 columns on a Letter page.
The heights, spacing and widths of the columns can be changed using the Print Preview Report "Setup". You can aslo change the Font Sizes as well.
Or you might want to create a report using one of the labelling setups like Avery labels. Unfortunately I have never used it.


----------



## VANESSA1

They came out fine


----------



## VANESSA1

Hey OPB, remember when we talked about putting some kind of flag(s) on a company that needed follow-up? I was wondering how to do that.


----------



## OBP

Hello bad penny .
Which Company Table, Main or Phys?


----------



## VANESSA1

Lol. I put the notes on the main table so I guess it would be there


----------



## OBP

OK I will take a look at the last one you sent me and get back to you tomorrow.


----------



## VANESSA1

Okay, Thanks


----------



## OBP

I have emailed you a new version of the database with the form in, whicvh opens when you open the Database.
If you want to use it in your database you will need to make a change to the tblMailLoc and import some queries, a form and a macro.


----------



## VANESSA1

Hey OPB I'm here. having problems with email software and the server. I'll look at it as soon as they can fix it.


----------



## VANESSA1

Okay I'm looking at it.


----------



## VANESSA1

What do I need to import?


----------



## OBP

First you need to add the "Follow-Up" Yes/No type field to the tblMainLoc table, then you need to import the
tblMainLoc Form
tblMailLoc Followup Form
tblMailLoc Main Query
tblMailLoc Followup Query
tblMailLoc Query
tblMailLocQuery
You may not need the last 2, but they do have the new field added to them.
Autoexec Macro


----------



## VANESSA1

I have 2 tables tblMailLoc and tbleMailLoc1. which table to I hadd the field to?


----------



## OBP

tblMailLoc, I am not sure what tbleMailLoc1 is doing. I would take a backup copy of the database and then delete it from the original to see if it makes any difference.


----------



## VANESSA1

Okay I'm glad I did a backup. I closed the database and opened it back up and the first thing that came up was the followup form. Got scared for a minute. Anyway I found everything but what do I do with the follow-up form? Do I need to add the field I created to the Main Form?


----------



## VANESSA1

Nevermind


----------



## OBP

He He. 
It must have been all that snow LOL.


----------



## VANESSA1

lol


----------



## VANESSA1

Question - does the followup form have its own tab cause I'm not seeing it. I see the yes/no box but where do I look for the ones I've checked?


----------



## VANESSA1

Search isn't working


----------



## OBP

The idea is that the Follow-up is ticked in the tblMainLoc form. The Follow-up form provides the list that have been ticked, they can be unticked either on there or on the tblMainLoc form. It does not have it's own tab at the moment, but it could.
What "Search" is not working?


----------



## VANESSA1

When I click on the tblMailLoc1 form and go to the search tab to look for a company, its not bringing up any results.


----------



## VANESSA1

Im kinda at a standstill right now


----------



## OBP

Is it working in the version that I sent you?
It is working in mine, although it didn't once when I pressed the Enter key.


----------



## VANESSA1

I'll check it


----------



## VANESSA1

Yeah it works in what you sent me


----------



## OBP

Try opening the database again and using the Tab key or clicking in the other text box.


----------



## VANESSA1

I tried all 3 fields none work hitting enter and/or the tab key


----------



## OBP

That is very strange, try importing the tblMailLoc form from my working version and open it manually.
You aren't going from the tblMailLoc Followup form using the "show" button are you, as that Filters the Records to just 1. Try clicking the "Show All" button top left of the tblMailLoc form.


----------



## OBP

I have to go out now, Granchildren sitting duties.


----------



## VANESSA1

I have 3 tblMailLoc(s) forms; tblMailLoc, tblMailLoc1, and tblMailLoc2. I thought I got rid of all of them but not I'm not sure


----------



## OBP

I would say that you should be using tblMailLoc2 has it got the new Follow-up field on it?


----------



## VANESSA1

Hello I thought you were gone for the day. Anyhoo, nope tblMailLoc1 has the follow-up field


----------



## OBP

Ok, does the Serach work on tblMailLoc1?
I have sneaked on here for a few minutes.


----------



## VANESSA1

It only works on the tblMailLoc but the Transfer company tab isn't on there or the follow-up field


----------



## VANESSA1

Sneaky little devil you


----------



## OBP

Can you send me the database at the end of work today and I will have a look and email it back to you before you start work in the morning.


----------



## VANESSA1

Okay cool. Hey I didn't want to do anything else today anyway. I got less than an hour before I'm outta here so I'll just play.


----------



## OBP

it should be working OK now, I have also added a Followup tab as well, so you can see the data at any time and a tab for opening the reports.


----------



## VANESSA1

Good Morning. Sorry I wasn't around yesterday. The car and my body is tired of this cold weather. I'll play around with it today.


----------



## VANESSA1

Hey OBP, I just inactivated some assets, but they are still showing up on the summary report. How can I fix to take them off?


----------



## OBP

How did you make them Inactive?
Did you set the NEW/CURRENT/CLOSED to "Closed"?


----------



## VANESSA1

These are the assets only not the company itself


----------



## OBP

Replace the tblMailLoc Summary Query with this one


----------



## VANESSA1

Cool it went away. On the report(s) I'm looking for a way to match the spreadsheet with the 30% of total cost and the final assessed value is the greater the 30% vs the total assessed value.


----------



## OBP

Is that a seperate Report, or on the currrent Reports?


----------



## VANESSA1

Can it be added to the current report?


----------



## VANESSA1

Question on the overall summary I noticed the numbers are different than what I had on my spreadsheet last year. Did the dates automatically update themselves?


----------



## OBP

I will have a look at the Excel sheet and see.

I am not sure why the numbers are different, the only thing I can suggest is that you compare some companies in detail and see what is different.


----------



## VANESSA1

Hey OPB, when I do a summary for form type = 103-T it is blank. Can this be updated like the 103-O?


----------



## OBP

How do you "ask" for 103-T Form Type?


----------



## VANESSA1

It has the same format type as the report for the asset listings which is type 103-O. The form type is on the Main Form.


----------



## OBP

So do you actually want to show the Report for whatever Form Type is showing on the Main Form?
Or should there be seperate reports, or have a selectable form type to choose the report?


----------



## VANESSA1

A selectable form type sounds good. I would like to keep the tooling separate from the other report


----------



## OBP

For now I have added another button to the Reports tab.
I have sent you a copy to try.


----------



## VANESSA1

Everything checks out. What do I need to import?


----------



## OBP

Queries
*tblMailLoc Main Tooling
tblMailLoc Tooling Summary*
Form
*tblMailLoc*
Reports
*MailLoc Tooling Main
Overall Tooling Summary*

PS Don't forget to replace the old tblMailLoc form


----------



## VANESSA1

Everything imported fine


----------



## VANESSA1

Hey OPB, I have a company that is closed but it had assets at six different locations. Is there a way to do a mass deactivation of this company and all its physical locations?


----------



## OBP

Yes you can use VBA or a Query to do it.


----------



## VANESSA1

Ok. This is one that has three jurisdictions. The numbers are 09-2-01131, 11-2-00055, 12-2-01573.
You should have this company in the latest file I sent to you.


----------



## OBP

What is the company number?


----------



## VANESSA1

I guess I'm confused by what you mean by company number. The links are 1205, 1207 & 1209. The name of the company is Ford Music & Vending


----------



## OBP

You said "Hey OPB, I have a company that is closed but it had assets at six different locations. Is there a way to do a mass deactivation of this company and all its physical locations?"
So I was expecting a Company with a MailLoc Control Number and 6 PhysLoc PIDs.


----------



## VANESSA1

Sorry to be so late getting back my email is screwed up. 
Control Number 09-2-01131 in the MailLoc has PID's of 09-2-01090. 
Control Number 11-2-00055 in the MailLoc has PID's of 11-2-00667; 11-2-00094; 11-2-00202; 11-2-00997 and 11-2-00106
Control Number 12-2-01573 in the MailLoc has PID's of 12-2-99994; 12-2-01220 and 12-2-01226

I hope this is what you are looking for


----------



## OBP

That is great, I will have a play around with a query and VBA and get back to you.


----------



## VANESSA1

Cool. Talk to ya later


----------



## OBP

Vanessa, would you want it added to the current Delete This Record Button?
Do you also actually want to delete the other table Records Linked to that Company?


----------



## OBP

Vanessa, ignore last question, the way that the Relationships are currently set up "Cascade Delete related Records" they will all be deleted anyway when you delete a MailLoc Company.
If you don't want that to happen you need to Untick that feature.


----------



## VANESSA1

Well the Company in the MainLoc will be deactivated. Only the PID Companies should be deleted. Don't know if I'm saying this right or not. The PID Companies are already in the MainLoc table and should remain active there


----------



## OBP

Sorry I mis-read your initial question. I susbstitued Delete for Deactivate.
So do you want to just deactivate the PIDS as well?


----------



## VANESSA1

Hmmm.....Well, it would make it historical data....yeah lets just deactivate them.


----------



## VANESSA1

Oh yeah, have you updated the pooling to reflect the dates of 3/2/10 to 3/1/11?


----------



## OBP

No, I shouldn't need I will just work on the de-activation.


----------



## OBP

Vanessa, you need to add an "INACTIVE PID" and an "INACTIVE DATE" field to the PhysLoc table.


----------



## OBP

Do you still need to show Inactivated MailLoc and PhysLoc records?


----------



## VANESSA1

Hi, remember when I asked about setting up a way to attach PDF files? Can we do that? I got a company I'm gathering up evidence with and I want a place to keep the documents.


----------



## VANESSA1

Sorry didn't see earlier posts


----------



## OBP

Yes you can have a table of PDF documents with their locations and open them at the click of a Button.

I have sent you a new version that Inactivates the PIDs associated with a Company, it works when you enter the Inactive Date.


----------



## VANESSA1

Glad I looked at your version. I had added the fields too. Anyhoo, in looking at updated version, I see the inactive fields along with a text field. What is the text field for?


----------



## OBP

One is a Yes/no tick box and the other is the date inactivated.


----------



## VANESSA1

Well I I'm kinda looking right at it. It has the Inactive Asset tick yes/no box, Inactive Date and a Text 36 field


----------



## VANESSA1

I should mentioned this is in the asset portion


----------



## OBP

Sorry, please delete that field on the Asstes form, it was me practising something else.


----------



## VANESSA1

lol..okay. So I what should I import?


----------



## OBP

Now your asking, I am not sure I can remember. As I changed the all three forms I would say 
tblMailLoc form
tblPhysLoc form
tblassetlist form

As long as you added the 2 fields to the physloc table it should work OK.


----------



## VANESSA1

Okay I added the two fields, I imported the three forms and its not working. I noticed in the forms sections there is a frmsmailloc in there. Would that have anything to do with it?


----------



## VANESSA1

Okay there is a bug in Private Sub INACTIVEDATE_AfterUpdate()
If Not IsNull (Me.INACTIVEDATE) Then
***DoCmd.OpenQuery "Set PID Inactive"***
Me.tblePhysLoc.Requery
End If
End Sub
The asteriked portion is where the code is highlighted


----------



## OBP

You also need to import the Set PID Inactive qyery as well.


----------



## VANESSA1

Okay first it gives me a parameter field and when I get out of it it takes me back to the bug I talked about earler.


----------



## VANESSA1

I also can't inactivate my main contact companies without the but popping up


----------



## OBP

Are your fields called
INACTIVE PID
INACTIVE DATE
as per the querynew


----------



## VANESSA1

I'll look at it


----------



## VANESSA1

I'm still coming up with the bug. so I'm Looking at the Query Design and I see it saying INACTIVE Date. Should I change it to look like this?


----------



## OBP

Yes, it needs to be the same as the query, which is strange, because mine says
INACTIVE DATE
copied directly from the query.
So maybe I have a different level to you.


----------



## VANESSA1

Okay I did that but now the inactive PID checkbox is grayed out and the INACTIVE Date has #Name? in the box


----------



## OBP

You ned to make sure that the form has the new names in the fields as well.


----------



## VANESSA1

I'm looking at the tblPhysLoc form in the design view. and it has the INACTIVE PID and INACTIVE Date in the fields. The INACTIVE PID checkbox has a green background and the INACTIVE Date has a green spot in teh upper left hand corner. Does that mean anything?


----------



## OBP

What are the names in the Form's "Field List"?


----------



## VANESSA1

INACTIVE PID and INACTIVE Date. I checked everything - tables queries and forms and they all say the same


----------



## OBP

Can you send it to me, or shall I send you the version that I have and you check the table name compared to mine and then import the query and forms again?


----------



## VANESSA1

I'll send it to you


----------



## VANESSA1

I sent it


----------



## OBP

Vanessa, sorry you also need to import the tblPhysLoc Query which supplies the Form with it's data, or open yours in design view and add the 2 new fields to it to make them available to the form.
I had forgotten how much I had changed.


----------



## VANESSA1

Okay, I'll look at it.


----------



## VANESSA1

Okay I have 2 queries - tblPhysLoc Query and tblPhysLoc Query1 which one?


----------



## VANESSA1

In addition one has the forms icon and one has the query icon


----------



## OBP

tblPhysLoc Query is the one.


----------



## VANESSA1

Okay where else do i have to add fields to?


----------



## VANESSA1

Okay it worls


----------



## VANESSA1

Question, once I inactvate them do they go away?


----------



## OBP

If you want them to.


----------



## VANESSA1

Okay, crazy day today. I did a search on Ford Music & Vending and deactivated all of their PID's. When I go back and look at the Physical locations tab nothing is there but its in the search tab.


----------



## OBP

Did you use my Query?
It has a 0 in the criteria for the "INACTIVE PID" which will prevent inactive ones from showing on the form.


----------



## VANESSA1

That's probably it then - no biggie just curious


----------



## OBP

If you want to see them, just remove the 0.


----------



## VANESSA1

Cool


----------



## VANESSA1

Hey can the pooling be adjusted to reflect current assessment year?


----------



## OBP

How do we do that?


----------



## VANESSA1

I'll send you a spreadsheet shortly showing the new dates


----------



## VANESSA1

Okay I'm struggling here. Its saying this one sheet exceeds file size, so I'll send it to your email


----------



## VANESSA1

I just sent to your emal.


----------



## VANESSA1

Good Morning, did you get email?


----------



## OBP

Yes, I will take a look.


----------



## OBP

Those dates and values are already set up in the table.


----------



## VANESSA1

Hmmm..let me test someting. New dates should reflect an assessment date of 3/1/2011 and prior for each pool.


----------



## VANESSA1

I just ran a test. When I put in an acquistion date between 3/2/10 to 3/1/11 - its not calculating a result.


----------



## OBP

Where did you put in the acquistion date between 3/2/10 to 3/1/11?
It is so long ago I can't remember how that part works


----------



## VANESSA1

Lol...The dates change every year with the most current date range at top. Just compare the spreadsheet sent to you before with the one I just sent to you. Hey that's why I like my job, always have to remember how I did stuff a year ago!


----------



## OBP

But those dates were already in the Pool and TTV tables, see the TTVOutput query.
The Assessed asset calculation is triggered automatically when you move to a record or add a new record, based on the current date.
Is that part not working?


----------



## VANESSA1

Okay it works on my test record. Don't know what the fluke was on that one but I need to ask something else. On records already entered, the numbers need to stay with that year. So if something was acquired in pool 2 between 3/2/09 and 3/1/10 it orginally calculated at 40%. This year that same cost should be calculated at 56%. So the numbers that are above the "Prior To..." should be added to that Prior To...


----------



## VANESSA1

Okay test record reveals no change between 3/2/10 to 3/1/11 and 3/2/09 to 3/1/10. First one should be at 40%, second one should be at 56% they are both calculating at 40%


----------



## OBP

Can you give me an actual PID/MID example to look at?


----------



## VANESSA1

Hmmm...take a look at MID 12-2-01743 and the PID's attached with them.


----------



## OBP

The first asset item for MID 12-2-01743 has Asset Value of $14,500.00 and Assesed Vlaue of $8,120.00 which is 56%.
Item 2 is $13,000.00 and $7280, which is 56%.


----------



## VANESSA1

Good Morning, They should be at 42% for this year.


----------



## VANESSA1

I just sent you an email of the spreadsheets showing the differences in the 2010 and 2011 assessed values.


----------



## OBP

OK, you need to open the TTV form and update the date periods and %age values in line with the pdf documant that you posted.

PS you need to add the new date periods to the Date Periods table first.


----------



## VANESSA1

Okay I'm looking at the Date Periods table and the only fields I'm seeing is DPID, Date Period From and Date Period To. Where do i update?


----------



## OBP

You need to add a date from march this year to march next year so that you can choose it for Pool 1.
You also need 01/01/1900 to march 2008 for prior to march 2008.
You also need 01/01/1900 to march 2005 for prior to march 2005.
You also need 01/01/1900 to march 2001 for prior to march 2001.
To get the values to update you need to step through the records of the Physloc form.

I suppose you really need something to update them all in one go.


----------



## OBP

You should write down what you have to do this year as you will need to do it next year.


----------



## VANESSA1

I'm really confused. You said to add new date periods to the Date periods table first. So, when I open that table, I see the fields I told you about. The date Period from and Date period to tables have a data type of Date/Time and that is all I'm seeing. There is nothing different in the fields properties below it either.


----------



## OBP

I have emailed you a copy of what I have done.


----------



## VANESSA1

Okay I'm sorry I really feel stupid right now. I didn't look at the datasheets of the tables themselves. Now I'm looking at what you were talking about.


----------



## VANESSA1

Okay I'm looking at your datasheet and you have things going to 3/2/xx. Can I change it to the 3/1/xx?


----------



## OBP

If you want, but be careful of the different date formats UK vs USA.


----------



## VANESSA1

Lol...I didn't see anything


----------



## VANESSA1

Good Morning, I have a question. I'm still looking at these date period tables. If the DPID is an autonumber, do I just add a new record with the dates that I need?


----------



## OBP

Yes, as I did.


----------



## VANESSA1

Okay that's done. what else?. I'm printing everyting I'm doing so I can keep it in a file for next year.


----------



## OBP

Now you open th TTV form and select the dates for the pools by basically moving each date down one space and introducing the latest date at the top.


----------



## VANESSA1

Okay done, Next?


----------



## OBP

Well if you go into the MailLoc form and then on to an Asset record it should update to the new value as mine did.
For this year you can just go through the Asset records but long term you really need some VBA to do it all at the click of a button.


----------



## VANESSA1

Cool. I was thinking about that. Is there an update query that can be done? There are quite a few assets not entered yet due to deadlines here. I want to get all the companies entered first and then go back and do their assets. I got alot of followups to do. I was thinking about the Overall Summary report. When some assets move into the 3/2/xx and prior category. The total costs needed to be added together and that total is multiplied by the TTV%.


----------



## OBP

I am not sure if an Update query can handle it, maybe with a function call it may be able to.
I will take a look at it.


----------



## VANESSA1

Okay


----------



## VANESSA1

Hey OBP. I have search issues. When I search on a company that has assets and I select a partcular PID company it does not go to that company in the PID. Is there a way to fix this? It's becoming cumbersome right now. I have a company with over 90 PID's and I have to select next/previous location just to get to the one I need.


----------



## OBP

Can you give me an example to look at?


----------



## VANESSA1

Okay if I do a search on GE Capital 12-2-01743 and click on Champion Mfg 12-2-00267 it goes to the first record instead of going to Champion.


----------



## OBP

If I enter Champion in the Search names field it goes straight to it.
So what way are you trying to do it?


----------



## VANESSA1

I think I'm probably communicating wrong. Do a search on GE Capital 12-2-1743 and go to the PID of Champion, when it says "go to this record" and its clicked on, it starts at the first record in that PID.


----------



## OBP

I have sent you a new version.


----------



## VANESSA1

Good Morning. I have tested the new version and its great! What do I need to import?


----------



## OBP

The Module called Module1
The Search Results Form
The tblMailLoc Form


----------



## VANESSA1

Cool. It works! Btw remember me asking about setting up an PDF? I got a few documents here that I would like to keep with a record. What would be your suggestion?


----------



## OBP

Create a "Documents" table with a DocID, Autonumber field, a DocAddress, Text field and a Comments, Memo field and a Link, Number - Long field.


----------



## VANESSA1

I guess I'm trying to understand the logic behind the fields. Can you explain them a little please? I'll work on the table.


----------



## VANESSA1

Okay the table is created


----------



## OBP

The DocID field is just to identify documents.
The DocAddress will hold the "Location of the PDF file"
The Link will link it to the MailLoc Company.


----------



## VANESSA1

Ah okay... a little slow this morning I guess...thanks


----------



## OBP

You now need a query and Form based on the table.


----------



## VANESSA1

What do you need me to do?


----------



## OBP

Well you could create them yourself


----------



## VANESSA1

Lol...oh yeah that'll go well...NOT!!!


----------



## OBP

Well you could send me a copy of the database at the end of work tonight and I will add them for you.


----------



## VANESSA1

Sure I can do that


----------



## VANESSA1

I just sent database. Let me know if you got it


----------



## VANESSA1

I thought about what I did after I got home last night. I didn't zip the file. Just sent a new one.


----------



## OBP

I sent you the original bakc with the mods.


----------



## VANESSA1

Question. So if I bring up a particular company and scan in the document will that document be attached to that one record?


----------



## OBP

Not immediately, you have to store the document first and then use tha browser that I supplied to assign it to the Company, or type in the path to where the document is stored..


----------



## VANESSA1

Okay, I think it did it.


----------



## OBP

Well if you click the view document button it should open the original document.


----------



## VANESSA1

Hey I'm trying to reformat (make look pretty) the MailLoc Report. In the design view when I go to the Overall Summary section, that area is so small where the ID header is I can't see any of the fields.


----------



## OBP

Open the Overall Suumary Report instead.


----------



## VANESSA1

overall summary is giving PID information not related to the company (MID) I'm trying to report on


----------



## OBP

I am confused now?


----------



## VANESSA1

Okay do a search on Reyna Capital. When you hit the summary button, it shows everything its supposed to but in an haphazard way, not the way to present to a taxpayer. So, if I want to make this report more presentable I thought I had to go to the indivudal reports themselves and format them. Is there a better way to do this?


----------



## OBP

OK, so which part do you want to change?
The fields in the Mname Header refer to the Company data and the Overall Summary Subform refers to the Assets data.


----------



## VANESSA1

Hey OPB I just thought about something with the PDF documents. The folder where we scan documents to is a temporary folder and will eventually be deleted. Do you have any suggestions for a more permanent solution?


----------



## OBP

I would put them in the same folder as the Database.


----------



## VANESSA1

I just sent you an email showing the format of how I would like the report to look.


----------



## OBP

What would it look like for multiple Assets?


----------



## VANESSA1

I'll work on that


----------



## VANESSA1

This is the basic format for multiple assets


----------



## OBP

And multiple Maddresses as well as multiple assets?


----------



## VANESSA1

Here is the format with multiple lessee's and assets.


----------



## OBP

OK, I will see what I can do.


----------



## VANESSA1

Question. I am in the process of printing labels for the new companies. Is there a way to change the design to print both address1 and address 2?


----------



## OBP

Yes, open the New Companies with no CN Query and add the Address2 to the query.
It is now available to be selected and added to the report.


----------



## VANESSA1

Good Morning. I'm looking at that query in design view and all boxes are checked. Am I supposed to be looking somewhere else?


----------



## OBP

Address2 is not one of the Fields. You need to drag it down from the Fields in the table above the rows of the Query.


----------



## VANESSA1

Okay, I can see that. I can do that. Where can I format how I want the labels to print the data?


----------



## OBP

That is in the Report in Design View.


----------



## VANESSA1

Thanks all done


----------



## VANESSA1

Good Morning, just looked at report and a couple of things. One of them I forgot, the other is mathematical issue. The Description of the asset needs to be added. It can go between Acqdate and cost. The final assessed value is wrong for each company. The final assessed value is the greater of the 30% or the total assessed value. It looks right in the Overall summary. Also looks like a size issue will come up so I can change the paper size to landscape.


----------



## OBP

The current reports use A4 format, I think that you are probably using "letter, so the reports need fine tuning for letter.
"It can go between Acqdate and cost. ", don't you mean between Lease Number and cost?


----------



## VANESSA1

Sure, the description can go there. As far as paper size, when I look at my printer properties, its set up as landscape.


----------



## OBP

Landscape is orientation, size is A4 & Letter etc.
I will send you another version.


----------



## VANESSA1

Okay


----------



## OBP

I have sent you another version, it is now set to Letter size paper.


----------



## VANESSA1

Got it. There are a few dates that didn't come out. It looks like dates with double digit months. Is there a easy way to fix this because there are so few?


----------



## VANESSA1

Other than that it looks really good


----------



## VANESSA1

What needs to be imported?


----------



## OBP

Open the report called OverallSummary in Design View and make the ACQDATE field slightly larger.
You need
MailLoc Main report
OverallSummary report
tblMailLoc Total report
tblMailLoc Summary query
tblMailLoc Total query


----------



## VANESSA1

Okay all done with that


----------



## VANESSA1

We'll get the bugs out of this yet. Now I have search issues again. I know that the search is set up on the MailLoc area. Is there a way to set up for it to search on the Physical location as well?


----------



## OBP

It will search on the Physical locations now, what is it not finding?
I just found the DUNLAP Branch Library by searching for DUNLAP.


----------



## VANESSA1

Good morning, its mostly addresses. Of course I can't think of one right now, but the situation did arise on a phone call I had last week.


----------



## OBP

You have to be very carefull with addresses because of the way users enter them, I just searched for and found 24197 COUNTY RD 16 by entering 
24197
or
24197 COUNTY
If I had entered 24197 COUNTY RD it would also have found it, but if I entered 24197 COUNTY ROAD it would not have found it.


----------



## VANESSA1

Most searches I do is usually by the first word, name, number of an address or a street name. What about wildcard searches?


----------



## OBP

They are LOL, that is how the query works, it finds anything with the search entry in the filed, where ever it is.


----------



## VANESSA1

Lol maybe I need some more coffee...I knew that


----------



## OBP

I was looking at creating you a "Town/City" table based on your Main & Phys tables when I relaized I recognised the names.
I have created 2 databases for someone else in you area.
On was fore the Belvidere & Boone Pantry.


----------



## VANESSA1

Looks like their in a different county and/or township. I can't access that information.


----------



## VANESSA1

Looks like its in Illinois when I google the information.


----------



## VANESSA1

Hey OPB, guess what? I can't think of anything else for this project except some buffs. Can you think of anything?


----------



## OBP

The town/city table would make entry more efficient.
You could also create a "Titles" table for contact's titles.
Maybe an Asset Description Table.
Possibly a Main Menu for those other tables.
At the moment the "Close Form" buttons just closes the form, it could close the database and Access instead.


----------



## VANESSA1

Okay, you're so good. I never would have thought about these things probably until later but it does get my wheels turning. On the asset report can a field be added to the footer showing the lessor's name and control number? 
And hey, lets run with your suggestions. I'm gonna start on my end to get this thing on a shared drive so my girls who help with Personal Property can have access to the information.


----------



## OBP

Is that on the Footer where the Final Assessed Value goes?

I would hold off putting in on the shared drive until I have created those other tables, as it means changes to the Main Tables which I would need to do "overnight".


----------



## VANESSA1

The IT department here is slow so this is something that isn't gonna happen right away anyway. I just want to get the request through.


----------



## OBP

OK, I will start work on the other tables, depending on my workload it shouldn't take more than a day or two.


----------



## OBP

What about "Is that on the Footer where the Final Assessed Value goes?"


----------



## OBP

Or is it the Last Page.


----------



## VANESSA1

I noticed on the bottom of the reports there are footers showing the date and the page numbers so I wanted to enter the company reporting on each page as well.


----------



## VANESSA1

Is there a way to email the reports to a company without printing the entire report first?


----------



## OBP

You can save the document and send it as an attachment without printing it at all.
However it may lose some of it's Formatting.
You can preserve the formatting using the free Access Snapshot Viewer and also send the viewer with the document.
You can also use PDF which I think Access 2007 may have built in.
You need to try those options to see which is best.


----------



## OBP

I have created a Town/city table and I have noticed the following problems.
I think these should all be "ELKHART"
EKHART
ELHART
ELKAHRT
ELKHAART
ELKHARTI
ELKHARTT
ELKHRT

And this
PARSIPANNY
PARSIPPANY
which I think should be PARSIPPANY.
Can you confirm these for me.

ps now you can see why I like Combo selection, at least all the spellings are the same LOL.


----------



## OBP

There are also
BREMEN
BREMENT
and
BRISOL
BRISTOL

ps I have checked and fixed these 2


----------



## VANESSA1

Geez, I type too fast for my own good. Can the ones that are showing up like this be corrected?
Also, did I answer the question about the footer?
I'll tinker with the report


----------



## VANESSA1

Okay trying this snapshop viewer but have no idea where its going.


----------



## VANESSA1

So far no luck in any of your suggestions


----------



## OBP

You can use this Query, import and run it and it will list the Cities in Alphabetical order.
You can change all the names that I listed in your copy of the table.


----------



## VANESSA1

Okay, done.


----------



## OBP

The location of the snapshot version of the report is controlled using VBA.
But you should be able to view with snapshot anything you have saved, all you have to do is to use the Explorer Search to find it.

I have modified the Search Form so that when you click the View Record button it goes to the Mailloc tab for that record.


----------



## VANESSA1

Good Morning. Hey I see that's cool


----------



## VANESSA1

Interesting, I'm able to email the reports as a pdf now


----------



## VANESSA1

But I want to get a little more specific. What if I want to only send one page of a report?


----------



## OBP

I don't know if you can as I don't have the pdf ability.


----------



## VANESSA1

Hmmm, things to ponder. I'll see what the IT people can do here.


----------



## VANESSA1

Hey OBP, I was showing off the database and one of my girls had a really good point to assist them. I think I need another report. I need a pooling report. the data ranges with the total cost for that partcular date and the calculated assessed value.


----------



## VANESSA1

Btw can I refer someone to you? I got a colleague who is a novice like me. His boss bought him the access software for something she needs.


----------



## VANESSA1

I'm just full of stuff today. But when I go to the followup tab and click to show. The physical location tab is showing up blank On companies I know I have entered PID and asset information on. For example If you go to Medtronics on the follow up tab, click show and look at the physical location, it is blank and it should not be. This is verified by doing a search on the same company and going to the physical location and the information is there.


----------



## OBP

Re the new Report, I need to know what data (fields from tables) they want in it.

Re your friend, he can post on here with e reference to your post abd I will pick it up.


----------



## VANESSA1

I'll send you an attachment of how it needs to look


----------



## OBP

Re MEDTRONIC USA INC I get 6 Assets in one Location listed using the Follow up or the Search??


----------



## VANESSA1

Hmmm, maybe its tempermental today.


----------



## VANESSA1

I just sent you an email with the report layout.


----------



## OBP

Ok, that will take me a while, I am working on 3 other databases at the moment.


----------



## VANESSA1

Lol. hey you're not superman today?


----------



## OBP

No I must be sitting near some Kryptonite,


----------



## VANESSA1

Hey I really didn't want to bother you today, but I'm still having search issues. If I do a search on US BANCORP 12-2-01140 and select the record for EARS NOSE AND THROAT ON 707 N WILDWOOD, when I select the record and go to the physical location, it shows a different company.


----------



## OBP

Sorry, I can't check that as I do not have any Record for EARS NOSE AND THROAT ON 707 N WILDWOOD.
The only record I have is for CENTRAL CHRISTIAN CHURCH.


----------



## VANESSA1

Well something weird is going on because I have over 30 different PID's with assets for this company.


----------



## OBP

Can I suggest that you send me your copy to work with?


----------



## VANESSA1

Do you want me to stop data entering?


----------



## OBP

No not for this test.


----------



## VANESSA1

Okay


----------



## OBP

We have a disconenct somewhere.
I have just gone to 12-2-01140 in your version and there is only one Physical Location and it is CENTRAL CHRISTIAN CHURCH, the same one I found in my version. It only has 2 Assets.
How are you finding "over 30 different PID's with assets for this company"?


----------



## VANESSA1

Let me send you a report


----------



## VANESSA1

I just emailed a report to you


----------



## OBP

I don't think you sent me the correct version, because the Report I get only has the 1 record.


----------



## VANESSA1

Hey, I'll research in am. I just had taxpayer from %^&* and I'm going home and have a drink


----------



## VANESSA1

Good Morning, about last week, I looked at the email I sent to you on Thursday. I opened the pdf file and it showing me 32 pages. Gonna be real busy this morning since I was out on Friday. I'll try to keep up the best I can.


----------



## OBP

Good morning. As I said you must have sent me an old version of the database, for one thing it didn't have the new report in it.


----------



## VANESSA1

Okay, let me try this again. I thought for sure I sent you my working copy.


----------



## VANESSA1

I just sent it.


----------



## VANESSA1

I see what you're talking about. I don't understand. When I send to a zipped file, I thought it would just copy, but what I think I'm seeing is It zipped my working copy. Does that sound right?


----------



## OBP

At some point you added all those records for that MailLoc to one of your databases, but which version?
You need to find that copy, or you will have to enter them all again.
I hope you haven't been doing too much data entry since.
It is possible to Cut & Paste the records from one table to another, rather than manually enter them again, if you can find the database with them in


----------



## VANESSA1

Okay, I'm looking at my working copy and I see all the stuff we did last week. I see what happened. I didn't move it to the correct drive. My working copy is on the C drive and what I sent you is on a different drive so it would make sense that you got the older version. Let me get things where they belong and we'll try it again


----------



## VANESSA1

Okay, I sent another one. I got a giant note on my screen now that tells me to send any newer copies to the correct drive.


----------



## OBP

The search finds it now with that version.


----------



## VANESSA1

Okay, its just weird to me cause if you click on the go to record, it goes to the first record. Could it be possible that since the Ears, Nose and Throat doesn't have a control number, it may be causing the problem?


----------



## VANESSA1

I think I see the problem. Remember when I asked you to fix the search? Well, when goes to that record, it shows P/Location 1 of 1 and its not showing the rest of the physical locations and its only showing the Add Location Button.


----------



## OBP

The Record does not have a PID number, the VBA code uses the PID number to identify the PhysLoc Record.


----------



## VANESSA1

Ahh...thought so.


----------



## VANESSA1

Here's another situation. I was entering some assets when I discovered that they were duplicates. So I went back and deactivated them. Now when I look at the summary to verify against what the taxpayer has reported, the overall summary is blank. This is a record I just updated so, any suggestions?


----------



## OBP

What happens if you uncheck the Deactive field, do they show up then?
Are there any duplicates?


----------



## VANESSA1

Good Morning, I guess I don't understand something with this partcular record. I still have the duplicate entries that I thought I deactivated yesterday and the overall report page is still blank. There is one PID in twice with this partcular MID so I need to deactivate one but not sure if that will solve the blank page problem.


----------



## VANESSA1

On another note, have you had a chance to work on that pooling report?


----------



## OBP

Good morning, no not yet, I may be able to do it tomorrow, I didn't manage to anything all day yesterday due to "other jobs", fitting Toilet seats and Looking after the Grand children.


----------



## VANESSA1

Okay, I'll just keep going here


----------



## VANESSA1

How are things going


----------



## OBP

I have sent you a new version with a Report.


----------



## VANESSA1

Hi, what do i need to import?


----------



## VANESSA1

I'm not understanding what you sent me.


----------



## VANESSA1

I think you sent me the wrong report


----------



## OBP

Sorry, I am not surprised that you are confused, I didn't just send you the wrong report, it is the wrong database.
My usual problem of having too many versions on my desktop and zipping the wrong one.
I will send you the correct one.


----------



## VANESSA1

Okay, It looks like I confused you too. I went back and looked at what I sent you as far as the format for the pooling report and it gave me more than I bargained for which means you got more than necessary. Let me reformat the spreadsheet and send you a better version.


----------



## VANESSA1

I just sent you an email. For some reason I'm having problems attaching PDF files to the forum


----------



## OBP

Funny, it took me about an hour to figure out how to get all of the Pools on the Report when they didn't have any data and you don't really want then anyway. 

You could try zipping the pdf file before posting on here.


----------



## OBP

So the Report is based on the MailLoc and not the PhysLoc Name?


----------



## VANESSA1

Using the MailLoc Name and the asset information if any is attached


----------



## VANESSA1

Hey who knows where I was mentally that day


----------



## OBP

Ok I based the first version of the report on the first example you gave, which was a Physical location.
I will modify it.


----------



## VANESSA1

Good morning, I got input from one of the people that do data entry for me and it looks good to them. So what do I import? I got a couple of companies that report in more than one pool and its not on your version but it is on mine so I would like to test that too.


----------



## OBP

I wish I could remember, I will take a look.


----------



## OBP

Queries
Pool Main Report
Report TTVOutput
TTV Output Totals
Reports
Pool Main Report
TTV
TTV Output Totals

The Headings still need a bit of tidying up on the Main Report.


----------



## VANESSA1

I imported what you told me to but the button isn't showing up on the reports tab


----------



## OBP

Sorry, forgot the Main form, tblMailLoc which has the button on.


----------



## VANESSA1

Lol


----------



## VANESSA1

Cool now I see it.


----------



## OBP

But does it work, or did I forget something else?


----------



## VANESSA1

testing it now


----------



## VANESSA1

Okay, there are a couple of issues. it looks like its doing each asset individually in each date range. What is needed is a total acq cost and total av for each date range. Not sure if I'm explaining clearly or not. Also, when I import to my working copy, I just get the top form with no pooling information.


----------



## OBP

Can you show me what does and what it should do?

To establish what I have missed try manually opening the Reports that I listed (with the correct mailloc record on the tblMailLoc form) and see which one does not show any data.
I may have missed a query.


----------



## VANESSA1

Okay I'm gonna have to print and paste what its doing and what i need it to do.

I went and compared your copy to mine, there were a few missed ones and i imported them, but it's still coming up blank


----------



## VANESSA1

Hope you got this


----------



## OBP

Ok, sent you a new version.


----------



## VANESSA1

Hey, I must have been really tired when I sent you that format. I thought about it later last night and this morning and yeah you did exactly which unfortunately was a little incorrect. Okay, the date range format is in the way it needs to be done. But when i said to total the assessed value that wasn't quite right. Its the total acq cost for that date range times the TTV%. when I multiplied the $286,682*56%TTV, I got $159,982.

The other issue seems to be that its only doing one PID per Mid. I need it to pull all the PID assets from all the PID's at one time. Believe it or not this is what I did manually on that excel spreadsheet. I would look at each acqdate and cost and put it on the appropriate line for all of the companies they were reporting.


----------



## OBP

What MID did you use?


----------



## VANESSA1

TOYOTA MOTOR CREDIT 12-2-01489. It has 7 PID's with their assets


----------



## OBP

You appear to have a major problem, you have 2 12-2-01489s.


----------



## VANESSA1

Okay..looking


----------



## VANESSA1

Okay it its a big issue..ummm...can I do a find and replace?


----------



## VANESSA1

The find and replace works The toyota number is supposed to be 12-2-01487


----------



## OBP

I think we need to set that field to "No Duplicates".

I am having an issue with the date formats mixing between UK and US formats, which is giving incorrect TTV%s in the query.
Can you tell me what date the MORGAN INSULATION - TOYOTA CLASS IV FORKLIFTS BELOW 8000 LBS asset's ACQDate actually is in your database?


----------



## VANESSA1

There are two assets for that location. 7/1/08 with a cost of 20,461.61 and 2/9/08 with a cost of 19,566.54

Yeah, on setting to no duplicates, but I can't remember right now why that was never done.


----------



## OBP

We can't set it to no duplicates because you have 140 duplicates in the table.
I will post a query so that you can see for yourself.
Can you tell me the month in those dates please, our formats are different?


----------



## OBP

Download, unzip, import and run this query.


----------



## VANESSA1

July 1, 2008 and February 9, 2008


----------



## VANESSA1

Okay, I'll print this datasheet and check stuff out


----------



## VANESSA1

Question, are the PID's in anyway connected to the MID's? because I one connected as PID


----------



## OBP

They aren't normally, perhaps you have the number in both tables?


----------



## VANESSA1

Yeah I do because it shows that the leasing company has assets at that location. But I didn't think they should be connected because I had to retype them everytime.


----------



## OBP

Most of the duplicates are complete duplicates ie the same company data, but a couple are actually different company names.


----------



## OBP

I have noticed that quite a few Assets do not have pool data, how do you establish the Pool?


----------



## VANESSA1

If form type on the mainform is = T (103-T) the assets will not have the pool data


----------



## OBP

OK, I am going to create a query to update all the Assessed values, as I think some of them may not be up to date.
You will be able to use it at the start of each finacial year to update them all in one go.
I have changed the Report query so that it uses the US Date format for the selection process, although I am not really sure if we need to or not.


----------



## VANESSA1

Okay, I'm working on those duplicates.


----------



## VANESSA1

Okay, here is a situation on the duplicates issue. I have assets tied to both links. Any suggestion on how to get them to one link? the Control Number is 11-2-00455


----------



## OBP

Do you think this is one where you have used the Transfer Assets, you could use that to transfer them to the first one (or most accurate/complete data wise) and then delete the other one.

Do only 103-O form types use the pool data for the TTV%?
I can do the Tooling one separately.


----------



## VANESSA1

Okay I'll try it


----------



## VANESSA1

I'll get the other duplicates done first. Been crazy today, putting out fires all day.


----------



## VANESSA1

Sorry I missed the other part of your earlier post. Yes only the 103-O's have the pool data for the TTV%. The 103-T are the ones with the 30% if its the current assessment year of 3/2/10 to 3/1/11 and anything else prior would be at 3%


----------



## VANESSA1

Good Morning, I went through the duplicates and all are fixed but 3.


----------



## OBP

Unfortunately to invoke the "No Duplicates" it would be necessary to get rid of those 3 as well.


----------



## VANESSA1

The problem is that the transfer assets tag only work with the PID Companies. I need entire PID Company info along with their assets, added to a MID on another link. So, I want to know how I can get the PIDs from link 2497 (Toyota Motor Credit Corp) added to the link 309. Pitney Bowes 11-2-01054 only has one that needs to be moved link 247 to link 2094.


----------



## VANESSA1

And I just found out the third one is a three way dup I'll get rid of two of them


----------



## OBP

Isn't that what the Transfer Physical locations is for?


----------



## VANESSA1

The problem is that one is that I cant tell which link is which, the duplicates show up on both sides. The link number is the difference.


----------



## VANESSA1

In the transfer of the Physical Locations tab, I type the control number of the physical location 11-2-00587 and company to receive physical locaiton 11-2-00455 I get the message "You must select the company with the Physical Locations first" When I click OK it moves me to transfer assets. So I put in the 11-2-00587 for company with the assets, but with the company to receive assets I put in the same control number and its coming up with the same link number. Fearful of executing this because I think its going to duplicate the assets. The toyota 11-2-00455 does not exist on the Comapny to receive assets


----------



## VANESSA1

Any suggestions?


----------



## OBP

I will take a look.


----------



## OBP

That is my fault, I didn't update the VBA when I copied the Asset version
Do you want to change the code or Import the Form?


----------



## OBP

Import & replace form tblMailLoc.


----------



## VANESSA1

Thanks


----------



## VANESSA1

Good Morning, I guess I'm confused with what the import is supposed to do.


----------



## OBP

It should have fixed the error message when transferring Physical Locations.


----------



## VANESSA1

Okay, let me check it out


----------



## VANESSA1

Okay, it asks the question "are you sure....", but how do I know I'm not duplicating data? The only way I know the difference visually for me is the Link ID's. Will access know this?


----------



## OBP

If you print them out doesn't that show you?


----------



## VANESSA1

Okay I'm confused. This is what I'm doing. In the process of trying to consolidate Toyota Motor Credit 11-2-00455 link 2497 to Toyota Motor Credit 11-2-00455 link 309. Where it says company with physical locations I put in 11-2-00455 and company to receive physical locations I put 11-2-00455. So I don't understand what you mean by print.


----------



## OBP

Well if you print them both out you will know if you have Duplicates, which is what you were worried about. Mind you, you could just transfer the data and then check for duplicates afterwards and delete any.


----------



## VANESSA1

Okay, I check both links and the PID locations are different. So, I guess I could re-enter the ones from one link to another, but I thought transferring would take care of that.


----------



## OBP

It will.


----------



## VANESSA1

OMG IT WORKED!!!!! I backed up the database just in case and to have something to compare end results. When I went to transfer locations I just moved the second Toyota to the first toyota because I figured they be in link order. I double checked against the backup file and all the PID's are there with the same MID link number except one blank and I'll delete it. It's amazing what happens when fear paralyzes you. Thanks again for your patience


----------



## OBP

Great.


----------



## VANESSA1

Okay I've done them all. Is there a way to double-check to make sure they're all gone?


----------



## OBP

Just run the Find Duplicates Query again.


----------



## VANESSA1

Alright, except for one company, all companies and assets for the previous year are entered. We are now ready for the current assessment year.


----------



## VANESSA1

Just did, all blank


----------



## OBP

Ok, you should now be able to go in to the Design View of the MailLoc table, click on the MID field and in the lower section where it says Indexed - Yes Duplicates OK change it to No Duplicates. That will prevent you or antone else from duplicting a MID.
We should really do the same exercise for PIDs in the Physloc table as well.


----------



## VANESSA1

Well, I don't know about the PID but I'm open to suggestions. They can repeat themselves in a way. For example, I can have several different MID's which have assets and the same PID. Like a MID Like ACCRA PAC 12-2-00012 can also be a PID for companies like IBM, GE Capital and Toyota. So, I can see looking for duplicates within an MID. Is this what you meant?


----------



## OBP

No I actually meant duplicate PIDs, I understand that you can have a MID that is also a PID, but PIDs should not be duplicated.
I can create a query to if there are any.


----------



## VANESSA1

Okay, just trying to understand. so we're talking PID's within a MID that should not be duplicated, right?


----------



## OBP

Right. Or another Company with Assets that has the same PID as a different company in a different MID.


----------



## OBP

You have 1221 duplicate PIDs.


----------



## VANESSA1

Okay, I'll work on that.


----------



## VANESSA1

Whoa..that's alot. Let me get it ran over here and take a look


----------



## OBP

Doewnload, unzip, Import & open the query in this database.

I have to go now.


----------



## VANESSA1

Okay ding-dong me thought I had it already. Waiting for you sir.


----------



## VANESSA1

Okay bye thanks


----------



## VANESSA1

This is for in the a.m. I ran it before I left for the day and it is as I thought. Those duplicate PID's are linked to different MID's. If you search on 09-2-00024 American Millwork shows up once as a MID. But AVI Foodsystems Inc has assets at that location and so does US Bancorp.


----------



## OBP

That is not good, there really should not be Duplicates, if you can have a Physical Location with more than one MailLoc company or another Physical location with Assets at that original one it suggests that the Table Structure is not correct.
But if you are happy to live with it you can, but it could well lead to confusion later.


----------



## OBP

I douldn't resist adding this post as it brings up our 900th communication on this thread.


----------



## VANESSA1

Awww:up:


----------



## VANESSA1

In response to the 900th post, this whole process has evolved over the last seven years. It all started on excel, then went to outlook, then to access. I've got a way better handle on it than I ever have thanks to you.


----------



## OBP

Well there has also been 10,581 views of the thread, so hopefully some others have picked up some tips along the way.

So what are we going to do about the PhysLoc table?


----------



## VANESSA1

Well, its doing everything I need it to do right now. The only thing I can think of is something like if their is a duplicate PID within one MID...don't know if I'm saying this right.


----------



## OBP

What I can't understand at the moment is why there needs to be duplicates.
Perhaps you could explain a bit more about the this "If you search on 09-2-00024 American Millwork shows up once as a MID. But AVI Foodsystems Inc has assets at that location and so does US Bancorp."
Is AVI a PID and is US Bancorp another PID?
Are they duplicated PID numbers?


----------



## VANESSA1

No AVI and US Bancorp MID's only. They are one of many out of state leasing companies who rent equipment to the companies that show up in the PID's.


----------



## VANESSA1

By the way, I got a leasing company in for the current assessment year. I updated the asset information and when I went to do an overall summary, the final page is blank


----------



## OBP

OK with the Mainform open on that record go to the Queries and open the "tblMailLoc Total" query and see if it has any values. That is the query that supplies the Last page report.


----------



## VANESSA1

Yes there are numbers there. But I did notice that there are some blank numbers and also the link id is not on this one. Is there a way to rectify this?


----------



## OBP

If the Link not there, there is something wrong with the data. The query lists all the data for all Links that have Assetlist data and the Report only shows your PID/Link due to the Report's Master/Child Links


----------



## VANESSA1

When I look at that record and the assets, the individual companies are adding up alright. Is there a report that can be ran so I can take a look at those links?


----------



## OBP

I don't quite understand that as the tblMailLoc Summary query uses the same setup as the "tblMailLoc Total" query, so they should both have the same data, one has the records and the other just has the Totals.
If you run tblMailLoc Summary query do you get the Link showing up?
You may have to send me the database.


----------



## VANESSA1

Okay, let me run the query


----------



## VANESSA1

Hey I ran the tblMailLoc Summary query and found the links


----------



## VANESSA1

I was thinking about sending you the database anyway since I got rid of the duplicates in the MID's


----------



## OBP

OK, I will take a look.


----------



## OBP

You didn't say which company?


----------



## VANESSA1

Airgas Great Lakes Inc 12-2-00264


----------



## OBP

Have you made any changes in the database?


----------



## VANESSA1

Just what you sent me to update. I haven't done any data entry since I sent it to you.


----------



## OBP

Ok, I have found an of issues with your version, which I have fixed, somehow the tblAssetList Pool field was set to "Text" in your version, when it needed to be a Number. Thta wasn't affecting your Summary report but was preventing your Pool report from working properly.
The problem with the Summary last page was the test I had on the Inactive Asset, I was checking for the value 0 which means "Unticked", but you have records where the there is no value, no 0 or -1. So I have changed the query to check for those as well. I will mail it back to you shortly.


----------



## VANESSA1

Okay thanks


----------



## VANESSA1

Hey, I had to go back a few posts but I found what was being worked on when the other issues popped up. Post #835 about the Pooling Report, post #854 yearly update query and post #910 blanks in one of the other queries.


----------



## OBP

So is it working Ok for you now?


----------



## VANESSA1

I went and checked a few of the companies and the overall summary looks good. The pooling report still needs work.


----------



## OBP

I thought I had fixed that.


----------



## VANESSA1

Nope, its still doing only one PID and each asset listing individually for that date range. It needs to pull from all PIDS acq costs within a given date range and be multiplied by the given TTV% for the assessed value


----------



## OBP

It shouldn't need to be multiplied by the TTV%, because it should already be calculated and in the table.
Can you give me an example?


----------



## VANESSA1

That will take time. I will put a spreadsheet together for Toyota Motor Credit 12-2-01487


----------



## OBP

I have sent you an updated version.


----------



## VANESSA1

I got it. And yeah you did do it. I went back and did it manually and our numbers agree.


----------



## VANESSA1

I won't be in tomorrow for the Good Friday holiday. I'll talk with you Monday


----------



## VANESSA1

Good morning, I think I remember you saying that the PID data looks at the control number only. Is there a way to modify it on the search that it looks at the PNAME also? I would like to change a address but I can't access the record. the MID is 09-2-00306 Hawkins Water Tech Inc. The PID is Timindy Builders ID 1117


----------



## VANESSA1

There's an issue with the pooling report. Its calculating twice on the 3/2/10 - 3/1/11. The first one needs to be removed.


----------



## VANESSA1

Nevermind on that


----------



## VANESSA1

This is weird. When I run the overall summary the numbers agree with what the taxpayer is reporting. However, when I run the pooling report, its off by $4,915.08. Could it be possible that it's calculating the assets that have been deactivated?


----------



## OBP

post #934, if you search for a name is searches bot the mail and phys tables.

post #937, what Mid/PID


----------



## VANESSA1

Post #934 - It brings up the search, but it you click go to that record, it comes up blank. The problem is that this is a new company and it doesn't have a control number yet.

Post 937 - MID 12-2-02654 and PID (active) 12-2-00366 CTS Corporation and (inactive) 12-2-00165 Family and Children's Center Inc.


----------



## OBP

For post 937 Unzip the attached database, Import and replace the TTVOutput Query and the TTVOutput Totals query.

For post 934 have you considered giving them temporary PID numbers?


----------



## VANESSA1

Yeah I think I could do that. Just have to consider a numbering system that won't clash with the main system here. Give me a few minutes to think about it a little.


----------



## OBP

How about Temp001, that is obviously not a correct PID.


----------



## VANESSA1

That would work depending on how the field is defined. If it's all numeric I'm thinking more of
12-1-11111 along those lines keeping the taxing district intact.


----------



## VANESSA1

Okay I imported the file. On the Pooling Report its still calculating differently. Could it be that on the pooling schedule the 3/2/10 to 3/1/11 is calculating twice? I see that the top two are identical. The first one needs to be eliminated.


----------



## OBP

Did you delete the old versions?


----------



## VANESSA1

Nope


----------



## OBP

You need to use the new queries.


----------



## VANESSA1

The version I'm using is the latest you sent back to me after I sent to you without the duplicates.


----------



## VANESSA1

Hey, I'll be out of the office until Wednesday. Talk with you later


----------



## OBP

Yes, but you need to use the 2 new queries that I sent you instead of the old ones.


----------



## VANESSA1

Good Mornig, I imported and replaced the queries you sent me. Do I need to run an update?


----------



## VANESSA1

Okay I calculated manually and its somewhere in the pooling on line 20 at the 40%. The Total Cost is supposed to be $9721 and the Assessed value is $3889.


----------



## VANESSA1

I seem to have the same problem with the 42% line 22 pool 2. On Ervin Leasing Co 13-2-00299 after manually calculating 10,313*42% shoudl equal 4332. The report is giving a result of 5775.55.


----------



## OBP

Can you send me the database?


----------



## VANESSA1

Good, Morning. I thought about that after I got home last night. Yeah, I'll get right on it.


----------



## VANESSA1

I just sent it.


----------



## OBP

Morning, what MID did you work with?


----------



## VANESSA1

A couple of them; 13-2-00299 Ervin Leasing Company and Adams Remco 12-2-02654


----------



## VANESSA1

Nope the control/parcel number is created by the main computer here or I create a fiticious one for exempt/muncipalities. There is no link on the server you are talking about. Why?


----------



## VANESSA1

Okay I know I wasn't seeing things. and I'm also looking at my email. What just happened with that post I just got? And it wasn't from OPB it was from a ningdezs


----------



## OBP

I think ningdezs was guilty of spamming.
I unzipped and opened the database that you sent and entered 13-2-00299 Ervin Leasing Company and got 4332.
See attached printscreens.


----------



## VANESSA1

Okay, this is weird, this one is okay now. It did not do this yesterday. What about the Adams Remco?


----------



## OBP

What value should it be?


----------



## VANESSA1

When I manually calculate it, it comes to total cost $44,294 and the av total was 14,017. The overall report has $44,253.04 and a total AV of 14,000.50. The pooling report has total cost of $59,726.91 and a total AV of 18,915.58


----------



## VANESSA1

Okay this is driving me batty. Take a look at this one. I deactivated a couple of assets on this one. and its still is getting numbers from somewhere


----------



## OBP

I agree something odd is going on, leave it with me.


----------



## VANESSA1

Hey, on a lighter note, have you been invited to the royal festivities?


----------



## OBP

For some weird reason when Access adds the Totals (grouping/summing etc) it is duplicating some records and not others. whereas when it just lists the records it is not. I have split the query, so that it lists them and then groups them in a second query, which seems to be working OK. I listed the Adams Remco output and put it in to Excel and it calculates the total as $43,365.00, I now have the Query outputting $43,365.00 as well.
So I am not sure if your $44,294 is correct or not, perhaps there is a small error on the input of data to the table?

I will look a bit more and get back to you.


----------



## OBP

No I haven't been invited.


----------



## OBP

OK, I have had to add the leasenumber to the TTV Output query as it was loosing records where the Asset Number and the Value was the same in more than one record.
I now have agreement between the Main Summary and the Pool Summary
The actual total cost for Adams Remco is the $44,253.04 from the Main Summary report.
So what numbers were you using for your "manual" check?


----------



## VANESSA1

It could have been a typo on my part I can double check that. But I have another one. Toyota 09-2-01462. It only has one PID and one asset. The Pooling report looks okay but the overall summary is blank. I was in the midst of sending this one to a taxpayer.


----------



## OBP

It is a Form Type 103-N, we did not cater for that, only 103-O


----------



## VANESSA1

Oops


----------



## VANESSA1

That's intersting. Does the pooling report generate for any company that has assets?


----------



## OBP

Yes I think it does at the moment.


----------



## OBP

Should we make it NOT 103-T ?


----------



## VANESSA1

Yeah it was a typo on my part the numbers agree now.


----------



## VANESSA1

I don't see it presenting a problem and it has a future potential so I'd like to leave it as is.


----------



## OBP

So, do we add 103-N to the query so that you can run that company, or should it have been a 103-O?


----------



## OBP

Also what do you want to do about updating your version, I have mad e a few changes?


----------



## VANESSA1

It should be an 103-O. When I changed it everything came out okay


----------



## VANESSA1

I made sure that I didn't enter anything new, so you can send it back to me. Believe me I've got enough to do for this time of year for me.


----------



## OBP

OK, will do.


----------



## VANESSA1

Its cool I'll change that it.


----------



## VANESSA1

Okay I got it. I'm gonna work on the returns I got in (Adams Remco and Ervin Leasing) to see how its working.


----------



## VANESSA1

Okay this makes no sense. This is what you just sent me


----------



## OBP

New one.


----------



## VANESSA1

Okay, lets try this again


----------



## VANESSA1

This is what I'm seeing. Getting ready to go home. I'll talk with you in am


----------



## OBP

OK, I think I have finally got all the Totals correct in the version I am sending you.
I have also added a new Tab called "Update Assessed Values", which has a command button that runs a query to update all the Current 103-O records Assessed Values with the current years TTV%.


----------



## VANESSA1

Good morning, I'm here. Do you still want me to unzip the newer version or just wait?


----------



## OBP

You might as well wait, I shouldn't be too long.


----------



## VANESSA1

Okay, you know what's weird about all of this is that its working for some of my companies great and others not.


----------



## OBP

I know, frustrating isn't it.
Anyway I have tested it first this time, so I know the update is working and it has been done for this year and you shouldn't need it until next year.
I will send it to you now.


----------



## VANESSA1

Hey I jut opened it and I'm not seeing anything but queries in the navigation window. Is this what I'm supposed to be seeing


----------



## OBP

No the usual forms should open, they do on my version.


----------



## VANESSA1

Okay I see, you just had the queries only selected instead of all objects. Okay, let me play with those problem ones to see if we have results


----------



## OBP

I will keep my fingers crossed


----------



## OBP

One more post to get the 1000 up.


----------



## VANESSA1

Well, Adams Remco is good so done with that one. However, Ervin Leasing both control numbers I see that the overall summary report is blank. But I'm also seeing on the pooling report numbers showing up in all pools when all the assets are in pool 2. So hey, sorry here we go again.


----------



## VANESSA1

Okay I hit the bit one


----------



## OBP

I get a blank Main Summary, but the Pool Report is OK???


----------



## OBP

OK, that was me testing the Queries, open the tblMailLoc Summary query and delete the 87 in the Link column criteria row.


----------



## VANESSA1

Do I need to run it again


----------



## OBP

No just open the Reports after saving the query.


----------



## VANESSA1

okay


----------



## VANESSA1

Overall Summary still blank. Pooling report should only be reporting in Pool 2 and its reporting in all pools. I checked the asset listing and all assets are in pool 2


----------



## OBP

What version did I send you?


----------



## VANESSA1

Lol alot to keep up with today. My email shows PERSPROPv0.44 sent at 8:54am 735 KB


----------



## OBP

That is the one, I am now getting a completely different error since I posted last, the "tblMailLoc Total" query has decided to stop working all together.
There is sometning weird going on here.


----------



## VANESSA1

Lol. Just great. Fickled piece of software isn't it?


----------



## OBP

Ok I have fixed it, shall I send you a new one or instruct you?
I don't know how you are getting more than 1 pool, as I only get pool 2.


----------



## VANESSA1

Okay, send me a new one. If I get that pooling problem again I'll send you what I got.


----------



## OBP

I have sent you the new one V 1.0 and I get both Companies with both reports OK.


----------



## VANESSA1

This is what I'm seeing


----------



## OBP

Interesting you are using ID 642 for Ervin Leasing Company and get more than one page I am using 13-2-00299 Ervin Leasing Company which is ID 1103 and only has one page.
So how are you getting ID 642?


----------



## OBP

You are using 12-2-00512 Ervin Leasing Company!!!!!!!!


----------



## VANESSA1

Oh I see. We're looking at two different reports. Yeah the ID 1103 looks like its running fine but the 642 is what is all over the place


----------



## OBP

Have you checked out 642 manually?
Why is there 2?


----------



## VANESSA1

I'll check it manually. But there are two because they are in two taxing jurisdictions. If you look at the control numbers one starts with 12 and the other starts with 13. I have 4 to keep track of.


----------



## OBP

Got you, I forgot the tax jurisdictions.


----------



## VANESSA1

I'm sorry, I did make a mistake. There were assets in Pool 3 and Pool 4 for 2010 but they are deactivated in 2011. So I deactivated them, re-ran the pooling report. Pool 3 and 4 with dates are coming up but with no values.


----------



## OBP

Well that is progress.
I will try and do the same and see if I can get rid of the empty pools.


----------



## OBP

Ok, unzip and and then import the Query and Report, don't forget to delete your ones with the same name first, or remove the 1 from the names of the new to replace the old ones.
The report isn't essential, I just moved the Heading up slightly, it is the query that now gives all 3 Company's data.


----------



## VANESSA1

Okay now its my turn to get confused on versions. Which version is this one going to?


----------



## VANESSA1

The last one I got is the PERSROP v 1.0 sent at 10:24AM


----------



## OBP

To version v 1.0.


----------



## VANESSA1

ALRIGHT IT IS DONE!!! Looks good. I say we celebrate


----------



## OBP

No way, there is bound to be something else. 
But it is a minor success, so a small celebration. :up:


----------



## VANESSA1

Okay just a small one then


----------



## VANESSA1

Okay, refocusing elsewhere. Remember the companies that have no control number? Is there a report I can import that I can run a list? I want to give temporary numbers until they have valid ones.


----------



## OBP

No PID?


----------



## VANESSA1

Good question, the report would consist of no MID and PID.


----------



## OBP

I think seperate queries listing MailLoc with MID and another for Physloc with no PID.


----------



## VANESSA1

Sure that will work


----------



## OBP

Ok, here they are, you need to Import the 2 queries and the new tblMainLoc form. Delete your form first so that the new one replaces it.
It has 2 new buttons on the Reports Tab.
Both queries are "Updatable", meaning you can add the MIDs & PIDs in the query.


----------



## VANESSA1

Okay hey I'll work on this but its been a long day for the both of us, you longer than me. I'll call it a day and talk with you on Monday.


----------



## OBP

OK, see you.


----------



## VANESSA1

Hey OPB, I know you probably told me this before. Is there a way to show the inactive PID's. I think I may have shut off the wrong one.


----------



## OBP

You use the Query in the attached database.


----------



## VANESSA1

Good Morning, here we go again. Okay Dell Financial Services 11-2-00998. I inactivated several PID's and their assets. However, when I run the summary, those inactivated ones are showing up.When I look in the physical locations tab there is nothing there for the ones that I inactivated. 

Btw I know the control numbers are wrong on this one. But I have added so many new fictitious numbers I don't know the last one I used. Is there a way I can get a list or something with the largest control number in each taxing jurisdiction?


----------



## VANESSA1

And it continues. Grab-N-Go Vending 12-2-10832. The overall summary has the correct value, but the pooling looks like it only pulled one company's assets. There are two companies with assets as the overall summary says.


----------



## VANESSA1

Okay, this is popping up too frequently. Definitely issues. When the above posts get solved maybe it'll fix the rest.


----------



## VANESSA1

Sorry, I hate to be a pain, but I'm kinda stuck where I'm at right now.


----------



## VANESSA1

I sent my lastest version to you.


----------



## OBP

Sorry I have had a Laptop hard drive failure, so I have been off line for a couple of days. When I get my Mail working I will take a look.


----------



## OBP

I have sent you a new version to look at.


----------



## VANESSA1

It looks like I need to inactivate the assets again on your version. Is that what I need to do?


----------



## OBP

Sorry, I don't know what you mean by "My copy", since the disk drive failure I only have the copy that you sent me last week, re post #1045.


----------



## VANESSA1

Okay I deactivated the assets on the version you sent me and it works. What do I need to import?


----------



## VANESSA1

Omg, I'm sorry, didn't think about that. Anyway, it works on the one you sent me. So I'll use this one and delete the old one.


----------



## OBP

That's a good question.
Queries
TTV Output Query
tblMailLoc Summary
Reports
MailLoc Main
OverallSummary
TTV

As I can't remember what I changed I have used the "Last Modified" date to select those items and for some reason I also changed the Documents Query as well.
Don't forget to delete your copies of those items.


----------



## VANESSA1

It looks like all problems have been resolved. Thanks again


----------



## OBP

Until next time


----------



## VANESSA1

Hey OPB on that Inactive PID query, do I need to run it to get it to show the company I need to reactivate?


----------



## OBP

Yes, it should show any inactive PIDs, you can then untick them in the query and that will update the tabel.


----------



## VANESSA1

OPB, can I modify the Summary report? I would like an address for the lessee's. So the heading would be lessee name, address and parcel number.


----------



## OBP

Yes of course you can modify the Report.
Or do you mean can I modify the Report?


----------



## OBP

Import the query and report from this database and replace the ones in yours.


----------



## VANESSA1

That's perfect. Thank you


----------



## VANESSA1

See I would have modified the report itself and never thought about the query


----------



## OBP

That was why I did it, the address fields were not available on the Report design as they weren't in the Query which would have had you confused.


----------



## VANESSA1

I'm looking for a report for the Form Type = 103-T. Similar to the overal summary report that's there now. This one will have no pooling report


----------



## OBP

Can you show me what it would look like?


----------



## VANESSA1

It would be the same as the Overall Summary Report. Just the form type = 103-T


----------



## OBP

But what about the "No Pooling Report"?
What do you use instead?


----------



## VANESSA1

I would use the overall summary as the comparison. Those numbers are entered into the main system differently than the rest.


----------



## VANESSA1

Hello there, I know its been awhile but I'm am in the middle of the tax season. The database is working great. I just have a problem with an error that's come up because of my data entry. I'm looking at a return that has many physical locations, and yes I found a duplicate location. The assets are different so they need to be put under one. 12-2-10838 is the MID and 12-2-00140 is the PID. The ID numbers for te PID is 2033 and 2035. I would like to get rid of the ID 2035. But for some reason it when I inactivate the 2035. Now what's interesting is that when I try to go to that record with ID 2035 when I look at it it has ID and link of 2033. So, I'm confused. Hope I explained things okay


----------



## OBP

What does 2033 have?


----------



## VANESSA1

Its the same physical location but a different asset.

On another note, another phase to my season. I need labels on companies whose status = NEW. I know we did labels with no C/N well that's changed since I added temporary numbers in the database. So is there a way to update that report?


----------



## OBP

If you are realy concerned about the Duplicate you can always go in to the table and Delete the one that you don't want.
Yes the Report could be modified to show the Status of New.


----------



## OBP

Open the Query called "New Companies with no CN" in design view and delete the "Is Null" in the MID Column, save the query.
You can then open the "New Companies with no CN" Report in design view and change the Heading.
Open the Mail Location Form in design view on the Reports tab and click on the "Labels New Companies with no CN" and change the name by removing the with o CN part.
You can do the same for the "New Companies with no CN" button as well.


----------



## VANESSA1

Cool, Got it done thank you


----------



## OBP

Vanessa, well done.


----------



## VANESSA1

Hey OPB, long time no talk  Okay we're update the assets for the year. The problem I'm running across is that I think the overall summary report is cutting short the asset number. Do I just need to go to the design of the report and modify it? When it prints landscape, I have plenty of room.


----------



## VANESSA1

Okay I just tried to do it on my own (I didn't save it) and the report was messed up.


----------



## OBP

So do you still need help?


----------



## VANESSA1

Good Morning OPB yes I do. Just got here. let me open database and get the company info.


----------



## VANESSA1

The company is Abbott Laboratories 12-2-00008. The asset numbers are quite long and its hard to match up what the taxpayer is reporting this year versus what they reported last year if I can't verify the full number. I know its a spacing problem in the design but when I try to do it, the results aren't looking well. :-(


----------



## OBP

Here is the Overall Summary report with wider asset number fields.
Don't forget to delete your old one.


----------



## VANESSA1

Hey got another situation. 09-2-00058 ICEE USA INC. I am noticing the the overall summary and pooling report is not matching.


----------



## OBP

Can you email me the database?


----------



## VANESSA1

Okay, I'll send it before I leave tonight


----------



## VANESSA1

Situation. CHAMPION MFG


----------



## VANESSA1

Nevermind


----------



## VANESSA1

I think the dates on the tooling reports need to updated. How do I do that?


----------



## OBP

Have we entered a Time Warp?
See this section of the Thread
http://forums.techguy.org/business-applications/954425-customizing-primary-key-45.html


----------



## VANESSA1

Lol. Yes I'm here on a saturday. Got a July 1st deadline. Doubt if I get it all in but effort is thee key. So my trusty time traveler where is the table for the Tooling, the 30% and the 3%. 3/2/10 - 3/1/11 is supposed to be at 30% and 3/1/10 and prior at the 3%. I have an tool that's calculating at the 3% instead of the 30. and its date is 10/5/10


----------



## OBP

Sorry, I read "pooling" instead of "tooling".
The tooling TTV is calculated on the Assets form, based on the ACQDATE field.
So did you enter an ACQDATE or ACQCOST?
Did it put the correct value in the Assessed Value field?
This is another case where you need a Query to update all the Tooling Assessed Values in one go.


----------



## VANESSA1

Hey, great reminiscing moment . Yes, I did on both questions.. So, how shall we handle this one?


----------



## OBP

Did it give you the correct assessed value?
I will need to see or duplicate the Record to find out what it is doing.


----------



## VANESSA1

12-2-00267 Champion Mfg information is acqdate 3/2/10 acqcost $4158. There is a second field that seems to be calculating at the 3% like it needs to but here is that info. Acqdate 3/2/08 acqcost $48,995


----------



## OBP

My 12-2-00267 is a Form type 103-N, should it be a 103-T then?

So despite having the database you still can't meet your deadline?


----------



## VANESSA1

Yes, it changed this year.


----------



## VANESSA1

I'll have more accurate values submitted to the state. But if you see what I get from these taxpayers, you would understand. It also seems to be worse this year for some reason.


----------



## OBP

Unfortunately I do not have any PID or Asset data in my version, would it be in the last version that you sent me?


----------



## VANESSA1

You know what it might not be. I can send you the database when I leave


----------



## OBP

You are right it is not there I just checked.


----------



## OBP

OK, my calculation for the cut off date is obviously not correct as it is giving 03/02/2011, not 03/02/2010.
So shall I try and get the calculation correct or do you want to Input the Date in to a table Annually for referencing?


----------



## VANESSA1

I remember doing something like that for the pooling, when I updated the dates for the current assessment year. Can I do the same thing for the tooling? If so, what do I need to do?


----------



## OBP

I will need to provide you with a table & form to do so and change the VBA code.


----------



## VANESSA1

Ahhh I've been entering in the database already this morning. Is that gonna be a problem?


----------



## OBP

No it won't be too much of a problem, so shall I make the changes?


----------



## VANESSA1

Yes please


----------



## OBP

You need all of the items in the attached database. I have added a new Tab to the mainform for the TTVs.
Watch out for the Cut-off date "USA Vs UK" format in case it doesn't work properly with your date format.
go to the Records that you have entered to change the Assessed value using the new setup.


----------



## VANESSA1

Question. On the new tab is the VBA set up like if dates are 3/2/10 to 3/1/11 the the TTV% is 30% otherwise its 3%? The new date is showing 3/1/10 as cutoff, I'm not quite understanding that.


----------



## OBP

You can set the date to what you want, anything equal to or after that date will be 30%, anything before will be 3%.


----------



## VANESSA1

There is a problem with a record 12-2-00267 Champion Mfg. When I try to go to the record the error message reads "Item not found in this collection." When I click okay a couple of times it goes to the record. When I go to the physical location tab and try to update the assessed value, the error shows up again. This is one where the tooling didn't calculate right. So what do I need to do?


----------



## OBP

Did you copy the ToolingTTVQ table and query and replace the old ones?


----------



## VANESSA1

Yep I double checked creation and mofication dates and they're all dated for today


----------



## OBP

Can you send me the database, as I don't get any error.


----------



## VANESSA1

Okay, tonight after before I leave.


----------



## VANESSA1

Sorry, I forgot to send you database. I'll make sure its done tonight. On another note, the overall summary report needs to be expanded to include the site code. I also noticed that the assessed value field is cut off. I'm printing landscape so it should be enough room.


----------



## VANESSA1

Thank you


----------



## OBP

I wonder my post went to?
I explained that the ToolingTTVQ query or the form must have been opened prior to the table being imported as Access had changed the CutOff date to "Exp1" so the VBA did not recognise it.
The Assessed value field was cut off because the mainform was landscape but the sub forms were still in Portrait.


----------



## VANESSA1

Lol. I've had alot of disappearing acts going on today. Maybe Im sharing without realizing it


----------



## VANESSA1

Okay, here's one. General Motors LLC 12-2-10800. I changed the form type from 103-N to 103-T. I then went in and entered into the assets. However, when I go to the tooling summary, its coming up blank. Any suggestions?


----------



## OBP

Did you them PID numbers?


----------



## VANESSA1

I'm assuming you're asking me if I gave them PID numbers. Yes I did.


----------



## OBP

Well the main MID has closed, and an Inactive date, so did you make it Current and remove the date?


----------



## VANESSA1

Duh, youre right being pulled alot of directions today. thanks


----------



## VANESSA1

Is there a easy way to remove the page breaks from one of the reports. Its 101 pages with the breaks and will probably be less than half without.


----------



## OBP

What are the page breaks doing, giving you blank pages?


----------



## VANESSA1

No, but when its just one company with one asset the pages pile up. And this company has over 100 physical locations.


----------



## VANESSA1

Good Morning, well I came into work early just to find out the main server is down, so what's up with you?


----------



## OBP

I am fine thanks.
So do you want the Physical data on the first page?


----------



## VANESSA1

Yeh that'll work. Have to keep refreshing forrum page so I'll Know when you respond. Apparently email isn't working either.


----------



## OBP

Try the Report called test, you also need the Overall Summary, which will replace your overall summary.

You can of course close up some of the gaps and use smaller fonts to save more space.


----------



## VANESSA1

Do I need to do the overall summary first? Asking because when I try to open the "test" report it has error messages


----------



## VANESSA1

It has it on both of them


----------



## OBP

What error message?
Did you open the Main Location form and select an appropriate MID?


----------



## VANESSA1

Looks like it did it. The SiteCode is missing however. What is the test supposed to do?


----------



## OBP

I did not want to mess up your main report, so this was to find out how to make the changes and see if they were suitable.
Is it?


----------



## VANESSA1

Yeah the page breaks are gone and its 60 less pages than what it was which is great. However, the field where you added the site code isn't there.


----------



## OBP

That is because my copy that I practised on hasn't got your copy of the Overall Summary, which has the Site Code added and is wider.
I will import you wider version and amake the changes to the Mail Location main report as well and then post them on here.


----------



## VANESSA1

Okay


----------



## OBP

This version has a bit more space saved and should give you a few less pages.
You will need to replace your versions of all 3.


----------



## VANESSA1

Cool. That'll work. Thanks


----------



## VANESSA1

Hey OBP. Need a new report. Can I get one that sorts on asset numbers for a given PID?


----------



## VANESSA1

Got a problem Safety Kleen Systems Inc p-011-002-00397. Trying to enter an asset in the PID. assessed value not calculating.


----------



## OBP

Do you just need the PID?
ie. not MID data?

Re the problem with 11-2-00397, it is not only with that MID, it is all MIDs, somehow the tblassetslist subform has reverted back to an older version which had a VBA error on it. So download the attached database, delete your tblassetlist Form and import this one.


----------



## VANESSA1

It won't mess with what I've done today will it?


----------



## OBP

No, after importing the subform just open the Mainform find those companies that haven't had the calculation and move to each Asset in turn, that should update Assessed value.


----------



## VANESSA1

Okay


----------



## VANESSA1

Okay, its not working for this one. I have to delete first? I'm used to just replacing.


----------



## OBP

OK, I missed one of the original changes, the one for the Pool field.
I have just tested this and it definitely works for all 3 fields
ACQ Date, ACQ Cost & Pool.


----------



## VANESSA1

Thanks, it works


----------



## OBP

What about my question on the Report?
What is it going to look like?


----------



## VANESSA1

It will look the same as the overall summary, just sorted by asset number.


----------



## OBP

Do you want to just change the Summary?


----------



## VANESSA1

Would like to have the option on how the overall summary report is printed, either by date or by the asset number.


----------



## VANESSA1

Okay, I'm getting ready to leave for the day. I'll be in tomorrow. The last saturday I have to do this. But things are looking up. I'm on the T's so maybe I can get those done tomorrow


----------



## OBP

Here is what you need, a new tblMailLoc Form which now has a "Date Summary" and an "Asset Summary" button, you need to replace the old one.
2 new Reports 
MailLoc Main Asset
OverallSummary Asset.

I have left the date sort in this one after the Asset sort as you have a few Assets without Asset Numbers.


----------



## VANESSA1

That works great! Thanks


----------



## VANESSA1

Just to let you know. I met my deadline yesterday at 1:00 pm. So, hopefully, when things are cleaned up more here, I can set up with the IT department here to create a shared drive for my other people.


----------



## OBP

That is good news :up:. Let's hope the others find the database usefull.


----------



## VANESSA1

Hi there. On the Overall Summary report on the Footer Section it prints date information and page numbers. Is there a way to add the MName?


----------



## VANESSA1

Hey OPB I have another issue. We get charged extra on color copies. I noticed that the headings are printing in Dark Blue. I tried changing the settings through the Access Options but its still printing in color. What else can I do to make my reports print only in Black and White? Yes I also went through the printing preferences on my printer.


----------



## OBP

Yes you can add the Mname to the Report Footer Section.
To change the colour of the Headings, click on a heading and then choose the Font Colour and set it to Black.


----------



## VANESSA1

Okay cool


----------



## VANESSA1

Okay, I'm doing some but Im still coming up with color. In the design view of the report, I'm not seeing the heading and the labels are over-lapping. I selected what fields I could and changed them. Haven't worked on footer yet.


----------



## VANESSA1

Okay I found another format


----------



## VANESSA1

Okay i can get rid of these blue lines. They are the ones between the assets


----------



## VANESSA1

Okay got it. Thanks for listening. I'm going home now bye


----------



## OBP

Well done, I haven't been on the Forum much as we are decorating my Son's house.


----------



## VANESSA1

Hey wanna fly over and cut down a tree?  Hey I'm learning to use chain saws now. I bought an electric one but the cord doesn't reach to where the tree fell in the yard, so I had to buy a cordless one.


----------



## OBP

Obviously a lady of many talents. I also had an electric chain saw. You need to be very carefull as chain saws are deadly.


----------



## VANESSA1

Yeah, that's for sure. I'll probably get the small branches and call for help for the larger ones. But I have my safety glasses and my gloves.


----------



## VANESSA1

Hey OBP I just emailed you an PDF file. It is screenprints of the PID for one partcular company. I have been trying to delete a duplicate, but the problem is everytime I got to the record, it brings up the list of the company/assets I don't want to delete. I tried looking by ID number and bringing it up that way but it keeps going to where I don't want to go. I also sent the pages of the reports where the duplicate assets are marking what I'm trying to delete. I'm thinking of sending you the database tonight when I leave if that's okay.


----------



## OBP

OK, on the mainloc form go to Searching, in the search names, type in ELKHART COUNTY CHILD SUPPORT and hit enter.
it will list 3 records and 1 blank one. You want the second record ID - 1979, click on the "Go to this Record" button.
Click on Physical locations Tab and then the Next Location button and there they are.


----------



## VANESSA1

I have done that. There are two asset numbers I'm looking for 158767 and 158768. there is a PID on the Overall Summary report that only has these two assets. These are the two I want to get rid of. But for some reason I can't bring just that PID and those two assets up


----------



## OBP

That is the record that you need, I am looking at it now.
See attached.


----------



## VANESSA1

Okay this is just weird. I click on that ID 1979 and when I do the ID 1970 comes up. Ok so then I click on the ID 1970 and the ID 1970 comes up. I'm not even seeing the ID's in the asset listing of 3141 and 3142. I even looked at my inactive PID's to make sure I didn't put them there.


----------



## OBP

Are you going to the second record of the 1970 Physloc locations, it has 1979 in it.


----------



## VANESSA1

Honest I really am. I've done it over and over.


----------



## OBP

Can you send me a screenshot?


----------



## VANESSA1

Here it is


----------



## OBP

Can you save them as xls files I can't open xlsx files.


----------



## VANESSA1

Okay


----------



## VANESSA1

I just looked at it and its not what you want. How do I send screenshots?


----------



## OBP

I put the screenshot on an Excel or Word Doc and post that, you can also zip them if you want.


----------



## VANESSA1

Is there something in the menu that lets me do this? because right-click doesn't work


----------



## OBP

Screenshots are normally alt or function key plus the [prt sc] key on the normal querty keyboard. That copies the screen which you can then paste in to Excel or Word.


----------



## VANESSA1

Well, I even went to the microsoft access online website and tried to follow its directions, doesn't work for me.


----------



## OBP

Sorry, I can't think of any other way to do it.
It works for me just going to the second Physical location using the Physical Location Tab's "Next Location" button.


----------



## VANESSA1

Didn't try to next location key and I just did it and it worked.


----------



## VANESSA1

Yes it balances now


----------



## OBP

Good.


----------



## VANESSA1

I have calculation problem. MID 12-2-00281 with PID of 12-2-00366. The last asset has a ACQDATE of 10/5/10 which is within the dates of 3/2/10 t0 3/1/11 so its supposed to be caluclating at the 30%. It is calculating at the 3%. All of the other ones are calculating okay. The correct value is supposed to be 109.55 but its calculating 10.96

Also, on another note, I need a final assessed value on the tooling report. It would be just the total Costs and total Assessed value.


----------



## OBP

I just clicked on the "DURABLE STANDARD CONTAINER" record and it updated OK to 109.56.

I will be busy decorating my son's house until the weekend, I will take a look at the report then.


----------



## VANESSA1

Okay


----------



## VANESSA1

Good morning, Ive emailed you an attachment so you can see what I see. Okay I've have stared at this over and over again, I have gone through the PID's over and over again. I still can't get the overall summary and the Pooling report to balance. the discrepancy seems to be in the PID 12-2-00485 where it seems that all of the acq cost don't transfer to the pooling report.


----------



## OBP

We have discussed this problem before, you have multiple identical records that the Query ignores to prevent duplication.
I entered dummy asset numbers in those records to achieve balance.
I used temp 1 to temp 7.
See attachment.


----------



## OBP

RE "Also, on another note, I need a final assessed value on the tooling report. It would be just the total Costs and total Assessed value" where do you want these fields to be?


----------



## VANESSA1

Yes it would just be the total costs and total assessed value a few spaces below the sub-totals


----------



## VANESSA1

And on the other note, I'll make sure those are unique numbers next time


----------



## OBP

Here is the modified Tooling Summary Report, you need to delete your version & Import this one.


----------



## VANESSA1

Hey OPB, I got a small cosmetic thing here. Is there a way to fix the control number on the search tab so that I can enter the numbers with the dashes preset? I can do it everywhere else but I have to put the dashes in myself here.


----------



## OBP

Just copy the "Format" Property of the Control Number field from the main form to the Search field's format property.


----------



## VANESSA1

When I go to the form design of the main form and click on the format tab of the MID, it is blank. But wh en I click on the control number field of the search tab the format tab says Combo 67. Not really understanding what I'm seeing here.


----------



## OBP

OK, the table uses an Input mask for that field, it is
00\-0\-00000;0;
you can paste that in to the form field's input mask.


----------



## VANESSA1

Isn't working for me. Went to search tab and went to design view. Tried the copy/paste and tried to type manually. Could there be an issue that the field says Combo 67 unbound? Also I'm doing this on the property sheet. Where would this be in a table?


----------



## OBP

I have just pasted the 00\-0\-00000;0; in to the Combo67's Input mask and it works fine for me as an Input Mask, however it does turn off the combo's auto jump to the number until you enter the last value.
So it is not perfect.


----------



## VANESSA1

Okay, got it finally


----------



## VANESSA1

Now something still isn't right. I type in the control number and its not finding the info


----------



## OBP

It works for me.
Is the number in the list?


----------



## VANESSA1

Yes, its one of my existing companies. I can do an alpha search okay but not by the control number


----------



## OBP

Yes but does the number appear in the Combo list of numbers?


----------



## VANESSA1

yes


----------



## OBP

Do you click on that number when it is highlighted?


----------



## VANESSA1

Yes and when I do, it just shows the number and not the company information


----------



## OBP

What number are you looking for?


----------



## VANESSA1

Well, this just gets better. I had to re-boot the system. Now I get an error message "The value you entered isn't valid for this field". When I look at the combo box its a valid number. Just received your last email. This is the number I'm currently on 13-2-00057. The name of the company is New Panda Chinese Restaurant.


----------



## OBP

That works for me, see attachment.


----------



## VANESSA1

I'm not getting it. I don't doubt yours work, I just can't figure out what I'm doing wrong.


----------



## VANESSA1

I kept at it and I think I got it. Just that one little glitch you were talking about but its working.


----------



## VANESSA1

Hey OPB, long time on hear. I'm not sure if I asked this before. On the search tab when I search on an address is it just looking at the mailloc info? I was wonder if it could be widen to include the physical location as well? I just tried looking up the street name Leininger and it only brings up the mail loc results when I know there should be some in the physical loc


----------



## OBP

Yes it does, I just searched for ELKHART and it found OLD COUNTRY BUFFET


----------



## VANESSA1

Okay on that, look up the street Leininger. The results only show on the the mailloc side but not the physloc side.

On another issue I need to fix a pooling percentage problem. In pool 4. for date range 3/2/00 - 3/2/01 should be calculating at 15% but its calculating at 10%. Where do I go to fix the pooldata?


----------



## OBP

Is the physloc Leininger record in the database that i have?
The %age is in the TTV table, for some reason pool 4, date period 4 has 2 records with both 15% and 10%, perhaps one of them should be a different date period, if not then delete one of them.
You can see them all in order on the TTV% tab on the mainform.


----------



## VANESSA1

Okay look at it from another angle. 12-2-10955 Elkhart Plastics. The mail loc is 1400 Leininger Ave and there are two leasing companies that have assets at the same location. So when I search on the Parcel Number it comes up just for this company. 

On the pooling percentages, it looks like the dates haven't been updated. Pool 4 the last two should have date ranges of 3/2/99 to 3/1/00 for the 10% and the last one should be Prior to 3/2/99 at 5%


----------



## OBP

Have you updated them?

I have quite a few Leininger addresses in the physloc table, but none of them are associated with 12-2-10955 Elkhart Plastics.


----------



## VANESSA1

Okay its Monday and I have murphy's law - everything that can go wrong will go wrong. Anyhow, this is a weird one. On both the overall summary reports I have this weird blue streaking on the paper. It looks like when a register tape on a cash register is getting ready to run out of paper. I went back to verify an update to make sure that I'm only printing in black & white, changing each field to#00000 in the design of the reports but its still give the blue streaks. Is there an easier/softer universal way to make sure these reports only print in black and white?

I'll get back to you on the other issues


----------



## OBP

Is it blue going accross or down?
If it accross it could be "Line", if it is find it on the design view and set it to black.


----------



## OBP

There is quite a bit of "blue" on the reports, both headings and lines on the "Overall Summary" subreport.


----------



## VANESSA1

It looks like the streaks are going both ways. On the left side its up and down and on the right side its side to side


----------



## VANESSA1

I just sent you an email with an attachment


----------



## VANESSA1

Hi, long time no hear. We are expanding the functions of the database. What I need to do is to pull some of the main contact info into a word document.


----------



## OBP

Hello bad penny.
You need to set up the Word doc with Bookmarks or use mail merge.


----------



## VANESSA1

Oh man that hurts
Thanks, you're great as always


----------



## OBP

Do you know how to set up Word's Mail Merge, as I don't.


----------



## VANESSA1

Well, I used to do it all the time with Excel. But now I want to do it from the database. So now it sounds like new territory for the both of us :-0


----------



## OBP

You use a query to select the Contacts and select that for the mail merge.
However you can also use VBA to directly place data in to the Bookmarked positions on a word doc (or email Recipients)


----------



## VANESSA1

That's good because the database is expanding to be able to do audits. I'm still researching the accounting information I'll need. Right now the goal is just those companies that reported $10,000 or less for the past 3 years. The list of companies and values I am getting from the data processing department. I don't want a mass mailing, just one company at a time. Right now I'm creating the form letter(s) that I will merge the address into.


----------



## VANESSA1

Hey OBP what version of Word do you have?


----------



## OBP

2003.


----------



## VANESSA1

I want to send you sample letters showing what fields I would like worked on to be imported


----------



## VANESSA1

Here you go. Just finished the last one. There are 4 letter in one document


----------



## OBP

I am not sure that I can work with it in that format, can they be separate documents.


----------



## VANESSA1

Okay, I'll work on that now.


----------



## VANESSA1

System went a little nuts here not sure what you got. test reply


----------



## VANESSA1

here you go


----------



## OBP

They all appear to be the same as the first one you posted, just with different names.
See the attached version that I have modified, I have deleted the other letters just keeping the first one and added bookmarks for where the Database data is going to go.
I assume that you will need a form to select and appropriate letter?


----------



## VANESSA1

They are basic right now. And yes i think i would need a form to select appropriate letter. I'm looking for the bookmark and can't see it. I see 2 dots just above the ATTN:. Is that it?


----------



## OBP

For bookmarks, on the main menu>Insert>Bookmarks
or 
Edit Goto bookmarks.


----------



## VANESSA1

Okay, I see them


----------



## OBP

Right so we need a form to choose a Word doc and we need all the Wordocs to have those Bookmarks (and any others that may be needed).
I will then create the VBA code to transfer the Record data to the Bookmarks ready for emailing.
But I have not yet tried it with an "emailing" type word doc, only standard ones.
So I will have to see if I can get it to work with the doc that I posted.
Where do you want the Seelction Form to go in the database?
How will you select the Recipient?


----------



## VANESSA1

I don't see myself emailing too much because of state regs wanting us to have hard copies of everything... I looking to print the letters and envelopes for mailing. I want to keep records in database of when letters are mailed but haven't figured out where I want it yet.

Can the letter selection(s) go on the reports tab?

In selecting recipient, I was wondering if there was a field that can be added to the main form that has an audit checkbox for now. There will tables to create. Still researching, but very close to getting an idea of what I will need.


----------



## OBP

I have created a couple of forms based on another database that allows you to select a mailloc and a letter (only APTLetter is working at the moment, as that is the only one with the Bookmarks) and the vba takes the letter template, adds the data to the bookmarks and saves it with the MNAME and the date.
There is a table that holds the Folder path to where the Templates and new documents are being held.
It works fine.
I will create a table to store the fact that the letter has been created and the creation date, what do you want to call the Table?

Are you going to add bookmarks to the others?


----------



## VANESSA1

Good Morning. The bookmarks will be used on all the letters. Any changes can be done manually as informed by the taxpayer, but I don't see this being an issue at this point because I'm working on the small businesses at this point.

It could be called AUDITtbl.


----------



## OBP

You didn't say if you were going to create the individual word docs with the Bookmarks.


----------



## VANESSA1

Sorry if I was unclear. The procedure I'm seeing is 1. Send the intro letter to the taxpayer informing them that they have been selected for audit. During this period, contact with the taxpayer is made and an appointment is set up or other circumstances and the audit process is closed. If an appointment is set up then 2. Send an appointment letter confirming the date of the audit and documents needed to perform the audit. Once the audit is complete, one of the last 2 letters will be sent out.


----------



## VANESSA1

Hey OBP, thsi is a general layout of how I would like to track companies being audited. These companies on the database form types are 103-N. Right now I have a listing from the IT dept here with the companies which reported 10,000 or less. These are further reduced removing the leasing companies (103-O), tooling (103-T), out of state mail locations, and closed as of the current assessment year. So, far in just the A's, I have 63 companies I can audit.


----------



## OBP

Have you forgotten that I can't open .xlsx workbooks?


----------



## VANESSA1

Oops Here it is again


----------



## OBP

Shouldn't your Audit table have the Audit Year in it?
Where is the data from the other tables going to go?


----------



## VANESSA1

On the spreadsheet on row 23 columns J-L shows the audit year(s). The rows 1-5 on the spreadsheet can go on the main form under the notes field. I'm thinking new form(s) will have to be created for the other tables.


----------



## OBP

Don't you need a table to bring all the data together?
Your audit table dosn't store any of those items.


----------



## VANESSA1

Okay, I'll put a flowchart together and get back to you.


----------



## VANESSA1

I have written out the procedures and attached an updated spreadsheet for the audit companies. I have created some drop boxes and a list boxes. I think that it would be more feasible for the audit asset listing to be separate from the leasing companies asset listings, but your feedback would be appreciated. Also, I have added a couple of more reports. If you need me to create tables, just let me know where. Also, I am thinking about that relationship issue that I always have a hard time with. I think if you have any questions, that may help me figure out. The difference being that the assets will not be linked to any other companies and/or locations.


----------



## OBP

OK, I will take a look and get back to you.
In the mean time perhaps you could expand on this "The difference being that the assets will not be linked to any other companies and/or locations."
Can you give me an example of the current Assets Vs the Audit Assets?


----------



## VANESSA1

Well, the current asset listing is linked to locations other than the main location. The audit asset listing will be for just the companies selected companies on the main location. They do not have assets located at other locations. The form types for these companies will be 103-N. The current asset listing with the PID have a form type of 103-O. Also, the companies with form types 103-T will not be utilized. The audit asset listing has extra fields, some with drop down boxes to determine what assets and costs will be pulled for auditing purposes. The audit asset listing will not utilized the physical location subform. On the asset table, the audit asset lisitng will not utilized the PID, SITE CODE OR LEASE NUMBER. Does this help?


----------



## OBP

Well I will make a start and see how it goes.
I assume that you will need to store what "Documentation" from the checklist has been selected so that you can check it off when they respond?
If you select Audit = No then do you need to select & store the "NO AUDIT REASON" given?

I am not sure about the Mainform design, I assume that it is just displaying current data where applicable?


----------



## VANESSA1

Well, I inadvertedly added some fields to the main form (I thought I had copied it). So there are 3 fields , audit company (Y/N), The no audit table and the audit category table. 

On the documentation checklist, once I receive the documents and copy them, I send any originals back to the company. Once the audit is complete, these documents are destroyed.


----------



## VANESSA1

Hey OPB, how are things going?


----------



## OBP

I am busy in my son's house again.
I hope to do some work on the database at the weekend.


----------



## OBP

I have made some progress with the database, most of the new tables, queries & forms are in place.
I have a couple of questions regarding the Audit database Excel sheet.
Are you going to be using the main MailLoc table as the Main table for the Audits?


----------



## VANESSA1

Yes to select the companies who will be audited. I inadvertently added three tables to the main form (I thought I had copied it). One with the audit company (y/n), the No Audit combo list and the audit category combo list.


----------



## OBP

I also do not see any "Discovery letters" on the new tables.


----------



## VANESSA1

You're right. The discovery letter will appear between the appointment letter and the audit closed date.


----------



## OBP

OK, I assume the Assets will be a new table all together?


----------



## VANESSA1

Yes, because how the data will be pulled into the reports will be somewhat different than the regular leasing companies I deal with.


----------



## OBP

I will carry on with the tables and things.


----------



## VANESSA1

Okay.


----------



## OBP

Should the Audit Company Y/N fields be part of the Audit Letters or part of MailLoc.
Does it require a letter telling the Company that they are not being audited?


----------



## VANESSA1

Sorry, just got back from lunch. If the company is not selected for audit, there is no letter, just the reasons under the No Audit Table is selected. The tables are attached to the MailLoc form. Also, the audit category helps determine who will be selected at a later date.


----------



## OBP

You mentioned the Audit Category, which is a field in your main tabel, however you do not have a table with a matching name.
You have "ASSESSED VALUE CATEGORIES" & BUSINESS PROPERTY TAX CATEGORY.
So which one is used for the Audit category and what would the other one be used for?


----------



## VANESSA1

Hey OPB. The ASSESSED VALUE CATEGORY is on the mailloc form and determines which tier or range of companies are selected for audit. THE BUSINESS PROPERTY TAX CATEGORY is on the audit asset listing and will determine during an audit if a company is reporting assets correctly when seeing their documentation.


----------



## OBP

So is one of them the "Audit Category"?
Or is that something else again?


----------



## VANESSA1

Audit Category is totally different and separate. When you look at the drop down list for audit category it has a range with $10,000 increments (ex $0 - $10,000). This helps determines which companies are to be selected for audit. 

When I do an audit, and I enter the assets, those assets will fall under one of the nine categories on the drop down list. This helps determines if the asset is being reported correctly.

Does this help?


----------



## OBP

Yes, you have called that table "ASSESSED VALUE CATEGORIES"


----------



## VANESSA1

Okay, I see. On sheet 1 of the spreadsheet i named it one thing and on the Second sheet I named it another thing.  Sorry for the confusion. On the database it is under Audit Category.


----------



## VANESSA1

Hey OPB, how is it going?


----------



## OBP

I haven't been able to do any work on the database since Monday.
But I should be able to send you something at the weekend.


----------



## VANESSA1

Thanks


----------



## OBP

I have another question, on the Excel sheet Database mainforms what are the DOR Date & DOR Number?


----------



## VANESSA1

Good Morning. On the mailloc form of the database, there are fields for the Dor Date and DOR Number. This is information i receive from the Indiana Department of Revenue of when a company applied for a Tax ID Number.


----------



## OBP

So they don't need to be in the Auditing section as well then.
I will send you what I have got up till now.


----------



## VANESSA1

Okay


----------



## VANESSA1

When I opened up the email, the first thing I notice is the attachment has the generic icon on it. When I click on it, it tells me "Windows cannot open this file". It doesn't have the access icon.


----------



## VANESSA1

The file reads "persprop.7z"


----------



## OBP

Yes you need to unzip it.
You can download 7z for free if necessary.


----------



## VANESSA1

Hmmm. Not sure if the constraints here will let me have it. Where do I get the download from?


----------



## VANESSA1

Already tried it and nope system here won't let me have it. Why aren't we using the *.zip for this one? I'll try to talk with IT and see if they'll release the security on the website.


----------



## OBP

I will send it again as a zip file.


----------



## VANESSA1

I'm liking this. You figured out something I was trying to figure out and that is the history. I like the options of being able to change/update categories. There are questions, of course. On the "Find a Company" the drop down doesn't show all the companies like the 'MNAME" does. Since all of the companies (records) will not be audited, do I just bring up the company and do the "No Audit Reason"? When I click on the next record, it brings up a blank screen. Will I have to do this for each record?


----------



## OBP

The find combo can only find the records that I have entered, so the list is one record long.
At the moment you have to enter the Companies manually.
However we could run an Append query that adds all the Companies that will be or are likely to be audited. You will need to remind which ones they are.
As you can see it has taken a bit of work to get this far, but we are nearly there.
By the way I think the first Letter Check box is still working and with the button on the bottom will create a word doc letter, providing you have the one with the Bookmarks in it, which is something else I still have to do, is set up the other letter's bookmarks


----------



## VANESSA1

Yeah, I have the intro letter of course but not one with the bookmarks in it. Can't figure the bookmarks procedure out. The selection of the companies to be audited will have to be done by me. Its a little tedious and I have to use the main server here in the process. Is there an easy tutorial on how to do bookmarks?


----------



## OBP

Bookmarks are actually quite simple.
Place the Cursor where you want the Bookmark and then on the main menu>Insert>Bookmarks add a meaningfull name for the bookmark and then click the Add Button.
You can check where the bookmarks are using the goto bookmark function in the insert bookmarks menu.
Can't you get an excel list from the main server?


----------



## VANESSA1

Is the bookmark one word or do I have to do a word for each field that is going to be imported?

I think I have such a spreadsheet already. It's not all of the companies, just those whose assessed value is 10,000 or less for the last 3 years. Will that work?


----------



## OBP

Yes that would certainly help, we can import it, transfer the data to your Audit and Asset tables and then delete it.

Each bookmark should have a one word name. If you look at the one that I have attached you can see the names and use the Goto function to see where they are.


----------



## VANESSA1

Okay, mine is not that simple. I have up to 3 years per company. I'll get another report from my IT contact. What information would be helpful for you?

I clicked on introduction Letter then on Create letter and I don't get anything. Is there something I'm doing wrong?


----------



## VANESSA1

Okay, I see sorry we got crossed on posts


----------



## OBP

Data that would be useful is the Mname and the LinkID field if it has one, the number of years do not matter as we can use Grouping to reduce them.


----------



## VANESSA1

The fields on the main server will not have the LinkID field. What I have right now is the Control/Parcel Number and the Company Name with the assessed value for the years 2009 - 2011. I will modify it back to its original format and send in and .xls format


----------



## OBP

Before trying to use the letters you need to set up the File Paths using the File Paths button, you need to specify where to find the Template Doc and where to file the completed letter.


----------



## VANESSA1

Okay, I wasn't gonna try that, besides those buttons don't work for me anyway. But you're doing a good job of scaring the heck out of me


----------



## OBP

They may work if you have the Appt letter doc and tell it where to find it.


----------



## VANESSA1

I just looked at the table the the File Paths and I can see where you are going with it. As far as where to put the completed letter, can it go to the documentation tab?


----------



## OBP

No it needs a Folder on the Hard Drive or Server to store it, it is then available as a record or for printing agaian etc.
Or do you mean can the Filepath Form go on the Tab?


----------



## VANESSA1

Okay light bulb just went off. I did something similar in the documentation tab when I created a folder for pdf files. Is this the same concept?


----------



## VANESSA1

With remembering stuff, when I do a "file save as", I need to save with an extension of *.dot for a template?


----------



## OBP

Actually although I call the letters "Templates" the VBA is currently looking for a .doc.


----------



## VANESSA1

Okay, I'll do that. Each letter needs its own folder, right. So far I've created one for the audit letter and one for the appt letter. Does this sound right? Also, do folders have to be created for the for the documents once they are created? Also, when I did the bookmark thing on the audit letter, I didn't see the little marks that were on yours. but I verified it and they are there.


----------



## OBP

The Template can all go in the same folder, as long as the letters have different names.
I would then create one folder to take all the letters that are created.

I am not sure what marks you are talking about for the Bookmarks, unless it is the commas.

Once you have the 2 folders you can test the Create Letter VBA for the Appt letter.
If that works I will create similar code for the other letters.


----------



## OBP

Am I correct in thinking that the NTR, Exempt, Leasing, FTF & Tooling columns will be used in the database to decide what Companies will be audited?
Why is the tblMailLoc Control Number not in the same format as the Parcel? ie. 09-2-00913 Vs P-009-002-00913 why were the leading zeroes removed?


----------



## VANESSA1

Hey OPB been out for our Thanksgiving holiday since last Thursday. On the bookmarks, yes it is the commas I am seeing on yours but not mine. I am going to update the folder(s) to put all the templates in one. 

The Control numbers are different because that is how they are moved from the main server. the "002" only refers to the personal property. In the main server there are several codes (001 - 008) that have different references. That is the required state format that is submitted. It doesn't have to be here since this data isn't submitted to the state.

The NTR, Exempt, Leasing..etc. are already in the audit form. And yes they, determine which companies will be audited.


----------



## VANESSA1

Okay, something's not working. I opened the File Paths table and put in directory of where the letter is (H:\apptletter). It has an ID 5. I then went back to the form and hit the "Set File Paths" then I hit the "Create Letter". What am I supposed to see?


----------



## OBP

There should be a new Doc in the folder for the output. it should have the Mname and date as the doc name.


----------



## VANESSA1

I just want to make sure that I have the paths set up correctly. On the NewDocFilePath it is 
H:\My Documents\sentapptltrs\. Does this look right? Because when I hit Set File Paths and Create Letter, nothing happens


----------



## OBP

That looks fine, what about the Template File Path?


----------



## OBP

OK, the 2 buttons on the form have lost their On Click procedures.
Click on each button and then click on the "On Click" event procedure, that will re-link to the VBA code.


----------



## VANESSA1

The templateFilePath is H:\My Documents\apptletter.

Okay, there is progress. When I click on the set file paths, another form comes up. When I click on the get "Get Folder For Template" the result from the file is H:\apptletter\. and the New Doc File Path is H:\sentapptltrs\. I then close the form. However, when I click on Create Letter, its says "This file could not be found".


----------



## VANESSA1

Nevermind, I have the make sure the "Select a Letter" box is checked. I did it again and it works. Yay!


----------



## OBP

Great.


----------



## OBP

Can you send me copies of the other Letters when you have them with bookmarks to your satisfaction?


----------



## VANESSA1

Okay, I don't know why I didn't notice this yesterday, but the appointment letter is really the introduction letter. I'll send you what is really the appointment letter with the others.


----------



## VANESSA1

My computer is acting stupid this morning see if this is it


----------



## VANESSA1

This is the no discovery letter. Its simpler than what the discovery letter is going to be. I'm still tweaking it.


----------



## VANESSA1

Okay, here is the discovery letter


----------



## OBP

In the Discovery letters what is the "Control Number"?


----------



## VANESSA1

I'm sorry that should be the MID


----------



## OBP

OK, what does "INSERT DISCOVER LETTER REPORT" mean?
Do you want to put some text in there manually or with VBA?


----------



## VANESSA1

Okay on the excel spreadsheet on the "Database Main Forms" tab, There is a Discovery Letter Report. This is the report that is to be inserted into the letter.


----------



## OBP

Where will that data come from?


----------



## VANESSA1

The data will come from the audit asset listing


----------



## VANESSA1

Hey OPB, how is it going?


----------



## OBP

I have been a bit busy, but I will get back to it.


----------



## OBP

Have you decided how you are going to incorporate the changes in to your working database?


----------



## VANESSA1

Hey OPB. I've been thinking about that. I don't think it would be feasible for me to try to do it on this end because of how big the modifications are. I could just zip and send to you. I haven't done any major updates the past few months as far as companies and assets go. I just had a dream about this thing last night on how to incorporate the notices with my next major mailing which will be sometime at the end of January to the beginning of February. How does that sound to you?


----------



## OBP

I will see if I can get it ready for testing by Christmas.


----------



## VANESSA1

Do you want my working database yet?


----------



## OBP

No not yet, I will finish the auditing section first.


----------



## OBP

For auditing purposes do you want to use the MailLoc Control Number (MID) or the Audit Parcel?
Also are the Audit Assets in the tblAssetList table?
Why are there 2 sets of TTV in the Assets listing?

Also the Appointment Letter you posted is the original Introduction letter.


----------



## VANESSA1

I'm sorry OPB. Your post never came through on my email. 

On the audit listing, the the MailLoc Control Number is going to be used.


----------



## VANESSA1

Im looking as the asset lisitng and something doesn't look right. I'm reviewing now.


----------



## OBP

Which Asset listing?


----------



## VANESSA1

Goodmorning, just starting to open stuff up. It is the audit asset listing on the spreadsheet. Okay, I looked at this too late yesterday; got fresher eyes this am. What I was trying to do is the show what was reported on their federal tax returns(s) vs. what they reported on the returns submitted to me. So the first set of TTV is what is from the federal and the second set of TTV is from their Personal Property Returns. But I'm seeing one problem however, I don't see the detail on returns submitted here. So, what I'm thinking of doing is elimnating the second set of TTV information. I'm thinking that when a discovery comes up I can use this on the discovery letter report.


----------



## VANESSA1

As far as that post, you're right, that was the original introduction letter. Do you have a copy of the appointment letter?


----------



## OBP

I have the Appointment letter and that introduction letter is basically the same, what I need is an introduction letter with the bookmarks.


----------



## VANESSA1

This is the one I have with the bookmarks.


----------



## OBP

Is it Introduction?


----------



## VANESSA1

This is the appointment letter when the taxpayer is notified when the audit will take place. The intro letter basically tells them they have been selected for the audit.


----------



## OBP

That is the one that I need.
I will add the Date & Time Bookmarks to it, as they are not currently there.
Also should the List of Documents be taken from the database rather than the fixed list that is curently on the document?


----------



## VANESSA1

The list of documents in the letter just tells the taxpayer what documents they will need to present that will be utilized in the audit. The list of documents in the database will show what documents are received.


----------



## VANESSA1

Hey OPB, how is it going?


----------



## VANESSA1

Hey OPB, I need a report where "new/current/closed/ = "new". The report we did last year for new companies all have a control number now but there status is still new. I will need to create labels for these.


----------



## OBP

At the moment it isn't "going" at all, I just can't work up any enthusiasm for Access programming.
I will take a look at the "New" Report and get back to you.


----------



## OBP

Do you need a new report or to replace the current one with no CN?

Edit, the current query although called "no CN" does actually have CNs, it must have been changed at some time.


----------



## VANESSA1

Hey OPB, good to hear from you. The old report can be replaced. At one time during last year the format was changed. I had to give fictitious parcel numbers to new ones. I think some problem popped up when they weren't showing up on another report.


----------



## OBP

OK, I will modify the current report, do you want the CN number on the labels?


----------



## VANESSA1

Thanks that would be great!


----------



## OBP

Where should it go on the label?
Do you want to change the "Report" command button names?


----------



## VANESSA1

The control number should be above company name and the report name can be changed to just "New Companies".


----------



## OBP

Ok.


----------



## VANESSA1

Happy New Year  How is it going?


----------



## OBP

A Happy & Prosperous New Year to you too.
I am afraid it is still not "going" yet, I will try and find some enthusiasm from somewhere. I have 3 databases to work on, so I need to do something.


----------



## VANESSA1

Okay


----------



## VANESSA1

How is the "going"?


----------



## OBP

I have made a small start, I will try and do some more.


----------



## VANESSA1

Cool!


----------



## OBP

Do you always send an Introduction letter before an Appointment letter and do you always send an Appointment letter?
I am looking at checks that the correct letter is chosen.


----------



## VANESSA1

Hey OPB, Happy Birthday . The Introduction Letter is sent first to let the taxpayer know they have been selected for audit. An appointment letter will be sent once contact is made with the taxpayer to confirm if an audit is taking place. An appointment may or may not be set up depending on discussions with the taxpayer. If it is determined that an audit will not take place, the audit process is closed. I hope this helps.


----------



## OBP

Thanks for the Birthday wishes.
That is good, I just need to check that the Introduction Letter has been sent before trying to send the appointment letter.
I will also ensure that the appointment letter has been sent before trying to send one of the discovery letters.


----------



## VANESSA1

That is a good thing


----------



## VANESSA1

Hey OPB, I was going to update the date periods for the current assessment year. Do you want me to wait on this?


----------



## OBP

If you won't need them before the end of the month you could hold off until then.


----------



## VANESSA1

Okay, I can wait. Thanks


----------



## OBP

Does an Appointment Letter use the DBANAME the same as the Introduction letter?
There is also an Attn Bookmark and a Dear place, what MailLoc table Field goes in the Dear position?


----------



## VANESSA1

Well hello there . Yes, it will use the DBANAME. Attn bookmark is the Contact Name. On the Dear...hmmm. Well, can I edit that myself or can it go with the generic greeting (Sir/Madam)?


----------



## OBP

So where on the page does the bookmark for the DBANAME go?
Is the "Dear" also the Contact name that is the Attn bookmark?
I am not familiar with US letter writing convention.


----------



## VANESSA1

Okay, I had to go back and look at the format we orginally agreed to. Now, I don't see where we put the DBANAME in the format of letter, so its not suppposed to be there. the ATTN is the Contact Name on the MailLoc. Normally, for US letter writing, the greeting is the first or last name of the Contact Name, so that is why I was asking if I could put this information in myself. My personal taste is usually the first name, but sometimes depending on any unusual circumstances, I may use the last name.


----------



## OBP

You could have an unbound field on the creation form, but it would't "remember" what you used, or you could just use the first name if there is one, there does appear to be one if the contact name is filled in.

I will remove the DBANME from all the letters.
I have just successfully created the appointment letter with the date & time, the only problem was what to put in the Dear.
As usual I will have to go soon.


----------



## OBP

I could put the first name in the unbound field for you to modify if required.


----------



## VANESSA1

That would be great if you could do that. Good to hear from you; surprised; don't expect to hear from you this late because of time difference.


----------



## OBP

I had just finished working on it.
I will do that and then move on.


----------



## VANESSA1

Cool


----------



## OBP

On the Appointment letter it says "scheduled audit for the years ", but at the moment we only have "Year", do you want to expand it so that you can enter something like 2011 - 2012?


----------



## VANESSA1

Yes, it needs to be expanded. The current assessment years are 2009 - 2011 that will be reviewed fro audit.


----------



## OBP

The Intro letter uses Dear taxpayer, do you want it to also use the contact's first name?
In the Discovery letter you have the Dear as well, use the same?


----------



## VANESSA1

On the intro letter, leave the "Dear Taxpayer". In the Discovery Letter/No Discovery Letter, we can leave those unbound as well.


----------



## OBP

Does that mean use the unbound field on the form with the default Contact first name in the 2 Discovery letters?


----------



## VANESSA1

Yes


----------



## OBP

On the Discovery letter there is the following section with a space for something to go in
"We have finalized your audit of your Personal Property Returns, and based on the review, we note the following;"
What is going to go in there and do you want it stored in the database?
Or just on the copy of the letter?


----------



## VANESSA1

This is where the numbers get pulled from the new asset listing and pooling report.


----------



## OBP

That is something that we do not yet have.
Can you remind of what data needs to be in it?


----------



## VANESSA1

I had to go back and look at stuff myself. I sent you a setup on a spreadsheet back on post #1253 called the Audit Asset Listing. We can start there.


----------



## OBP

Assuming we make an Audit Asset table, would the parts be the same as the parts in the tblassetlist table?
If not would some of them be the same ie some the same and some different?


----------



## VANESSA1

Hey OPB. Yes with differences. These assets do not fall under the 103-O Category for leased assets. These will be under 103-N. They will not fall under different locations, usually same main location. Fields like Site Code, Lease Number and PID will not be used. I'm invisioning as I look at the Physical locations tab on the database PID would be replaced by the Busienss Personal Property Tax Category which is a drop down list. Site Code can be replaced by the BPP y/n. Lease Number can be replaced by the MACRS LIFE which is a drop down list. Age can be removed period. Is this enough for now:


----------



## OBP

I will have a look and see how much sense it makes for me as I do it.
I envisage using a Combo to select from the current 103-N list to put the info in to the Audit Asset table and then create the report form that.


----------



## VANESSA1

Okay, lets go with it and see how it goes


----------



## OBP

In the database that I have there are currently only a few 103-N Assets.
So are they all going to go in the tblAssetList at some stage?


----------



## VANESSA1

I guess I'm wondering what you're looking at. At this point their only should be assets for the
103-O and the 103-T. If their are any in the 103-N, those companies should probably be changed to one of the above.


----------



## OBP

Download, unzip and then Import the query in the attached database.


----------



## VANESSA1

Okay


----------



## VANESSA1

Hey OPB, its not opening. Its downloaded and unzipped but wont open into Access


----------



## OBP

Is it the "desktop" folder or the Update database?


----------



## VANESSA1

It looks like when I go through the extraction wizard its not showing anything extracting. But It goes to the extraction complete and says its been sucessfully extracted and to show extracted file(s). When I go there it shows the Desktop Folder, when I click that it shows the Access database icon with the name Updates.mdb, I double click and it brings up the Open File - Security Warning; I hit open and nothing happens. I check my files for all my access folders and its not there


----------



## OBP

Is it on your desktop?
You could try opening the database in the folder and try exporting the query to your database.


----------



## VANESSA1

Okay Its there. Now what do you need me to do?


----------



## VANESSA1

I'm printing the data and taking a look


----------



## VANESSA1

Most look like data entry mistakes. There are a few that should be 103-O's, the rest should be 
103-N's with no asset data. How do you propose fixing?


----------



## OBP

You can do that at your leisure.
The question is how do we put in real 103-Ns so that they can be selected when you want to print the discovery report?
Are you going to use the MailLoc mainform or do you want to put them in on the Audit Letter form?
Are you going to put them all in or only those witha variation?


----------



## VANESSA1

Good Morning. Well, the first step is selecting which companies are going to be audited. There are many companies (besides the 103-O's and the 103-T's) that will not be selected for audit. That was mentioned in the word document. So can the ones that are selected for audit - the ones with an appointment letter be will be the ones that assets will be entered for and the subsequent discovery/no discovery letters will be printed. Did I ever send you my most recent database? I thought I did. On the MailLoc form it has the Form type as well as other modifications I made to determine whether or not the companies will be audited.


----------



## OBP

Can you show me screenprint of your current form?
Should there be an "Audit?" tick box in the MailLoc table/form to indicate those that require letters?


----------



## VANESSA1

Here you go. I'm trying to get other examples but the Alt/Print Screen won't work with the database


----------



## OBP

No I haven't seen that version of the form before, perhaps you had better send me a copy.


----------



## VANESSA1

Okay, I will email to you.


----------



## VANESSA1

Let me know if you have it. I sent it under that zip software


----------



## OBP

yes I have received it OK.


----------



## OBP

You have the "AUDIT CATEGORY" and "NO AUDIT" fields in the MailLoc table, is that where you want them?
If there is more than one reason for not auditing you will need a subtable/subform instead of a field or a field that will hold more than one value.


----------



## VANESSA1

Not quite understanding that but okay you're the expert here. I thought it would show which companies are going to be audited versus those who will never be audited. Also, with where they are located here interfere with what you've already done?


----------



## OBP

I am leaving them in the MailLoc table as it is not problem.
But do you need to be able to select more than one reason for not Auditing a company.


----------



## VANESSA1

No. One reason should suffice as the reasons cover for all the companies I have right now.


----------



## VANESSA1

Okay, if you look at record #5 Accent Apparel 09-2-00001. I have them in an audit category, but there not going to be selected for audited until a later date. Do you see that creating any issues?


----------



## OBP

No you can audit them at any time.
I will only list the 103-N companies that have the Audit Company ticked when creating the letter.
We could also go directly to the Audit Letter form from the MailLoc Main Contact Information tab.


----------



## VANESSA1

So, if I am reading this right, you're saying that If I check the audit company y/n (record number 6) that it will create the introduction letter?


----------



## OBP

It can do that if you want or it can just take you to the Letters form and you can select the intro letter.
The choice is yours.


----------



## VANESSA1

I think I like the option of it taking me to the letter form.


----------



## OBP

OK, there is also the option of clicking on the Audit Letters Button on the Reports Tab as well.


----------



## VANESSA1

Okay.


----------



## OBP

I am struggling a bit with how the Discovery letter is supposed to look like with the Report in place, can you mock me up one so that I can see how it should look?


----------



## VANESSA1

Okay, Let me work on that.


----------



## VANESSA1

Here you go


----------



## VANESSA1

Sorry that's not it


----------



## VANESSA1

Lets try this again


----------



## OBP

OK, that looks promising, where do the values for 2009 1900 and 2500 come from?
Will the 1900 be in the AssetList and the 2500 be in the Audit Asset list?


----------



## VANESSA1

The 1900 comes from their 2009 personal property return. Now here is something dummy me didn't think about. I need to be able to put their numbers reported on some type of pooling report to be able to pull and compare to the audit reports. What is sent to me are bottom lines for each pooling schedule, I don't see the detail. The audit asset table will supply that detail that will put the assets in their correct pooling. That way the bottom lines can be compared side by side. Once again, not sure if I'm making myself clear here.


----------



## OBP

I am not sure if you answered my question or not 
So the 1900 would come from the Pooling Report using the current AssetListing?
How would the 2500 be entered, in the Audit Letter Form?


----------



## VANESSA1

Okay, the returns I receive are basically just numbers reported for a given assessment year. So this company reported $1900 in 2009 I take their return and enter the numbers on a reported pooling report. Then I look at their documents received for audit for the 2009 tax year and enter their assets in the audit asset table. The numbers from the audit asset table are pulled to what basically identical to the Pool Main report, Modifications being the year being audited. Those numbers are compared side by side and if their is a discrepancy the difference is reported as well as a percentage in the difference. Let me work on display of what I'm trying to say.


----------



## VANESSA1

Hey take a look at this and tell me what you think.


----------



## OBP

OK, I assume that the Pool assessed value conditions on the 2009 sheet are those that applied during 2009?
It would appear that we need somewhere to store the Adjusted Values, can they go in the AssetList table?
Are they annually dependent, ie changing every year?


----------



## VANESSA1

Okay, I think I see what you are asking. And yeah, I can see an issue of storing those adjusted values as they do change every year. The assets that I put in the asset table from the documentation sent to me from the taxpayer for the audit. Also, I have a returns here already of what was reported from 2009 - 2011, but its just numbers that also needs to be stored somewhere.


----------



## OBP

It may be that I will need to store the TTV each year when you update them.
Do you need a "Returns" table or something?


----------



## VANESSA1

Would this be keeping track of the returns I receive? I'm thinking that is a good possibility in case someone decides to dispute a discovery.


----------



## OBP

Is there much data to input that is not already in the system?


----------



## VANESSA1

I doubt in the beginning stages (small companies) that there will be. But data input will grow with the size of the companies. I'm not seeing that being an issue in the near future. Are you seeing potential problems?


----------



## OBP

No, I was just thinking if there was masses of data to input whether scanning would be an option, but we had better stay with manual data entry for now. 
Can you send me a copy of the data so that I can see how it relates to out current data?


----------



## VANESSA1

This would be for a larger company but the format would be very similar. Do you need me to send a copy of a return I receive?


----------



## OBP

Is it a return?
If not then an actual return might be worth having.


----------



## VANESSA1

I emailed it to you.


----------



## VANESSA1

How are you? Feeling better?


----------



## OBP

Better, but not good thanks.


----------



## VANESSA1

Hey OPB, how is it going?


----------



## OBP

A lot better thanks, still got a nasty cough though.
I will try and get back to the database.


----------



## VANESSA1

Good Morning OPB, as my personal property season is starting, I was wondering if or how can the Labels New Companies report be modified since they now have control numbers. I need to print labels for all companies whose status = new


----------



## OBP

I will take a look and get back to you.


----------



## OBP

I am sure that you have asked this before, because the Query does not use "No CN" just the "New" in the NEW/CURRENT/CLOSED status.
You only need to change the Button's text or if you still need "No CN" then another button, query & Report.


----------



## VANESSA1

Okay let me take a look


----------



## VANESSA1

I think you're right, I should be okay. Thanks


----------



## VANESSA1

Hey OPB, Can I modify the labels report for a different style of labels? The ones I need are 3 across/30 per sheet


----------



## OBP

Can you do it, You set the Columns in Print Preview>Setup and then you adjust the Field widths & Record depth in Design view.
You will also need to play around with the margins and Report width as well.


----------



## OBP

See the attached report.


----------



## VANESSA1

Thanks I got it


----------



## VANESSA1

Hey I thought I had it and now I think I really messed up. The file you sent me would not open, so i thought I needed to import. So i went through the import process and deleted the old report. Now its not being recognized at all. When I click on the button it states "The report name "labels New Companies with no CN" you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist". when I tried it the way you suggested, the labels and data didn't line up correctly. Its starts out okay for the first couple of rows then it seems to move up lines into the previous label.


----------



## OBP

That is the Record Spacing in the report in design view, you need to make each record slightly larger.
Is the database I posted still in the Zip file, you could unzip and open it again.


----------



## VANESSA1

Good Morning, When I try to open up this report in print preview, I get an error "The record source "New Companies with no CN" specified on this form or report does not exist."


----------



## OBP

Did you change the name of the Query or delete it?


----------



## VANESSA1

Do you mean the original one in the database or the zip file you sent me?


----------



## OBP

The original query.
I can send it to you if you need it.


----------



## VANESSA1

Yeah I think I deleted it because i imported the zip file you sent me.


----------



## OBP

Here it is.


----------



## VANESSA1

Okay, went through the unzip process and it won't open


----------



## VANESSA1

Okay, I looked in my navigation pane and I see the query and the report. But when I click on the button, it says "The report Labels New Companies with no CN" you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist."


----------



## OBP

Try running the report manually to see if it what you want.


----------



## VANESSA1

For some strange reason, outlook is blocking the attachment as 'potentially unsafe'. I am in touch with the IT dept here to get it resolved asap


----------



## VANESSA1

Hey OPB, can you resend that email to me?


----------



## VANESSA1

Okay, thanks got it. Now this is what I'm coming across. When I open the query, I see the correct data. When I import it however I get the same error message


----------



## VANESSA1

The report ran manually line spacing is still off, so how do I fix that?


----------



## OBP

The line spacing is controlled by the "depth" of the overall record in the design view, try increasing the depth slightly.
If that doesn't work drag the bottom field down until you get the label spacing correct and then adjust the other fields to get even spacing on the label.


----------



## VANESSA1

Okay, I'll work on it


----------



## VANESSA1

I'm looking at the property sheet of the report in design view and not seeing "depth" or "line spacing". where else would I look?


----------



## OBP

There isn't one you physically have to drag it down, a bit like a form.
But you can set all the sizes of the Fields using the Properties, or again drag them to the size you want.


----------



## VANESSA1

Thanks again OPB, got the mailing done!


----------



## VANESSA1

Good Morning, I have received returns for one of my leasing companies and I was trying to update the dates when I came across a situation. On the "Prior To", I need to update to 3/1/09 and it tells me its not on list. Can you tell me where to change it at?


----------



## OBP

Have you looked at the TTV Tab?


----------



## VANESSA1

Yeah, that where I was trying to update the dates. Do I need to be somewhere else?


----------



## OBP

The actual Date periods are stored in the Date Periods table and Manipulated for displaying in the combo to say "Prior to " in the last column of the Date Periods Query, which interprets the date 01/01/1900 to be prior to.


----------



## VANESSA1

So, what do I need to do for the prior to dates? Because its telling me to select from listed items.


----------



## OBP

Open the Date Periods Table and change the date in there or if necessary create a new date period.
Do you want a form for the Date Periods Table?


----------



## VANESSA1

I print out this list and it has in it on #19 1/1/1900 and 3/1/08 - how come this doesn't work? I also see when I hit the '+' key it has Pool 1 with a TTV% of 20%. Is this interfering with anything? I'm sorry, I'm just trying to understand what I'm seeing.


----------



## VANESSA1

I thought this was the form for entering the date periods.


----------



## OBP

No that form is for assigning a pool to a date period and give it a percentage.
I don't think we did anything with the actual periods. I think I entered for you last year.


----------



## VANESSA1

Hmmm...well that probably needs to be rectified. So when I update in the Date periods table, will that mess with anything?


----------



## OBP

Does it mean that all the Prior to's will shift up one year?
Or are you adding another year to those that are already there?


----------



## VANESSA1

All the prior to's will shift up one year


----------



## VANESSA1

I am able to change the dates on the others, just not the prior to's


----------



## OBP

You can't change them in the Table?


----------



## VANESSA1

Nope


----------



## VANESSA1

I'm sorry I said that wrong. What I was unclear on in the Date Period Table is in the Line #19 that has the 1/1/1900 to 3/1/08. Was wondering if this was part of the prior to and will the additional information in the record at the "+" sign mess with anything?


----------



## OBP

No it shouldn't mess with anything other than moving all dates up 1 place. Try it, you can always change it back.


----------



## VANESSA1

Okay its late, Can we take a fresh look at this in the am because I'm not doing it well right now. I also know its late there.


----------



## VANESSA1

Good Morning. Okay looking at your post from yesterday I went to the Date Periods Table and entered the new dates. Now when I go to the TTV data tab I have 2 prior to dates in it when I only need the one. What also is interesting though is that its only doing it for pools 1 and 2.


----------



## OBP

Maybe you shouldn't have added them but modified what was there to make them what you want.
Take a copy of the database and delete the entries that you don't need.


----------



## VANESSA1

Okay, don't ask me what happened. I closed the database and reopened it and its fixed. Everything looks updated on the TTV data tab. I ran a sample pooling report and it looks fine.


----------



## VANESSA1

Hey OPB, I have a little down time here and was wondering how are the modifications going.


----------



## OBP

Not very well, I am having enough to do with keeping up with the Forum questions.
But I will try and something on it.


----------



## VANESSA1

Good Morning, How are things going?


----------



## OBP

Did I send you a copy of the database with the Auditing Mainform in it with the letter generation?


----------



## VANESSA1

No you didn't.


----------



## OBP

OK, will send you a copy, it takes the current master doc and creates a new one with the data in and saves it to a new file based on the Company and the date.


----------



## VANESSA1

Thanks, got it. Taking a look.


----------



## VANESSA1

Tried to select the Introduction Letter. Compile Error: User-define type not defined. It highlights "Set m_objdoc - New Word.Application"


----------



## OBP

OK, you need to set the VBA library Reference to MS Word, use VBA Editor main menu>Tools>References


----------



## VANESSA1

I'm looking at the References drop down ment and the closest I'm seeing is "Microsoft Word 12.0 Object Library"


----------



## OBP

That will do fine.


----------



## VANESSA1

Now it says "Word was unable to read this document. It may be corrupt. Try one or more of the following: "Open and repair the file" and "Open the file with the Text Recovery converter"


----------



## OBP

Have you "set the Path" to where you keep the word docs?


----------



## VANESSA1

I thought I did that before. I'll recheck them.


----------



## VANESSA1

Okay I did that. Does it need to be changed in the VBA because I'm still coming up with the same error msgs


----------



## VANESSA1

The other message that pops up along with the other one is:
"AVWiz12s.dotm is locked for editing by 'vpressler'
Do you want to:
Open a Read-Only file
Create a local copy & merge your changes later
Receive notification when the original copy is available"
When I click on this word comes up without a document.


----------



## OBP

It could be that you need to shutdown the computer to release the Word doc from the VBA code.


----------



## OBP

Have you looked to see if has changed either the Document or a saved version?


----------



## VANESSA1

I rebooted checked for the document and its there and now when I rerun it it says "Compile error: Sub or Function not defined"


----------



## VANESSA1

In the VBA area its highlighting "Private Sub cmdGetSource_Click()


----------



## OBP

Sorry when I transferred the forms etc in to your latest database I forgot a Module, it is in the attached Updates database.
Import it in to that copy I sent you.
Mind you it is only used for finding the file path to the documents.


----------



## VANESSA1

Its not working this way for some reason. Try emailing it.


----------



## VANESSA1

It needs to be a zip file. Outlook won't accept .mdb files.


----------



## VANESSA1

Good Morning, Added Module, set file paths still coming up with "Word was unable to read this document..." I even rebooted the system. Could it be the company I selected?


----------



## VANESSA1

Also, that AVWiz12s.dotm is locked for editing... is still showing up


----------



## OBP

Have you tried a different document?
I don't think it is the Company.
I am not sure what AVWiz12s.dotm does.


----------



## VANESSA1

I think i see a pattern here. When i tell it to create the letter it brings up H:\introletter\introletter.docIntro letter.doc I have something double in here but not sure how to find and correct it.


----------



## OBP

Does your "Path" include the document name?
As it should only be to the Folder.


----------



## VANESSA1

I see what its doing now. Eventhough my word document name is introletter.doc. its coming up with H:\intoletter\Intro letter.doc. So, when I hit OK, it comes up with that weird message


----------



## VANESSA1

Btw, the appointment letter came up okay


----------



## OBP

This line of code puts together the File Path and the Letter's name
fromfile = rs.templateFilePath & myletter

What I don't understand is how the appointment works and the Intro letter doesn't
Have you checked to ensure that the name is actually just Intro letter.doc?


----------



## VANESSA1

Option Compare Database
Private Sub cmdGetSource_Click()
On Error GoTo errorcatch
Directory = GetDirectory(Msg)
If Directory = "" Then Exit Sub
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
Me.templateFilePath = Directory
Exit Sub

errorcatch:
MsgBox Err.DESCRIPTION

End Sub
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

DoCmd.Close
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command1_Click

End Sub
Private Sub Command6_Click()
On Error GoTo errorcatch
Directory = GetDirectory(Msg)
If Directory = "" Then Exit Sub
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
Me.NewDocFilePath = Directory
Exit Sub

errorcatch:
MsgBox Err.DESCRIPTION
End Sub

Good Morning, this is the line of code that's coming up. Is there anything to be changed here? I'm not seeing the code you referenced.


----------



## OBP

That code is for selecting the path, not for creating the letter?


----------



## VANESSA1

I'm really not sure what you're saying here. Where would I look for the code that creates the letter?


----------



## OBP

Click on the Create Letter button in design view and go to the On Click event procedure.


----------



## VANESSA1

Okay, got it. It just has the company name. Do i need to reset the bookmarks?


----------



## OBP

You shouldn't need to.


----------



## VANESSA1

So is there a reason why I'm only seeing the company name in the letter?


----------



## OBP

Which letter is it and which version of the letter are you using?
Does it have the required Bookmarks?
If it hasn't it should say so in the VBA.


----------



## VANESSA1

It is the intro letter. Are the versions you're talking about refer to the 2000 vs 2007? I think it is the 2000 version because it has the .doc extension on it. Where is the VBA would it say so?


----------



## OBP

Yes it would be the 2000 version, but does it have the bookmarks that my copy has?
Do you want me attach my copy to be on the safe side?


----------



## VANESSA1

Good Morning. Yeah let's do that to be safe.


----------



## OBP

Here it is.


----------



## VANESSA1

When open it it says 'Intro%20Letter[1].doc (Read-Only) [Compatibility Mode] - Microsoft Word. What I'm most concern about is the %20. Should it cause any real concern?


----------



## OBP

It doesn't do that with mine, so I don't know what it means.
Can you save it as a new file and then rename it.


----------



## VANESSA1

I did save and rename and when I do the create letter I am still seeing just the company name. I see where the bookmarks are in the letter. Also, another message that pops up is "The requested member of the collection does not exist."


----------



## OBP

That is what is stopping the Bookmarks being filled, is there a "Debug" option?


----------



## OBP

OK, I am getting the same message, so something has changed since I first ran it. I will get back to you.


----------



## OBP

Should the Intro Letter have the same Heading as the other Letters?


----------



## OBP

Use this one.
We changed the bookmark names on the later letters by adding an M in front of the Address details.


----------



## VANESSA1

Good Morning, These is the result letter.


----------



## OBP

That looks good.


----------



## VANESSA1

So, I can just tweak it as needed?


----------



## OBP

Yes as long as you don't delete any bookmarks you can change anything else.


----------



## VANESSA1

Okay, cool. Now onto the apptletter. the first thing I noticed is that the path is the same as the introletter. Do I have to change the path each time?


----------



## OBP

Not if they are all stored in the same Folder.


----------



## VANESSA1

I thought that the suggestion in the beginning was to create folders or directory paths for each letter.


----------



## OBP

I don't think so, it is best to have them in one place so that you only have to select the path once.
Should you need ot move them you can re-select the path.


----------



## VANESSA1

Okay. put them all together then


----------



## VANESSA1

I'm getting the same messages as I did on the intro letter


----------



## OBP

With which letter?


----------



## VANESSA1

The appointment letter


----------



## OBP

I thought that one was working yesterday?


----------



## VANESSA1

Yeah I know. But when I moved it to the folder that the intro letter is in, it stopped working correctly.


----------



## OBP

Mine works Ok, did you fill in the dates and time?


----------



## VANESSA1

Yep. I remember your intro letter was named a little differently than mine in the VBA. Can you tell me where the code is that name the letter?


----------



## OBP

Depending on which letter you choose

If Me.Frame2 = 1 Then myletter = "Intro letter.doc"
If Me.Frame2 = 2 Then myletter = "appt letter.doc"
If Me.Frame2 = 3 Then myletter = "discovery letter.doc"
If Me.Frame2 = 4 Then myletter = "No Discovery letter.doc"
If Me.Frame2 = 1 And Me.Intro_Sent = -1 Then


----------



## VANESSA1

Yep that was the issue on that one. I made a print of the code so I would know how to change the next one. But on this one, the only bookmarks I'm seeing is the company name and ATTN:.


----------



## OBP

Which letter, Appointments?


----------



## VANESSA1

Yeah, its the appointment letter. I'm going to go ahead and check the others and get them moved as well.


----------



## OBP

Here is the Appointment letter with the bookmarks.


----------



## VANESSA1

Yay, it works


----------



## VANESSA1

It has some of the same format issues as the other one but all the data is there


----------



## OBP

That is because I just took your early rough drafts and added the Bookmarks. I am sure you can tidy them up to get what you want.


----------



## VANESSA1

Good Morning, I moved both discovery letters and am having same bookmark issue on both. Also, on the discovery letter where I have the "insert discover letter report", that should change at a later point, right?


----------



## OBP

What are the Bookmark issues?
The report is supposed to go in there, but I am not sure at the moment how to arrange it., it would be easier to have it as an attachment.


----------



## VANESSA1

Its Monday. I don't understand. I checked everything we talked about on the first 2 letters and its still not finding the last two. One thing I'm noticing is that when I go through and recheck the code and close it out. When I go back to the auditing tab two the selection letter checkboxes are grayed out but the Intro Sent and the Discovery Sent are checked. Would that make a difference?


----------



## OBP

That should only prompt a message if you try and send them again.
Are the name as of the letters correct and in the same folder as the other 2?


----------



## VANESSA1

That was the first thing i checked. Then I re-checked the path. I bought up the VBA, then I opened up the directory where the letters are located and compared the two. I know on Friday, I changed them to make them the same. I rechecked them just a few minutes ago again and they're the same.


----------



## OBP

Are the letter names the same, with .doc and not something else?


----------



## VANESSA1

Yeah, that what I checked. I have two monitors so I am able to put things side by side. This is what I'm seeing


----------



## OBP

The VBA is looking for "discovery letter.doc" you have "discovery letter*s*.doc"
I am not sure why the "No Discovery letter.doc" is not working.


----------



## VANESSA1

I'll try rebooting the system and see if that makes a difference. Be right back.


----------



## VANESSA1

Okay, figure this one out. the No Discovery Letter printed, but the Discovery Letter is still not printing. I'll compare it again.


----------



## VANESSA1

Okay, lets try this. Can you send me your Discovery letter with the bookmarks? Maybe it doesn't like mine.


----------



## OBP

Here is the Discovery letter, I have just tested it as OK.


----------



## VANESSA1

Yep, it didn't like mine. Now what's missing on the No Discovery letter on my end are the bookmarks. So I guess I need your copy of that one too.


----------



## OBP

Here it is as just checked.


----------



## VANESSA1

Alright Everything works! Now is there anything I can do with the other part of the database? What can I offer to make things simpler?


----------



## OBP

Can you look at the AuditletterAssetList table, AuditletterAssetList Query and AuditletterAssetlist Output query, that was where I was trying to create the Report for the discovery letter.


----------



## VANESSA1

Okay


----------



## VANESSA1

Would setting up a dummy company help?


----------



## OBP

You could use a current company with some dummy records in the AuditletterAssetList table.


----------



## VANESSA1

Okay, I'll work on it.


----------



## VANESSA1

I'm looking at the AuditletterAssetList table and I guess I'm confused. I don't see anywhere the reported asset costs vs the actual asset costs are going. On the discovery letter reported amount and corrected amount comes from the pooling. The variance is calculated if the actual TTV is greater than 5% of the reported TTV.


----------



## OBP

I assumed that the reported asset would be in the current tables, so you would only need the Variance stored to put in the report along with the original value.


----------



## VANESSA1

Here is the output I'm looking for. That would be based on what is pulled from both pooling schedules.


----------



## OBP

Going back to post #1600 and "The variance is calculated if the actual TTV is greater than 5% of the reported TTV.
" does that mean that there should be more fields in the original table or can we use the new table for "Actual TTV"?
Or is the Actual in the original table?


----------



## VANESSA1

Okay, I think I see now. I can use the orginal asset table to report what is submitted on the returns. Then I use the new asset listing table to enter what is on their audit documentation. But a question remains, how do I pull both totals together to calculate a variance? On the original asset table the numbers go to the pooling report. Can the same thing be done for the audit asset table?


----------



## VANESSA1

Hey Btw when you read this in the am I'll be out of the office here until Monday. My son's getting married this weekend.


----------



## OBP

We can use a query to calculate the Variance.


----------



## VANESSA1

So the same process used for the audit asset table to the pooling will be the same?


----------



## OBP

With some data in the table we can see if the current pooling queries would work for the new table.


----------



## VANESSA1

Okay I'm looking at the AuditLetterAssetList table and I'm trying to figure names vs types of data. To me, I interpret Asset ID as the a number identifying the asset. Var Pool is the correct pool asset is to be reported in andI'm not sure what ALID is. I dont see anywhere old vs new costs.


----------



## OBP

ALID is the Audit Letter ID that the asset applies to.


----------



## VANESSA1

I just put data in for the heck of it and in the AuditletterAssetListTable and it gave me the message "You cannot add or change a record because a related record is required in table AuditLeterSent."


----------



## OBP

Make sure that you give it the correct ALID, ie one form the AuditLeterSent table.


----------



## VANESSA1

It took it, now what?


----------



## OBP

Now we need a simple select query from that table (if I haven'r already made one).


----------



## VANESSA1

I see a couple. Audit LetterSent Query, AuditletterAssetList Query.


----------



## VANESSA1

And AuditletterAssetlist Output


----------



## OBP

Use either AuditletterAssetlist Output.
Add the Audit LettersSent table to that query and then add the MailLocID field to the output, that should allow you to then add the Mailloc table as well.
This is where it gets a bit tricky as I am not sure whether we need the Mailloc and physloc tables as well as the Asset list table to create the same type of queries as the TTV queries.


----------



## VANESSA1

So, looking at the AuditletterAssetlist Output query in design view, how do I add the Audit lettersSent table?


----------



## OBP

In Access 2003 there is is a Plus Symbol with a small square with 4 dots in it, which represents the table.
When you click on that it lists the tables and queries.


----------



## VANESSA1

I know I can remove this later, but I went to show table and put in the Audit LettersSent table, showed the MailLocID field and ran it. It shows on the output.


----------



## VANESSA1

Unclear on what you are saying on the second part of post #1617.


----------



## OBP

Well we need to compare the audit value to the original TTV value, so I assume we need to combine the query with one of the TTV queries.


----------



## VANESSA1

Good Morning, Yeah I'm looking at those TTV queries. I forgot we had that many.


----------



## OBP

Do we compare to the overall totals or to the individual Asset?


----------



## VANESSA1

I guess I'm not sure how to combine the queries without the audit asset listing.


----------



## OBP

But do we compare individual Assets or the overall Assets Total for the company?


----------



## OBP

Actually as we will be storing the AssetID in the auditing asset list we can link directly to it.
I am currently working on how to filter the AssetList combo to only get the Assets foe the Audited Company.


----------



## OBP

I am going to send you a new copy of the database, which has the AuditletterAssetList form on a new tab on the Audit letters mainform.
The combo only allows you to select the Assets for the Company chosen on the First tab where you start creating the letter.
This will then provide a direct link to compare the Audit value to the original declared value.
Do you think that will work to start comparison process?


----------



## VANESSA1

Good Morning, I'm sorry about yesterday. There were system issues here. In answering post #1674 & 1626 - Both. Once a variance is determined based on TTV's which the taxpayer incorrectly reports, they are going to want to know what assets need to be reported where. 

In response to latest post. You had me do a couple of things to the previous version. Do you want me to disregard these?


----------



## OBP

Leave them in for now and take a look at what I sent you and let me know if that is a start to getting the items that vary.
Do you currently do it manually?


----------



## VANESSA1

Grrr. Still having system issues here. Anyhoo, I think I just received a return for a perfect sample company. So, let me get it processed on the regular stuff, then I'll do a full walkthrough on the audit stuff.


----------



## OBP

Great idea.


----------



## VANESSA1

I just sent you an email of the return data


----------



## VANESSA1

How is it going?


----------



## OBP

Not very well at the moment, I am way behind on 4 databases and losing too much time to other things.


----------



## OBP

I am looking at the pdf Document that you sent me. I notice that the Audit Asset Items have value information going back to 2003.
Is that consistent with the Current AssetList data?


----------



## VANESSA1

Yes it is. Its the dates of acquistion along with the TTV% used with pool data


----------



## OBP

OK, I will try and put that data in to the AssetList and see how it goes.


----------



## VANESSA1

Hey OPB, in the middle of my tax season here. Got a situation. Got a pooling report not balancing with the asset listing. I ran the numbers on my spreadsheet and it agrees with the asset listing but for some reason, its not totaling all the pools. The record is 12-2-01996 Check Freepay Corp.


----------



## OBP

I will have to download the database from my email account, my old laptop died and I don't have it my new yet.
I will get back to you.
Will it be in that copy?


----------



## VANESSA1

Hmmm. I'll email a zip file to you.


----------



## OBP

OK, got it, your follow up list is much shorter than it used to be? 

I have this computer running with Access 2007, which I am still finding my way around, it has some good features but finding your way around the simplest things is much harder.


----------



## VANESSA1

Yeah, started follow up list all over for current year. And as far as 2007, its probably why I was struggling trying to do the simple things when all of this first started.


----------



## OBP

The overall total appears to be correct, it seems to have placed an Asset in to Pool 3 instead of Pool 2.
The asset value is about $123.


----------



## OBP

NO, Pool 3 is correct the Asset value is $522.00.


----------



## VANESSA1

If you look at the date summary final assessed value and the pooling report they don't match. Its like it didn't do a final assessed value of all the pooling.


----------



## OBP

But there are 2 of them


----------



## OBP

What is the correct value?


----------



## OBP

One of the Assets (EPSON PRINTER BUNLE) is set to Inactive Asset.


----------



## OBP

Can you lay it out for me as I can't see what is wrong.
I have looked at the base data and it all ties in as it should.
Can you give me the Values?


----------



## VANESSA1

The two companies on the asset listing has a total cost of $1909 with a final assessed valueof $572.70. The pooling report should reflect the same thing.


----------



## OBP

It reflects the $1909.


----------



## VANESSA1

Okay I know that each overall summary adds up to the $1909, but I'm not seeing that at the end of the pooling report.


----------



## OBP

Sorry, I still can't see it, the $572.70 is the Final Assessed Value which is what the Pool report shows, the Assessed Value is $338.55, which is also what the Pool report shows.
What is wrong?
Pool Report Final Assessed Values
$416.10
$156.60
$572.70 total
$


----------



## OBP

Is the Pooling Report supposed to give an Overall summary at the end?


----------



## VANESSA1

This is what I have.


----------



## OBP

That summary on page 2 of the Pool Report is for Pool 3, there is no overall summary on that report?
Did there used to be?


----------



## VANESSA1

I thought for sure there was.


----------



## OBP

Somewhere along the way the TTV Output Totals subreport has been deleted from the Main Report.
I will post it tomorrow as I have to go now.


----------



## VANESSA1

Okay, thanks


----------



## OBP

here it is


----------



## VANESSA1

Good Morning OBP! I'm still having issues with the pooling report adding more than 1 pool. The asset listing and the main frame numbers balance but the pooling doesn't. I've attached what I'm looking at with notes.


----------



## OBP

What is the cash amount for the error?


----------



## VANESSA1

Well, the pooling report is giving an erroneous value of $1,200 with a total acq cost of $2,000. The correct total cost should be $6,072.60 , 30% should be $1,821.78 with an assessed value of $2,265.01


----------



## OBP

Is that for the Northern Leasing System.


----------



## OBP

You didn't go to the 3rd page.


----------



## VANESSA1

My report only shows 2 pgs


----------



## VANESSA1

My bad. This is my page 3


----------



## OBP

Mine shows 3 with the overall summary, your page 2 only has the summary for each pool, this is the report that I posted on here recently.


----------



## OBP

We cross posted there.

The overall totals are correct, but it does not add them together to get the correct the final assessed value, I am not sure why at the moment.


----------



## OBP

Can you try this for me, open the report called TTV Output Totals.
Drag down the Report Footer down a couple of inches.
Highlight all the fields and objects in the Details section and drag them in to the Footer and save and close the report.
Run the pooling report from the Mainform.


----------



## VANESSA1

Okay, I'll try it.


----------



## VANESSA1

Should I do this in design view?


----------



## VANESSA1

Okay, I'm kinda lost here. I am in the design view, I dragged where it says report footer down but not sure how to drag the detail to the report footer because there is nothing below where it says report footer.


----------



## OBP

You have to click on the bottom edge of the bar that says Report Footer and drag it down, it should then have a white section of Footer.
If you can't get it done I will post what I have already.


----------



## VANESSA1

Okay, I got the footer section but how do I select all the fields in the detail section? Do I have to do them one at a time?


----------



## OBP

No, you can click anywhere on the detail "background" and drag the mouse over the objects to highlight them all.
Or you can click the first one and then hold down the shift key and click on all the others.


----------



## VANESSA1

I'm not having success here. I can select all the fields but I can't move them


----------



## VANESSA1

Hey btw you going to the jubilee?


----------



## OBP

I was not invited to any Jubilee functions.
I have attached the one I made earlier, I have put it in the new modules database.


----------



## VANESSA1

Do I have to re-save the database in the 2007 format? its not recognizing the extension.


----------



## OBP

I will have to repost one in Access 2003 format, or can you open the new module database in 2007 format and save it as Access 2003


----------



## VANESSA1

I saved the module as a 2003


----------



## VANESSA1

Okay I had to laugh at this one. On the import objects in the module tab, you have TonyAuditTrail. Anyhoo, I just select everything?


----------



## OBP

The Report called TTV Output Totals.

The TonyAuditTrail is my version of storing who does what in the database.


----------



## VANESSA1

Okay, I'll tell you what. Its late there I know and I'm kinda tired. I'll be in in the am and we'll work on it tomorrow. Talk to yu later


----------



## VANESSA1

Good Morning, I ran what you sent me yesterday along with that TonyAuditTrail which now I think I shouldn't have because now my asset listing isn't even balancing.


----------



## OBP

Which is odd because neither of them will have any affect on the Asset Summary.


----------



## VANESSA1

Boy I think I was REALLY exhausted yesterday or its another problem with a different company. But let's get this one cleaned up first. I just ran the pooling report with the formats you posted to me and it's still not right.


----------



## OBP

What values do you get and what should they be?


----------



## VANESSA1

I am getting a total cost of $4,072.60, a total assessed value of $1,065.01 and a 30% of 1221.78. Its like it is totally missing the pool 3 numers. It should be total cost of $6,072.60 and asessed value of $2,265.01 with a 30% of $1,821.78


----------



## OBP

Import these reports and replace the 2 old ones, copy of TTV total output is new.


----------



## VANESSA1

I'm seeing 3 reports with "Copy of TTV Output totals" do you want me to do that one too or just ignore it?


----------



## OBP

That one as well


----------



## VANESSA1

okay


----------



## VANESSA1

Nope no good.


----------



## VANESSA1

Hey OPB I'm getting ready to leave. I'm zipping the file to send to you. Maybe something else is going on.


----------



## VANESSA1

Hey OBP, that was it!!! It balances


----------



## OBP

At Last.


----------



## VANESSA1

If you're around today, I need a report for my companies whose status = New. What I need is:

Company Name
DBA Name
Address1
City, State, Zip
DOR Date DOR Number
Notes


----------



## OBP

Try this, Query and Report Required.


----------



## VANESSA1

Okay we have issues of course. I have a table that was created called "Name AutoCorrect Save Failure". When I open it, it has Fields called "Object Name" with New Companies - Object Type Report. Failure Reason, could not open


----------



## OBP

Can you open the Query?


----------



## VANESSA1

Yes


----------



## OBP

Can you view the report in design mode?


----------



## VANESSA1

I'm not even seeing a report with today's date on it.


----------



## OBP

The Report is called "New Companies" and it is in the Updates Database along with the query of the same name.
Do you have that Query?


----------



## VANESSA1

The query has today's date on it. I'll rerun the updates. This is not a good day. Now I have a message "Microsoft Office Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file. I'm the only one here lol!!! It looks like its trying to import the New Companies Report when I get the message.


----------



## OBP

Sounds like you need to close Access and try again.


----------



## VANESSA1

Believe it or not I did...doing it again...Also the main server is acting quirky too...I'm laughing because its just too bizarre for a Saturday


----------



## VANESSA1

Same message


----------



## OBP

I hope they have a backup, I don't like it when databases start acting weird.
You could try taking a copy and compacting & Repairing the copy.
Or create a new blank database and import everything in to it.


----------



## VANESSA1

Are we talking about my database?


----------



## OBP

Yes.


----------



## VANESSA1

Okay how about this one. I closed the database which puts me at the opening screen for Microsoft Access, hit the drop down menu key, went to Manage, Compact and Repair Database, selected the database out of the menu, and it tells me "You attempted to open a database that is already opened exclusively by user "Admin" on machine ECBCDAVPRESSLER. Try again when database is available.


----------



## OBP

That is what I was afraid of, it could be corrupted. You need to contact your IT dept to check if anybody has the database open.


----------



## VANESSA1

Okay, that's my que. I'm going home. I know. I sent you a copy last week. We may end up using it. But I'll talk with the IT department first thing Monday am. Well, thanks anyway. As always, you're a gem.


----------



## VANESSA1

Hey OBP, things should go better today. I emailed IT on Saturday and got a response when I came in this morning. They did take over the system to do some changes. Basically wiith no notification to us lowly users over here. Anyhoo, I got it back and imported the updates. I guess now I need to know how to run it.


----------



## OBP

If it gives you what you want you just need to add another Command Button that opens the report to the Reports Tab on the main form.


----------



## VANESSA1

Did I do that myself or was it in a query?


----------



## OBP

Was what in a query?


----------



## VANESSA1

I don't think i set up the buttons on the reports tab. So I thought you had done it in a query for the new companies.


----------



## OBP

I set up the Report Buttons, but there is no reason why you couldn't do so using the Command Button Wizard, it asks what you want to do, you say open report, it asks which one and you highlight the new.

Have you manually run the report?


----------



## VANESSA1

I double clicked on the report icon and it gives me "Enter Parameter 
value" MNAME.


----------



## OBP

It doesn't when I do so.
Do you have the Query imported, if so double click that and see if you get the same message.


----------



## VANESSA1

When I click on the query, I get the data but the headings are reading Expr1....Expr11


----------



## OBP

Have you changed the Table field names?
I would delete the query and import it again.


----------



## VANESSA1

Nope haven't touched the table field names. What about importing again and replacing?


----------



## OBP

That is what I suggested?


----------



## VANESSA1

Did the import/replace...same result


----------



## OBP

I will send you the database.


----------



## VANESSA1

Hi there. I got the database, I have made so many updates in the current one how can I keep what's been done?


----------



## OBP

Just import the query and Report from the version that I sent you.


----------



## VANESSA1

Yay (clapping) I got it!


----------



## VANESSA1

Hey I'm looking at the output and I noticed that I did some of these already and they are now marked current. Do I need to refresh the data?


----------



## VANESSA1

That seemed to work. I'm also noticing that the database is kinda slow when running queries...is that because I'm growing?


----------



## OBP

Probably, You should do a Compact & Repair to see if that helps.


----------



## VANESSA1

Hello, having issues this morning. I'm trying to add a record to the database and an error comes up "object invalid or no longer set" and doesn't go away when I hit ok


----------



## VANESSA1

It went away then gave me a blank record but still pops up when I tab fields


----------



## VANESSA1

The message just randomly pops up the help site says it has something to do with the object code.


----------



## VANESSA1

Definitely problems. The search tab is all messed up. Only #Name? in all fields


----------



## OBP

I would need the database in it's current state to try to get to the cause of that kind of error.


----------



## VANESSA1

Okay, as it stands now I'm gonna have to reboot just to get it to go away. I'll be right back.


----------



## OBP

Strange that it should start like that when it was running OK, has anyone made any changes lately?


----------



## VANESSA1

Well, it was running slow but ok when I left last night. The slowness might have been a symptom of of something else.


----------



## OBP

It works fast & great on my computer, including the Search Tab..
Are you having Server issues?
Try putting it on your computer and trying it.


----------



## VANESSA1

Hmmm. This seems to be a Saturday pattern doesn't it? The main server seems to be fine. But I'll email IT again.


----------



## VANESSA1

Hi there. Got the season all wrapped ahead of deadline Yay! Now ready to continue with the auditing portion of database.


----------



## OBP

It is good to know that the database is working and aiding efficiency.
We will have to discuss where we were and what still needs doing.


----------



## VANESSA1

Well, hello there  I'm just reviewing from where we left off back in March trying to get it to do what our posts said its supposed to do. I have a question however, is there a way I can get a screen print in MS Word of the folders with the letters?


----------



## VANESSA1

Okay, figured that out, just trying to remember how I set the file paths.


----------



## VANESSA1

Here is a screenprint of the error messages. I can't remember what I did to remove the double entry


----------



## OBP

It has to do with the File Path, do you have them in a table?
If so remove the actual document name and just leave the Path to the document.


----------



## VANESSA1

When I look at set file path it only has the template and document folder which is one in the same. I don't remember how I was able to remove the actual document name.


----------



## OBP

Can you send me the database?


----------



## VANESSA1

Okay.


----------



## OBP

Try adding a \ to the Path name in the File Paths form.


----------



## VANESSA1

Got the intro letter to print, but here is what I'm seeing with the appt letter


----------



## VANESSA1

The other letters are printing fine. just the appt letter


----------



## OBP

It sounds like it was opened before, which can lock it.
You may need to close both Access and restart windows to "release it".
Or you could delete it if you have a back-up copy and then replace it with the back-up.


----------



## VANESSA1

I remembered this problem before. I went back to previous posts, and it is the way its identified in my folder vs how it is in the vba. All fixed


----------



## VANESSA1

Also, I need to update years audited to say 2010 - 2012. I went to the audit years table and added 2012 but want to know how to change it on the form.


----------



## OBP

If I remember correctly you just type it in the field on the form.


----------



## VANESSA1

Okay.


----------



## VANESSA1

A formatting issue on the intro letter. i am noticing that there is no current date therefore no current date is showing up on the form. Is there a way to correct this?


----------



## VANESSA1

Another thing I'm noticing, when I get the letters ther is no MS Word menu to print them. They're showing up as read-only with that "AVWiz12s.dotm is locked for editing by 'vpressler'" message again.


----------



## OBP

You said "there is no current date therefore no current date is showing up on the form", where is there no current date in the letter or on the form or both?
If it is on the letter you can add it to Word Doc Template, if it the form we can add a field with the Control Source set to Date().
Are you seeing the word doc when it is created?
If so do you save it?


----------



## VANESSA1

Okay. I select the introduction letter, I hit the create letter button. It says "This letter has already been sent Do you want to send it again?. I hit yes. it gives me H:\introletter\introletter.doc. I hit ok. That's when it gives me that weird message I told you about also on the screen print I sent on Post #1758. I tell it ok, it brings up the document in Compatibility Mode with no menu available. I go to close it and it asks me if I want to save it and I say no because of the format.


----------



## OBP

You should save it any way and then delete it. That will close the open document.


----------



## VANESSA1

When I do that it brings up my Save As with a wrong File name. Its not bringing up the folder from the selected path or recognizing the company name


----------



## OBP

OK, can I suggest that you close the Access Database and try and open the documents in Word to see how many copies it has tried to save?
I think you need to unlock the locked documents, as I said you may even need to restart your windows ig Office won't allow them to be unlocked.


----------



## VANESSA1

I rebooted the entire system. I didnt see anything in the folder for "sentintroltrs" however I found 2 letters in the "sentapptltrs" folder when we discussed putting everything in one folder. That happened back in November last year. As I'm looking at the Word Document file paths form, they both have H:\introletter\. Would this create a problem or should I change the name of the folder where the documents should be sent


----------



## OBP

I would separate them so that there is no chance of the templates being overwritten.


----------



## VANESSA1

It worked!!! Now I'll work on the other ones. Still having that date issue however


----------



## VANESSA1

I'm gonna go for today. I'll be back next Monday. Going out of town tomorrow. Have a great week!


----------



## VANESSA1

Hey OBP, just reviewing emails we sent each other back in april on the auditing trying to remember what we did. You wouldn't have those emails? Or do you need me to forward them to you?


----------



## OBP

I have the emails thanks.


----------



## VANESSA1

I'm having a hard time remembering what we were doing back then


----------



## OBP

I thik we were trying to decide what was going to be saved in the Audited Assets.


----------



## VANESSA1

Okay I entered some dummy assets in the asset listing and went to the audit tab and saw the drop down button of where those assets were updating accordingly. Now where do I enter the pooling reported on their return?


----------



## OBP

I can't actually remember, I will have to take a look and get back to you.


----------



## VANESSA1

Hey OPB, the powers that be made small change to my procedure. They only want me to do one year instead of three for now. I think they will probably do the 3 years when I get into the larger companies.


----------



## OBP

OK, suppose we will have to go back to what you currently do manually and see what the database does in comparison.


----------



## VANESSA1

I don't think much will change except for the amount of data I enter. I've already entered on the asset listing. Now I need somewhere to enter the pooling as reported on their return.


----------



## OBP

What does that look like, can it just be an ordinary field along with the Asset?


----------



## VANESSA1

This is the format for the discovery letter. The first pooling report is derived from the asset listing. The second pooling schedule is from what I get from the returns. The third pooling report is the variance which calculates the difference between the two. What is in the letter are cells I copied to the letter to show the difference.


----------



## VANESSA1

In answering your previous post, it is identical to the pooling report, difference being its just the numbers with no assets attached.


----------



## OBP

Can't you use the Var Pool filed in the AuditLetterssetList table?


----------



## VANESSA1

As I am looking at the table, I guess I'm wondering 1) how this ties into what i entered into the asset listing? and 2) how do I get a variance without having the return the taxpayer reported?


----------



## OBP

If you put the taxpayer reported Pool in the Var Pool we can add a "Variance" field to show the difference if required, I would have thought that the difference in Assessed Value would be the important part and you can have a field for that as well if required.
Basically that table can hold whatever you need it to and is currently shown on the AuditCreatingLetters form as a form on the AssetList tab.


----------



## VANESSA1

I think I remember this from before. When I attempt to change the AssetID, it tells me "you cannot add or change a record because a related record is required in 'tblassetlist'. So where do I put the numbers?


----------



## OBP

Which numbers are you referring to?


----------



## VANESSA1

On the attachment I sent you the submitted return is what i need to enter. When I look at at the table and see AssetID, and try to change it, I get the error message. So, I guess I'm not understanding what the AssetID is.


----------



## OBP

The assets must be in physloc table, so when you select the company on the front audit letter tab it will list their assets in the combo box on the assettlist tab, see the second record to see what I mean


----------



## VANESSA1

What second record?


----------



## OBP

I am looking at your version 1.4 and the AuditCreatingletter form has a second record and it is for "GE CAPITAL COMMERCIAL INC"


----------



## VANESSA1

Okay, I'm seeing on the AssetList tab it does have an asset that was recorded on the phyloc tab which is what I had done with the other company. I think I see where you are going with this. The problem is there is no way i can tie one asset to a variable pool. If you look at the return submitted by the taxpayer, I only see numbers , no assets. Look at my zip file I just sent you.


----------



## VANESSA1

Oops I sent through forum by mistake.


----------



## OBP

Surely this goes back to the original data in the Physloc table which we used for the pooling report.
How do you manually identify the Assets form the pdf document you sent me to work out any discrepancies?


----------



## VANESSA1

If there are discrepancies, Can I do an asset summary report as is also used for the leasing companies that shows the assets that were used in the audit?


----------



## OBP

We should be able to use the query that supplies the summary report to supply the same Assets for you to work with in the audit letter, I will have to think about it.


----------



## VANESSA1

How is it going?


----------



## OBP

I am a bit busy on other things at the moment, so I am not on the computer much. I will get back to it when I have a chance, give me a reminder each day to keep the post live.


----------



## VANESSA1

I can do that ;-) Talk to you tomorrow.


----------



## VANESSA1

Good morning OPB - here anyway. Any great seeds to wisdom?


----------



## OBP

I haven't had a chance to look at it today, I am Decorating my sons house again this week after they had to have a wall replastered, so I am away form the computer for most of the day.
I did have one thought about it and that was do we need to store the data, or just use the Physloc table data?


----------



## VANESSA1

I thought the Physloc table was just for those addresses that were different from the MailLoc table. What is needed is another pooling schedule that reports what is received on their personal property returns. And then to compare the pooling reports for any variances. That's how I came up with the third pooling schedule that basically took the actual minus the reported on the cost side of the pooling.


----------



## OBP

OK, I will look at using the "Pooling" method in the AuditLetter Asset list, basically duplicating the physloc one where you can get a similar report, it may be possible to "Variance" report as well by comparing the 2.


----------



## VANESSA1

Hey OPB how is the house remodeling going?


----------



## OBP

Got the day off today it is my wife's birthday.


----------



## VANESSA1

Awww. Have a good day!


----------



## VANESSA1

How are we doing today?


----------



## OBP

As a matter of fact I am just looking at the Calculations in the tblAssetList subform as I assume they need to be carried out on the Audit subform using the "corrected" Pool values or are the correct values the current tblAssetList ones?
Either way I assume it needs calculating.


----------



## VANESSA1

Let me make sure I'm reading you correctly. Yes the tbleAssetList subform would be the correct values. That pooling report shows what the current assessment should be. And the assets I created on that subform is on the AuditAsset List. Now what is needed is a pooling schedule that shows what the taxpayer reported and another schedule showing the variances in each pooling schedule.


----------



## OBP

OK, so I have added the Audit Assessed value to the subform, so I now need to calculate what that value is based on the Pool on that form rather than the correct value in the tbleAssetList, unless of course you can just type it in because you have it?


----------



## VANESSA1

I emailed you the spreadsheet so you can see the formulas and how they are pulling


----------



## OBP

So would you type in to the subform the values in column "N"?


----------



## VANESSA1

Yes


----------



## OBP

Who is the Excel worksheet for?


----------



## VANESSA1

The company I'm doing is E & M Fulfillment Solutions 09-2-00002


----------



## VANESSA1

How is it going?


----------



## OBP

I have just got back from my Son's house, so I haven't managed to do anything today, I might get a chance after I have eaten.
I will try replicating the data from the Excel spreadsheet.


----------



## VANESSA1

Cool Thanks!


----------



## VANESSA1

Good Morning!


----------



## OBP

Hello, I still haven't found any time, but I should be able to do something this weekend.


----------



## VANESSA1

Okay


----------



## OBP

When they send in their assessment of the assets do they identify which "Pool" years they think the individual items go in to?
Or do they just provide the data as you have shown in the Excel Sheet?
If it is the latter we have a problem, because we are currently looking at assets for auditing, whereas we would need to look at "Pool Year Totals" for the comparison.


----------



## VANESSA1

I just emailed you a portion of a return. This is all I see to enter into the main system. I guess I'm kinda unclear as to what you mean by "Pool Year Totals".


----------



## OBP

The Pools Totals that I am talking about are the ones in that document you sent me ie 1169, 500 & 2049.
What we don't have is the Asset Items that make up those totals as seen by the owner, which was how I set up the table and form. So now I need to set it up so that you can enter the values as you receive them.


----------



## VANESSA1

Okay, sorry about that. Yeah, that's why the audit so I can see how they are coming up with their figures and see if they are reporting correctly


----------



## OBP

I don't think the Audit Assessed Value field will actually be of any value in the AuditLetterAssetList table as it can be seen from your examples that you have some cash values move from the owner assessment to different Year Ranges.
The Audit Assessed Values are actually held in the tblAssetList, what we need to do to re-produce your Excel sheet is to compare the Pool Year Range totals and establish the Variance for the last set of columns.


----------



## VANESSA1

That's why I was confused as to what i was seeing on the AssetList tab.


----------



## OBP

Shall I delete that field then?


----------



## VANESSA1

You mean the AssetID field?


----------



## OBP

No The asessed Value


----------



## VANESSA1

Okay. Lets try it and see what the end results are.


----------



## OBP

I have input some data, I will send you the database later.


----------



## VANESSA1

Okay


----------



## VANESSA1

I got it. Okay. I'm looking at what I would enter. The audit assessed value would be pulled form the audit list?


----------



## VANESSA1

Another thing I thought about. Should I enter test data into the asset lisitng?


----------



## OBP

I have entered the data from the Excel sheet to show you what you would enter.
I now need to create the Query to compare the 2 sets of data, that in the tblAssetlist Pool Report and the AuditLetterAssetlist, or possibly put the Pool report data in to the Audit table?
It may be that we should enter all the "Date Period" values for the audit data including the zeroes so that we can compare like with like.


----------



## VANESSA1

Good Morning, I can see that logic. As I'm looking at the Asset Value List, I know my test data had some values entered in some date periods not reported by the taxpayer.


----------



## OBP

I could have all the "Date Periods" for the chosen Pool automatically created with the zeroes then you would only need to enter the values supplied by the owner.
We would then have the like for like comparison.


----------



## VANESSA1

Okay that'll work


----------



## OBP

I will see if I can get it done after picking up the grandkids.


----------



## VANESSA1

Cool. Good luck with that. I don't get much done when grands are around.


----------



## OBP

OK, I have the VBA code to put the Pool Years in to the table and form working as discussed.
I will now work on putting the Pool Report actual value and Assessed Value in as well, that would be a bit of duplication but would bring all the data together in one place for your Audit Output.


----------



## VANESSA1

Okay


----------



## VANESSA1

Good Morning! How is it going?


----------



## OBP

Nearly there, I am working on updating the ACQ and Assessed Costs with the VBA code.


----------



## VANESSA1

Cool.


----------



## OBP

I am sending you the database in a few moments.
Open the Audit Creating Letters form, click the Asset Value List tab and then enter 3 in the VAR Pool field.


----------



## OBP

Sorry, I forgot to mention, go to the "AuditletterAssetList" table and delete the records first, I forgot to do it.


----------



## VANESSA1

It's a crazy Monday. I'm not seeing any tables, just forms.


----------



## VANESSA1

Never mind.


----------



## VANESSA1

Do I just repeat what I did on the spreadsheet?


----------



## OBP

When you enter the Pool number it should create the Pool date periods and populate the fields with the Assessed data from the tblAssetlist table, all you need to do is enter the data from the Owners Audit sheet.


----------



## VANESSA1

Good Morning. Okay, did that. One thing I am noticing is the layout of the pool year. 3/1/10 - 3/1/2011 is supposed to be at the top and descending to the 3/2/01 - 3/1/02. I see the prior years on the bottom. Is this just a cosmetic thing?


----------



## VANESSA1

Also, do I tell check the variance box?


----------



## OBP

If there is a Variance between the Audit value and the owner's value then I would check the box.

The Date order is as I created them, they can be reversed if required.


----------



## VANESSA1

Okay.

That's intersting because when I bring up a pooling report, the dates are in descending order.


----------



## OBP

Yes, well the VBA uses a different query, but as I said it can be changed either in that query or in the Form's query that displays the data.


----------



## VANESSA1

Can you change the form's display query? Its the way the forms are printed on this end in descending order.


----------



## OBP

In this Query in design view
AuditletterAssetList Query
Set the Pool Year to Descending.


----------



## VANESSA1

Okay, got it.


----------



## VANESSA1

So what else needs to be done?


----------



## OBP

I think all we need is a report, does it have to be exactly the same as your Excel worksheet?


----------



## VANESSA1

I had this on my mind last night, a scenario that will pop up. When assets are reported in pool 3 and they should have been reported in pool 2 So my asset listing will show the assets in pool 2, but the taxpayer reported the assets incorrectly in pool 3. I'm sorry, I should have thought about this earlier.

As far as the reports, unless you had something I'm not thinking about in mind, then yes I would like it as close as possible to the spreadsheets, that's going to be part of the discovery letter.


----------



## OBP

For the owner where the Pools are at variance you would have to enter both pool numbers.
I thought you would say that about the report.
I will have to see if it is feasible.


----------



## VANESSA1

Okay. Sorry I just thought about this, dreamed about it really.


----------



## VANESSA1

How is it going?


----------



## OBP

Busy at the moment.


----------



## VANESSA1

Okay


----------



## VANESSA1

How is it going?


----------



## OBP

Just popped home for some lunch and I am now off back to my Son's house.


----------



## VANESSA1

Okay have a good day!


----------



## VANESSA1

How is it going?


----------



## OBP

I may be able to take a look at it over the weekend.


----------



## VANESSA1

How is it going?


----------



## OBP

Nothing new yet.


----------



## VANESSA1

Okay


----------



## VANESSA1

Good Morning! Anything yet?


----------



## OBP

No not yet.
I will definitely take a look between now and Monday.
But to create the equivalent of the Worksheet is going to take quite a bit of work and manipulation.


----------



## OBP

Do you ever use the 
9 TO 12 YEAR LIFE
Column?


----------



## VANESSA1

Well when I look at the entire spreadsheet, for my purposes no that column will not be used in any of the pools.


----------



## VANESSA1

Also, if you look at my spreadsheets, there is a column b 'adjustments'. I won't be utilizing that one either


----------



## OBP

OK.


----------



## VANESSA1

Okay, I'm trying to create an intro letter and I get an error message - "Word cannot start the converter mswrd632.wpc"


----------



## VANESSA1

Another issue - When I bring up another company, I get "run-time error 2465 application-defind or object defined error"


----------



## VANESSA1

Private Sub Combo13_AfterUpdate()
Dim position As Integer
position = InStr(Me.Combo13.Column(2), Chr$(32))
If position > 0 Then
Me.LettersDearentry = Left(Me.Combo13.Column(2), position - 1)
End If
Me.AuditletterAssetList.Form.combo8.Requery
End Sub


----------



## OBP

Have you double checked that your Word Reference in the VBA Reference Library is set to MS 2003?

For the Me.AuditletterAssetList.Form.combo8.Requery delete that line.


----------



## VANESSA1

How did we do that VBA Reference Library?

I deleted that line.


----------



## OBP

VBA Editor>Tools>References


----------



## VANESSA1

Duh  okay


----------



## VANESSA1

Good Morning. I remember doing that but I can't seem to remember how to get there. when I go to the References I see that the "Microsoft Word 12.0 Object Library" is checked. Is there a way I can check the code itself? If so how do I get there?


----------



## OBP

The problem doesn't appear to be an Access one but a Word one.
Can you open Word manually and the letter document?


----------



## VANESSA1

The documents are opening, just that message pops up every time.


----------



## OBP

Does it pop up when you manually open them?
Have you googled the offending item?


----------



## VANESSA1

The template is opening just fine.

Do you mean the error message?


----------



## VANESSA1

I did google it and I think I see what's going on. The templates have an .doc extension whereas the letters are being created and saved in a .docx extension. I will try to do the googled instructions and see what happens.


----------



## VANESSA1

Okay I'm back. Don't know what happened but it froze, I rebooted, and then got the general page for the forum.


----------



## OBP

Is it working now?


----------



## VANESSA1

Everything's working now.


----------



## VANESSA1

Question - can I save the templates to a .docx file because its saving the created letters as a .doc file?


----------



## OBP

You can but the VBA code will need to be changed to reflect the x on the end.


----------



## VANESSA1

Good morning. I kinda thought so. So where would I look in the VBA?


----------



## OBP

It is in the On Click event of Create Letter buton on these lines
If Me.Frame2 = 1 Then myletter = "introletter.doc"
If Me.Frame2 = 2 Then myletter = "appt letters.doc"
If Me.Frame2 = 3 Then myletter = "discovery letter.doc"
If Me.Frame2 = 4 Then myletter = "no discovery letter.doc"


----------



## VANESSA1

Hey I changed those extensions, now an old favorite error message coming up. "Word was unable to read this document. It may be corrupt. Try one or more of the following: *Open and Repair thei file, *Open the file with the Text Recovery Converter."


----------



## VANESSA1

I'm seeing in the VBA code there are some lines that have .DOC in them. Do they need to be changed too?


----------



## OBP

Have you changed the word docs to .docx?
There is a line that saves the document with a new name, you can change it to docx if you like, but I am not sure your version of word will actually do so.


----------



## VANESSA1

Hi there, been awhile. Got sidetracked here on a different project. Anyhoo, how is it going?


----------



## OBP

Did you get the Word Doc working OK?
I have also been busy on other things.


----------



## VANESSA1

Yeah, Ive been just saving the documents as a .docx and putting them in the correct folders.
Well, Ive got some of the smaller audits coming in. Just really started on them this week. I've got one already who gonna be caught


----------



## VANESSA1

Hey OPB on the creating letters form, when I put in a Audit Closed date, it just comes up with ##### and won't go away when I go to a different company.


----------



## OBP

Have you tried making the Field a little wider?


----------



## VANESSA1

I went to the table and changed the format there, I went to design view on the form and widened it there and its still showing up.


----------



## OBP

Still showing up as #####?


----------



## OBP

It should be set to Short Date format or make it much larger for long format.


----------



## VANESSA1

I did all of that. It shows up for every company.


----------



## OBP

I just entered a Audit Closed Date in Short Date Format and it shows up OK in the Form.
see Attached


----------



## VANESSA1

Not seeing attachment


----------



## OBP

Sorry it was too big.


----------



## VANESSA1

I just emailed you what I'm seeing


----------



## VANESSA1

Hey OPB, how is it going? I've been doing the audits on the smaller companies basically utilizing the spreadsheet model I sent and its been working very well so far. I've been thinking about the last challenge I requested and I need to scale it back. I can still use the asset detail to list their assets along with the resulting pooling reports. But then I will just use those numbers in the excel spreadsheets that have been created to do the variance reports. They seem to be working very well right now. How does that sound?


----------



## OBP

That sounds do-able, what reports do you want?


----------



## VANESSA1

The reports would be identical to what is used for the leasing companies, the difference being that in the main contact information where form type for these companies are 103-N. Is there way to fix this or just change the type to 103-O?


----------



## OBP

Yes you just take a copy and change it to 103-O


----------



## VANESSA1

Take a copy?


----------



## OBP

A copy of the query and then paste it with a new name, a copy of the report and paste it with a new name.
Change the Criteria to 103-O in the new and then change the new Report's Record Source from the old query to the new query.


----------



## VANESSA1

The difference is that these are 103-N companies. Will that be an issue? Other than that, it sounds good.


----------



## VANESSA1

Btw I got a new (out of the box) computer here. My display on the database is awfully bright. How do I change the colors on my forms?


----------



## OBP

Wouldn't it be easier to change the brightness once rather that change all the Forms?


----------



## VANESSA1

Well, its not on all of software I use, just Access for some reason.


----------



## OBP

You can change the Background colour of the form in the Form's Properties, each Section of the Form has it's own Colour control called "Back Color".
Once you have the Colour you like you can copy the Number and Paste it in the other areas and forms.


----------



## VANESSA1

Hey OPB how is it going?


----------



## CPJB86

Sorry to gatecrash.

Can anyone help me please?

http://forums.techguy.org/business-applications/1082575-help-needed.html


----------



## VANESSA1

Hey OPB, how is it going?


----------



## OBP

Happy New Year to you.
I am working on a Project for someone in Australia at the moment.


----------



## VANESSA1

Cool. Hoping to get things wrapped up here when you have time.


----------



## OBP

Can you remind me what you need?


----------



## VANESSA1

Well, the auditing database needs to be merged into the main database. I think that's the biggest issue at this point.


----------



## VANESSA1

Also, testing my new companies for labels, coming up with error message, "The report name "Labels New Companies with no CN" you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist."


----------



## OBP

That report was renamed to give New Companies with CNs.
You should be able to find a copy of the Report in an older version of the database and import it.
How do you want to integrate the auditing section?


----------



## VANESSA1

Well considering that I have done a few audits on the smaller companies, would I need to send you both databases where the auditing modules can be merged with the main database?

I'll look for that report


----------



## VANESSA1

I found the labels report but trying to remember how I put them in the right format as the labels are in


----------



## VANESSA1

Hey OPB, yeah the databases need to be merged. I now have companies that were added in the main database but not on the audit database so I can create any letters for them.

Also, do you remember how I was able to put the labels from the database into a correct avery format?


----------



## OBP

If I remember correctly I used Columns to set up the labels, do you not have a lables report that you can copy and change the record source for?


----------



## VANESSA1

Hmmm...I'll try a mail merge with Word.


----------



## VANESSA1

Yeah, got it. Exported the report to a word .rtf file


----------



## VANESSA1

Hey OPB, anything on merging the databases?


----------



## OBP

If you send me a copy of the database at end of work on Friday I will do something on it over the weekend for Monday.


----------



## VANESSA1

Do you want both of them?


----------



## OBP

Are they currently separate?
As I have always had them all in one database, it would then only be a case of creating a suitable Tab on the original Main Menu form.


----------



## VANESSA1

Yeah you sent me the auditing database while I still had the main database.


----------



## OBP

If you have been running them separately I would need them both.


----------



## VANESSA1

Okay, I will send on Friday before I get off work. Thanks


----------



## VANESSA1

Hey OPB, did you get my email?


----------



## VANESSA1

Hey OPB, How is it going?


----------



## OBP

I have your email, I haven't done anything with the databases yet.


----------



## VANESSA1

Okay


----------



## VANESSA1

Hey OPB, how is it going?


----------



## OBP

I still haven't got around to combining the databases yet.
Has any fo the data changed since you sent them to me?


----------



## VANESSA1

Yeah a little, not much. I am done with the first batch of audits for the 2011 tax season. Do you need updated ones?


----------



## OBP

Yes please.


----------



## VANESSA1

I just sent an email to you with the databases


----------



## OBP

Which Persprop database is the one that you are currently using for the Non Audit business and which is the one for the Audits (I assume the latter is 1.42)?


----------



## VANESSA1

Yeah the 1.42 is the auditing database and the other is the main database.


----------



## OBP

I have sent back the amalgamated database for testing.


----------



## VANESSA1

I opened it as a read only and went to the reports tab. I am not seeing any button for the auditing database.


----------



## OBP

Sorry, I didn't didn't import the Mainform, I assume it doesn't have any changes that will mess up anything else?
Can you open the Audit CreatingLetters form manually for now?


----------



## VANESSA1

Yes its opening fine. I'm able to bring up the newer companies that I audited so that's a good thing. Now when I try to create a letter I get a compile error - User-defined type not defined. I'm thinking that this goes back to the errors I had in the auditing database.


----------



## OBP

That could well be the VBA Editor's Reference Library "Microsoft Word" reference not being set as it was in the 1.42 version.


----------



## VANESSA1

So should I look at the VBA in the 1.42 version and adjust it accordingly, or will you?


----------



## OBP

It is the VBA References in the 1.12 version, I can do it and send you another version tomorrow.


----------



## VANESSA1

Sounds good to me!


----------



## OBP

I have Imported the Main form and set the Word Reference in the VBA Editor, however I can't do it for you because my version is for Office 2007.
So you need to go in to the VBA Editor (alt+F11)>Main Menu>Tools>References and Microsoft Word should say "Missing", you need to Untick it and then go down the drop down list and find your Microsoft Word and Tick it.


----------



## VANESSA1

Good Morning! I looked at the VBA Editor and the Microsoft Word 12.0 Object library is ticked. I'm not seeing anything in the VBA or references that say "Missing".


----------



## OBP

That is good, the VBA code should work now.


----------



## VANESSA1

Okay well see because when I create letter it states, "The Microsoft Office Access Database engine cannot find the input table or query 'File Paths.' Make sure it exists and that the name is spelled correctly." So I clicked okay and went to "Set File Paths". When I click on that one I get "The form name 'File Paths' is misspelled or refers to a form that doesn't exist."


----------



## OBP

OK, I missed that set of data.


----------



## VANESSA1

Lol...Oops


----------



## OBP

I have sent you another version.


----------



## VANESSA1

You have saved the day once again super-hero!  I know there are other things that we discussed but at the moment, I can't think of any. I'll just see how the next season goes which starts in a couple of weeks. Until then, enjoy your family and have rested days.


----------



## OBP

I hope the new season goes well fro you and the department.
I am maintaining a lower level of interest in Access programming by just answering the Forum questions and not embarking on major database creating projects.


----------



## VANESSA1

I understand that. I have been trying to work with the powers that be here just to get the training so I can understand Access more.


----------



## OBP

That would be good, but you will also need VBA as well later on.


----------



## VANESSA1

Hey OPB which table did I update the dates for the pooling?


----------



## VANESSA1

I'm looking at the date periods but not really clear


----------



## OBP

If I remember correctly you open the Date Periods Table and add the new Date Period for 2012/2013.
Which then makes that date period available on the Main form Tab for TTV Data date period Combo drop down.


----------



## VANESSA1

Thanks


----------



## VANESSA1

I update the date periods table and added the 3/2/12 and 3/1/13, but when I try to enter asset information, its not updating.


----------



## OBP

Did you go to the TTV Data tab to update the "Groups" of date periods?


----------



## VANESSA1

Bingo!!! Nite, going home


----------



## OBP

What is this, only working half a day?


----------



## VANESSA1

Lol...It was 4:00 pm my time..


----------



## valis

reopened per request.


----------



## Cookiegal

I'm going to close this back up again. I think you customized that primary key ages ago. It's preferable to start a new thread for different questions.

You'll find the new thread here:

http://forums.techguy.org/business-applications/1098083-database-pooling-issue.html


----------

