I work for a property management compnay and they have put me in charge of coming up with a way to go some what paperless. What they are wanting is a way to enter a work order for a particular client (i.e description of work to be done for maintenance). This would then need to be printed in a report to be faxed over to the workers. This info would then need to be pulled out and placed into an invoice where, where items could be entered such as items bought ets.
My company is willing to spend thousands for software, but I think it is insane. to do so.
I was thinking that surely this could be done in access. I have an understanding of how access works (forms, reports, tables, etc.) but I have never designed one like this.
It does not have to be complex and it does not have to have a lot of formulas, I was thinking that the total could be placed in field when the invoice info is entered. Only really calculation would be adding the total of the items.
Anyway is this something that can be done relatively easily by someone with my experience, or is this to complex.
I'd like to say thank you to those who helped me with questions I had on my database. Even though some of you didn't even realize that you helped. Searching the forum yields answers to many questions.
So now that the database is complete, I'd like opinions. I'm attaching it as a zip file. A couple of notes. I forgot to remove the database password so it's "fornax". Also, being that I work for a very large hospital I cleared out my table and removed the logos. I hid the menus and disabled the F11 key, but there is a hidden button on the far right of the footer. A double-click will restore the menus. Also try clicking on the logo--or it's place holder.
Okay, as mentioned in another thread, I was given a huge project involving MS Access. I need some opinions on what to do for this situation, and here's the situation. So far, unless suggested a better idea, I'm going to have two databases. One database will be the final version, the other will if I want to make additions, changes, etc. They will contain the same data as users will only be using one and a relationship between the two will be created. I can't remember how to do it, but I remember there was a way to prevent users from changing anything about the Access file with the exeption of what the form and the vb code allows. I plan on creating some type of login using vb. The only access to the database I want users to have is what options the forms I create give them. Please give your opinions/suggestions so I can figure out what to do.
I have very painstakingly worked out a new database and I'm about to present it to the departments who will be using it. At various times words like 'password' and 'locked' have been thrown out but as of now I have not implemented any security measures. I started reading up on the topic this morning and the general consensus seems to be that unless there is a sincere need for them the security process is excruciating. Does anyone have specific advice or opinions on access security? If I were to do anything, I think it would be to set up working groups only allowing users access to the parts of the database they are concerned with.
I would love any opinions or advice from people with experience of this on if I should recommend such measures or try to steer the department away from them.
Main table about 3000 customers with 18 fields Will input payments for each customer monthly and / or quarterly total about 12,000 per year. Plan to purge payments over 18 months old frequently. 1) Put it all in one table or break into two ? 2) As far as db size wise and efficiency, would it really matter ?
We all know we're not supposed to duplicate data right? So was wondering how people deal with the deletion of Master records.
For instance let's say I have 3 tables. tblSales tblSalesSub tblProducts
tblSales - lists the sales summary - stuff like SaleId, SaleDate, SaleTotal tblSalesSub - lists the products contained within the sale. tblProducts - is the Master list of products storing the product specifics
Now to prevent duplication of data I should just store the productId in tblSalesSub then reference that to tblProducts with an innner JOIN of some kind. But what if the Master record gets deleted? The reference is broken and NULL values are returned.
How do you deal with this? Don't allow deletion of these records?But just hide them when deleted? Duplicate data by storing the product title and description within tblSalesSub? Tell the user - tough cheese you shouldn't have deleted it should you? Any other ideas?
Hi all: I currently have a database with a list of clients, and it is to be accessible to others to enter information on clients and to add new ones. However, I want to make it so that the user who wants to add a client can easily search the database for that particular client so that they won't enter a duplicate.
I have created a search function on my form, but then it occurred to me: what if there are two people with the same last name's? At first, I wanted a search operation to display a list of client names, along with their respective ID numbers and possibly a link next to each that would take the user to the corresponding record.
However, I have no idea how to do this, and I'm unsure as to whether or not this is the best way to tackle the problem. Please, someone show me the way!
Am fairly certain that I will be asked to design and implement a Machine Status Application.
Description below is very simplistic but believe you will get the idea
Basically simple stuff at Machine Operator. He clicks a command button on a display that signifies machine is okay or not. This updates machine status. Image it just toggles between Running and Stopped.
Each minute little process runs to at 1 to Run value or to Stop value. Okay so this will get a little more complex for sure.
Now I could end up collecting data on say 100 machines. Okay so I would not expect the status change to occur any more than once per hour on average so it would be a bit like having 100 very very slow users on the system. Now the crunch. What effect (if any) would these 100 very very slow users have on performance and particularly the One much quicker user who is executing preset queries one after another for say 20 seconds every minute.
Does anyone know of a listing or a publication/website where I can place an ad for an expert to develop an Access database? We need someone to do this work for our organization.
I am an inexperienced user, part of my job is to up date data files daily, have been doing so for 7 months. Problem arose when I accidentally deleted a link and required IT assistance trying to locate where it had gone-- that's when we discovered that I was not updating the company-wide server but a separate shortcut that a previous (and briefly employed individual had created).
Now IT and I tried a few tricks to combine or join the data I had entered (this could be history of up to 2000 assets), but have not figured out just how.
I have backed up my data on a thumb drive to prevent loss in case of my hard drive accidentally failing or crashing due to weather, now I need advice on how to put that data into the main Access data base--w/o having to do it all manually again.
I have a situation. In completing our Annual Report for our Accrediting body for my school, it requires us to complete a 'Completion and Placement Chart'. This chart was provided to us in Excel. The problem was that we needed a system to automaticaly update this chart as the year progressed so we can visually see our completion and placement rates. With this, I created an Excel sheet with the Completion and Placement Charts and made it so it can be updated as I entered a students information in.
Although it does calculate the data properly, the process is extremely ugly. It is terrible in appearance, function, and by all means, far from user friendly (I understand it, but it takes some explaining to do for someone else). This Workbook that I created houses almost all the information I need with the exception of it being entirely unsafe and far from being secure and data entry is a nightmare.
So, this brought me to Access. I created a database for this purpose (first time using access as well). It works like a dream in storing all the data that I need and keeping it safe. I needed to have a 'Placement Tracking Document' for each student outlining the students information for the accrediting body. This was done wonderfuly in Access and could not be accomplished through Excel (that I know of). However, I NEED to have Access tally all the information in the 'Completion and Placement Charts' like the Excel Workbook does. I need to keep the exact format of the 'Completion and Placement Chart' because it is a standard form in Excel and I can not steer away from it.
How can I get my DB in Access to comunicate with the 'Completion and Placement Charts' and have it automatically tally like my workbook???
I have attached some screen shots of my database and the entire Excel Workbook (Its edited because of confidential data) so you can understand a little of the chaos.
I rarely used Excel and never used Access until now. It took me nine months to create what I have because I was learning as I went. The books that I purchased to help me out do not touch this subject nearly enough (Access Cookbook, Access for Dummies and VB & VBA in a nutshell).
My point is, any help or direction in where to get thorough information on how to acomplish this is more then welcome. Any comments on what I have as well is more then welcome (negative or positive).
Thank you thank you thank you thank you in advance!
Oh, and use the buttons in the Excel Workbook to navigate.
The question is whether or not to normalize the fields "Place of Birth/Place of Death" in my 100'000 records famous people data base. So far, 15'000 different birth places are deployed. Only 3'000 of them are used more then once, that is to say from 2 to a maximum of 1000 times (New York City). 12'000 birth places are therefor used only once up to now. So, a table of 15'000 places would need a 3-digit (all characters used) or even a 5-digit (numbers only used) identification. In the latter case the ident would often be longer than the returned value (Wien, Oslo, Rom, it's in german, you know). Furthermore, instead of just entering a birth place like "Novodny Chomarowsky" I had to search the table of places whether or not the entry has been used yet. Please supply strong pro-normalization-arguments.
Good Morning, I am new to Access and I welcome expert guidance on the relationships of my database. The database will be used to track employee training and although I've enjoyed limited success so far I'm hoping that eventually I'll get it right.
The database has to do just a few things so it doesn't have to be very complicated. It basically has to store employee, training event, and completed training event information. The attachment shows the tables and their relationships.
The are basically two types of training that the database needs to store. The first is training that applies to everyone and the other is training that only applies to certain job specialties. These types of training are identified by the ProjectMandatoryCode and the JobCode respectively.
The ProjectMandatoryCode is a default value that is automatically entered everytime a new employee is entered into the database (EmployeeInfo table) and it is also a default value when a training event is entered into the (TrainingEventInfo table) that applies to everyone. The employee JobCode is a selection when a new employee is entered into the EmployeeInfo table.
When the user wants to enter a training event that is job specific, they are able to select a JobCode from a combo box/option group and this code is written to the record. Selecting one button from the option group writes a default value (ProjectMandatoryCode) to the TrainingEventInfo table while selecting the other button enables the combo box so that a specific JobCode can be entered against the event.
Everything works fine except... so far I've been unable to produce a report that lists every training event that pertains to the employee whether it be ProjectMandatoryCode or JobCode related. I can query and produce completed events or a list of what events are required but nothing that is complete. Ideally, I should be able to produce a complete training plan for the employee which shows all necessary events and where the employee has completed the task I should be able to show the DateCompleted.
This failure has led me to doubt my table relationships since all my efforts at various query joins have failed. Is there anyone who could enlighten me so that I could learn from this, complete this task, and apply this new knowledge to future databases? To the person with the answer, thank you in advance for your assistance. Respectfully, Dale Gagnon
If I remove the order by clause, it works. Now, I have been programming for 30 years and for the life of me, I cannot figure this one out. It is my first use of 'order by' on this ISP so I am assuming there is a server thing? Can anyone suggest to me what this might be? Thank you very much.
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name FROM mtgwl.loan_payment lp inner join mtgwl.deal_loan_relation dlr on lp.loan_id=dlr.loan_id and lp.time_out='9999-12-31-23.59.59.0' and lp.as_of_date='2006-04-30' and dlr.time_out='9999-12-31-23.59.59.0' and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr on dlr.deal_id=pdr.loan_id and pdr.time_out='9999-12-31-23.59.59.0' and pdr.type='PRODUCT'
inner join mtgwl.product_control pc on pc.product_id=pdr.deal_id and pc.time_out='9999-12-31-23.59.59.0' and pc.product_name='GSAMP 2003 AHL'
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name FROM mtgwl.loan_payment lp inner join mtgwl.deal_loan_relation dlr on lp.loan_id=dlr.loan_id and lp.time_out='9999-12-31-23.59.59.0' and lp.as_of_date='2006-04-30' and dlr.time_out='9999-12-31-23.59.59.0' and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr on dlr.deal_id=pdr.loan_id and pdr.time_out='9999-12-31-23.59.59.0' and pdr.type='PRODUCT'
inner join mtgwl.product_control pc on pc.product_id=pdr.deal_id and pc.time_out='9999-12-31-23.59.59.0' and pc.product_name='GSAMP 2003 AHL'
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are, Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records. Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
