Queries :: Creating Access Database To Monitor Expenses For New Snack House
Jun 3, 2013
I am trying to create an access db to monitor my expenses for my new Snack house.
I have created a "Main" table which will be holding all the daily expenses entries one the fields is called "Type" in which i formatted as combo box with the following values (vegetables , butchery , bakery).
Another field is called "Item" which should list the items based on the Type selection.
Knowing i have a second table in which i stored all types and their corresponding items , how can i force the filed "item" in table Main to simply display the ones i need based on the "Type" input.
i am busy with creating a access storage database and need to calculate the number of days a vehicle is in storage, i have a [date in] field and a [date out] field. i need to calculate if [date out] is empty to use today otherwise [date out] - [date in]
I have a query that is pulling from a single table of expenses the sum of expenses for each quarter. I am using only three fields: the quarter, expense type and amount paid. I am using - Qtr: DatePart("q",[PostDate]) - to obtain the quarters, grouping by quarter and then by expense type. However, the record returned for the 4th quarter shows this result: "3.0026" It should not have four digits after the decimal.
Results for the other three quarters in the same query return correctly with two digits after the decimal point. The query below was suggested to me in order to make the sum returned have only two digits after the decimal:
AmtPaid: Sum((SELECT FORMAT(PymntAMOUNT, 2) FROM tbl_expenses))
The result of this was a curious information box that said: "At most one record can be returned by this subquery." The query returned nothing. I have checked the formatting in both the table and the query itself and the format is "Standard" for both. So, I can't figure out what is producing this 4 digit problem, and why it is only in one record while all the others have two digits after the decimal.
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
I have built a few Access DBs and am looking to improve my development skills and attemt to migrate my Access DB to SQL Azure. I find access to be a very powerful development environment to build rapid applications . How to use multiple monitors so that I can display different forms on each monitor from a single Access DB.
There are a number of what I call reference tables. These are fairly static and in the main will not change.
There are a further half a dozen tables all linked together.
The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.
What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.
The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.
I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.
Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.
I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.
I heard from a number of people that this is the place to come for Microsoft Access related help.
So here is my question and I hope someone can help me understand access a little bit better.
I am trying to create a database for a job I am doing. THere are 700+ convenience stores that we (4 inspectors) inspect every three months.
We have been told to do an ADA compliance survey when we visit these stores.
The ADA checklist that we have is about 78 questions and my manager wants me to create an ADA database for this information.
So I am in need of help on how to create a database that allows me to input the answers of this ADA checklist for each store inspected.
I then would like to be able to pull all that information into a report that I could say of the 700+ stores, 50% passed all questions, 25% passed only questions 1-??, etc. etc.
I can upload the two files: The ADA checklist and our Store list if anyone would like to help me out!
Hi guys, i need help with a sample database, and wondered if anyone could tell me the entities and how to calculate the prices etc. If anyone could attempt starting the database off for me it would be appreciated too!!
Here is the spec!!!
You are required to produce and document a design that meets the requirements of the McDuffs Burgers scenario:
The corporate office of McDuffs Burgers has asked you to design a database to help track its restaurants and managers. The database is to help the management show the total annual sales of each restaurant and the performance of each manager, as measured by the totals annual sales of all restaurants for that particular manager. Each restaurant is supervised by a single manager, but a manager is also responsible for several restaurants. The company stores typical personnel data (name, salary, and so on.) for each manager as well as basic data for each restaurant such as the telephone and address of each restaurant, its size in square metres, and total annual sales for the last fiscal year. The company would also like objective ways to measure the performance of a manager such as the total revenue for which they are responsible, the average annual revenue per restaurant, the average annual revenue per square foot, etc.
The database should also track the orders that are placed by individual restaurants to the corporate office for various food supplies. Each order is associated with a specific restaurant, and of course, a single restaurant will place multiple orders during the course of a year. The company uses a standard set of product numbers, product descriptions, and associated prices that applies to all restaurants. Each order can specify several products, and a single product may appear in several orders. The database should be capable of computing the total cost for each order.
Deliverables 1. Entity definition for each entity. 2. Entity Relationship Diagram, which must show entities, relationships and membership. 3. Relationship definition for each relationship. 4. Relations (This must include for each relation the primary and foreign keys). 5. Data Dictionary.
Furthermore:
You are required to implement the design (produced in the first part of the assessment - McDuffs Burgers - Database Design), by designing and creating queries, forms, reports and any supporting code. Revisions may be made to the design in the implementation process.
You should note the management of McDuffs Burgers has little experience of database systems and wish to be advised on the information the system can produce.
They require example reports demonstrating the capabilities of the system to: - Aid the day to day operation of the business. - Provide appropriate management information.
You must also implement an appropriate user interface to the database easy to use.
I have MSAccess 2003 running on WindowsXP. I have multiple users sharing a single database. When more than one user opens the same database, a copy of the database is being created?? I don't know if this is a standard trait of Acccess or something else. Any explinations would be helpfull.
I'm not too hot at creating a database in Access, so I was wondering if there was anyone out their prepared to help me construct one.
This database is designed to search for property from various locations and from various price ranges, you can also pick the type of property, the amount of bedrooms and have the result in descending or ascending order.
For location just use: Location 1, Location 2, Location 3, and Any Price Ranges use: 30,000, 50,000, 70,000, 90,000, and 110,000 Type of property please use: houses, flats, and bungalows Bedrooms use: 1, 2, 3, 4 or 5+
Fill in any data you wish, I think this needs to have a query setup?
I am new to Access and somewhat OK with Excel.I am working at a government institution, agriculture sector. We have field staff of ~150 doing different field ranges ~350. Some officers are assign to more than one field BUT no field is assign to more than one officer. (these fields belongs to different Districts, number of fields in each district is not the same)
we have four different programs namely new cultivation program (NPP), productivity improvement of existing lands (PIP), farmer capacity building (HRM) and post harvest handling (PHP). Each program has its activities lets say NPP1, NPP2, PIP1, PIP2,PIP3 etc. for an example NPP1 is land inspection, NPP2 is donating planting materials. these activities are predefined and sequential. (planting materials can not be donate without land inspection)
Officers send their progress to progress monitoring unit monthly which includes progress of each program and each activity progress for that month. My objective is to track, analyse, visualize officers progress.
These are the questions I have,As I am OK with Excel and NOT good with Access do you think I should use access for this due to any special advantage only access can give me.can I visualize data with Access?
i have been trying to create an employee database to record:
1. Name , Address 2. Telephone 3. Date of birth 4. employee ID# 5. Emergency contact -name, address, telephone 6. Photograph 7. Other pertinent information related to employees
So far I have incorporated the contacts database forms into my new database but I am unable to link the forms into one form that will incorporate all the information needed. Is there an existing employee database that I may edit to suit my purposes?
I am not sure where to post this question. I remember finding a forum just for Microsoft Access 2007 questions, but I couldn't find it again... Indifferent
Anyway, I have created a database, and have been testing it with my application. I have some data in this database. I was wondering if there was a way in Microsoft Access 2007 to create an empty copy of the database for release. In other words, I would like to create a copy of the database with all of the tables and columns, but without the data (rows) that I had entered as part of using my testing database...
Does anyone know if I can do this?
If anyone knows where this Access 2007 forum is, or resources that might answer my question, please let me know, as I would greatly appreciate that.
I have attached the excel document in a zip file. Excel documents are not an authorized file type. This is going to be used for volunteers to input the shift and day they would like to work. I want to start by requiring volunteers to contact me and I will add them to the list of possible volunteers and I will give them a unique identification number.
When they contact me I will require all the information that is currently in the excel document (first, last, supervisor, ph number) I will then add them to a list of possible volunteers.
Then I would like a form they can access via sharepoint for volunteering what shift they can work. On this form it will have a box to input their unique id number and then select a shift from a dropbox (that has not been filled and auto populates). This will add them to the schedule.
Then I need a way to pull a report that shows all the volunteers and does not include their ID numbers.
As you can see the supplied document is full for March. I also have a second sheet that is blank for April.
I imagine there will be tables for
Volunteers (no duplicates in this list) Supervisors (1 supervisor can be over multiple Volunteers) Dates/Shifts (there are 2 shifts per day, there are 2 slots per shift)
I am a newbie at Access. I have uploaded an Access 2013 file and an Excel 2013 file to show what I am trying to do.
(I also incorrectly uploaded this question under QUERIES - since marked there as 'Solved")
The Access tables that I have created are meant to mirror the individual fields on the Excel spreadsheet.
On the Excel spreadsheet, I would enter data in the rows as the data becomes available.
The Access file contains the excel fields as tables. Some of the table fields consist of only one field and others have many fields.
I have created Primary Keys in all the tables. But I don't understand the process well at this point in time.
I need to know how to create proper relationships, and then, to create a query or a form to enter new data as it becomes available so that I can keep adding rows of data (query to enter and update data) and subsequently run queries to analyze the data
Need to create the correct relationships and a query that lets me update all the data for new cases, as an individual row (like on an excel spreadsheet) .
I am using an Access 2003 database to create a Word report that contains Graphs. We have since moved to Office 2010, and now the vba code will not select the graph and populate the data, i presume this is because the data is no longer in a datasheet but Excel itself.
I read in the book (Access 2013 inside out), one of the way to distributing access database is creating an application shortcut.
Now i have an Access 2013 file on my computer (with office 2013 and windows 7) other users have office 2007 and windows (XP) on their computers. now i want to give a copy of this file to other users without save as that to 2007.
I would like to know how i can do that with creating an application shortcut , if it is possible because in the book I could not find the way if there is?
I have played with this problem for 3 days and have come close but not quite solved it. My problem, I have several drivers delivering several orders, the orders are named 101, 102 and so on lets say to 150. Due to locations of the drivers, some deliver more orders then others. I want to be able to create a report that looks like
"Driver #1 101 - 106" "Driver #2 107 - 110"
Driver 1 delivered 6 orders. Driver #2 delivered 4 orders and so on.
I have tried the 'count" which gives me the number of orders per driver but having trouble figure out the design of the calculation in the query.
Hi, I'm having problems getting my head around the best way to design a table for an employees expenses. This is my current M_Expenses table:
ExpenseID (PK) PayrateID ExpenseType Amount
PayrateID is the foreign key to a table that holds information about their rates, evertime etc.
My idea was that the user would only choose an expense if the employee were entitled to that expense - so if someone was not entitled to flight expenses the record would not be created. However my boss says that if he were to look at the report produced from this he wouldn't know whether the person putting in the data just didn't have the information at the time or it didn't apply to that employee. So I will need to let the users know that they must create a record for each.
Also the report must list all of the expenses: Per diem expense Accommodation Car hire Flight Other expenses They need to be listed in this order and must appear whether that particular employee is entitled to them or not. I don't see how I can do this as it is because my subreport that lists the expenses is a continuous form and they are not listed in alphabetical order. Plus only expenses that the user has chosen will appear.
The only other way I can see to do it is redesign my table as follows: ExpenseID PerDiem Accommodation CarHire Flights OtherExpenseType OtherExpenseAmount
I have just upgraded from Access 2003 to 2010. Now I'm trying to relearn some of the small things I used to be able to. In 2003, I could just have a table highlighted and select "Insert, Query". It would then open a query design with that table. Is this possible to do this in 2010? Or do you have to open query design then add the table manually?
I just bought a new computer. It came with a GIGANTIC (for me) 19 inch monitor.
Here's the question. What resoution should I set the screen to? I make databases for others. If my forms are too big for their monitors they have to use the scroll bars all the time. If my forms are too small it doesn't look professional.
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
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.
