General :: Changing Existing Database From Overall To Monthly Records
Nov 8, 2013
I'm altering a database to have certain fields be recorded monthly.For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".
I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).
I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.
the company doesn't track when the transactions occurred; I'm unable to group them along those lines.I'm new to Access & don't want to make a mistake.
View Replies
ADVERTISEMENT
Mar 30, 2007
I have an existing contact information database which consists of a number of tables, the main table has a primary key which is currently set to NUMBER.
There is a relationship between it and 2 other tables via that key (ContactID).
I would now like to change the primary key (ContactID) to AUTONUMBER, so that any new records added, have a number assigned automatically. I also need any newly created primary key number to be used in the child tables created as a result.
Is this possible ? (without renumbering my existing tables/records)
many thanks
View 6 Replies
View Related
Jun 15, 2014
I'm developing a database that tracks monthly metrics of employees. These employee are at several different locations.
Here's what I would like:
A table of the employees and their location.
A table of the metrics.
A form that you can select an employee and it would autofill the location and then you could fill out the metrics.
the ability to change an employee's location without it effecting past records.
A report, by month and location of the employee's metrics. (pretty sure I can do this on my own, just can't get to this point.)
I've tried to use a auto lookup query but that then changes the employees location on past records.
View 4 Replies
View Related
Mar 20, 2013
I am creating payment database for a community gym, the data will be created on a data sheet.
There are 2 payment types monthly and annual (these are on a dropdown box with their own id number).
I have a joining date and a paid date, but reflected against the payment type ie 31 days or 365 days.
I have been trying to do something along the lines of:
if [monthly] (is selected) (so this would call up a 31days) date() is > [joining date] then give message to renew and same kinda thing for annual.
View 1 Replies
View Related
Oct 25, 2014
I have to design 2 databases base on some data that was given by my instructor but I feel like I'm missing something on both specially the 2nd database. I have attached my work so far as well as the instructions and the existing data..
View 2 Replies
View Related
Feb 28, 2014
How can I overwrite the existing Access database with another Access database without the need for the end user to get involved in the process.
I want the code to copy a new mdb file over the database I am using (!) and then automatically start the new mdb file...
View 9 Replies
View Related
Jan 21, 2014
I have table1 in my (Access 2010) database that has exising records. I have another table2 that after I run a query, it first deletes the data in table 2, then imports new records into that table. I run the import into table 2 on a semi regular basis but have yet to copy those records into table 1.
With that said, using either a query or VB, how do I copy all the (new) records from table 2 into table 1 without altering any records that already exist in table1?
BTW, Table 1 has a main form with a subform within it
View 5 Replies
View Related
Apr 18, 2013
We are currently using Access 2007 for all our database needs but so many different people have had access to changes things that now they are so convoluted that my boss is wanting me to build a brand new database... Is there a way to take all the tables in one database and migrate them over to the new database that I am building?
View 3 Replies
View Related
Dec 29, 2014
In the past I have only done data input into Access databases that have already been created. They have been simple text fields only. I was hired on contract to finish data input for a database that serves to catalogue material resources. The database itself was supposed to have been completed by a former contractor but I'm discovering bits and pieces that weren't finished. I don't have any background in IT or coding.
So, this database eventually has to be shared in a "locked" format so that other entities can view our resources (like a catalogue) without changing them. My next step is to add images to each of the items being catalogued (there are about 100 images). The former contractor began a table labeled "Pictures" in which there are 2 columns: PictureID and PicturePath, the latter being a path starting with C:. The issue is, his picture paths include the hard drive name so that it looks like this:C:HardDrivePicturesPictureID.jpg
I need the database and its images to be share-able on other computers. And I need to use relative paths to connect the images to the database so I'm not tremendously increasing the size of the database.does the location/path of the database need to be changed so it can be shared, and if so how do I do that?
Second question: I have all the images I will be using in a folder on my desktop; how do I save them in a "fixed subdirectory" of the database so that they get moved with it whenever it is sent out to another user?
If I change the database name as I'm working on it (I've been saving a new database each day, so that if I screw up something I can go back to the most recent working one) will that mess up my picture paths?
View 5 Replies
View Related
Feb 7, 2015
Im building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
View 3 Replies
View Related
Jan 12, 2015
I would like to get a third party to edit a database and record those edits by changing the colour of the font. I know this can be done in layout mode, but it changes the font in all records, I only wish to change the font colour in one record, how I can do this, without changing all the records in the database.
View 3 Replies
View Related
Dec 14, 2014
I am new to access.
On occasion my access database all of a sudden begins to operate very slowly, I notice the queries take a much longer time to run.
Last week it suddenly began to operate very slowly after idling in excess of 30 mins
Today when I change the screen to design view and then back to form view once again it begins to operate very slowly. (yesterday was working fine on my desktop at home, I have not made any changes to the database with exception to saving and using the file this morning on the shared drive at work) If I shut down and re open it operates well but once again changing to design view causes the same issue. Sometimes compact and repair rectifies sometimes not.
View 1 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Apr 4, 2013
i want to create a database for a monthly expenditure. i want every month to have its own table or form.
View 1 Replies
View Related
Jun 22, 2015
I'm working on an access database and I need to connect that database to the reminder function of Outlook.
At first, I set the reminder precisely six months before the due date, everything was okay and the reminder worked. But after some revision required by my supervisor, I need to set the reminder based on month (not exactly on the due date), so the tasks which have due dates in the same month will be wrapped into a single reminder. The reminder also should be appear in every two weeks. Here's the code I've been worked on :
Code:
Private Sub Expired_AfterUpdate()
Dim outLookApp As Outlook.Application
Dim outLookTask As Outlook.TaskItem
Set outLookApp = CreateObject("outlook.application")
Set outLookTask = outLookApp.CreateItem(olTaskItem)
[Code]...
I put this code on the AfterUpdate event in the column containing the due date. How should I rearrange the code so it would be able for the reminder to appear based on the month? (just consider that the reminder will be active six months before due date).
View 4 Replies
View Related
Jan 19, 2015
Is it possible to set an access database to take a depreciation value of lets say 10.00, and subtract this from the current value every month?
So far my database has a form where you can input the depreciable life, purchase price, current value and total depreciation. However the date and amount of depreciation has to be added manually into a data table for the text boxs to calculate how much it has depreciated and the current value.
View 4 Replies
View Related
Jul 29, 2014
My database has a crosstab query that counts the number of hits by month. I have two columns that are named "Count of Material"(Frequency) and "Sum of ordered Qty"(Total ordered). I need add a colum to find the Average Monthly Demand by dividing the Total ordered/Frequency/number of months in the query. I have tried everything to make it work but can't.
View 8 Replies
View Related
Apr 13, 2013
I need to make a report that shows a work schedule for 10 employees. I need it to print in a monthyly calendar format with horizontal lines to show start and end date and time. I need it to show that there is or is not any overlap for that machine."
It has been a while since I have created a DB and I get some of this okay. So far I think I need three tables. One is the machines. Two is the product schedule (how long it takes to produce X number of products). The last would be work orders and that pretty much covers the data.
I think most of the data can be maniputated by queries and that is even done on the report. The report as we all know is the reason for a database. If it was just about the data it would be simpler to do a spreadsheet. It could get fancy and make a pivot table. That output can be a calendar of sorts.
View 2 Replies
View Related
Nov 14, 2013
I am currently working on a small database to track my own investment records.
I would like to create the function which enable to count the number of records that has exceeds the average value during that period.
Following is the SQL that I have managed to produce:
SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;
This SQL does work. However, when I try to add the Avg function. The Query stop working.
Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?
View 1 Replies
View Related
Jun 12, 2012
I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.
At this point I can generate a report for any given day.
This could be an example of what I want to achieve
Code:
------------ Date | Date+1 | Date+2 | Date+3
New Sheets 2 1 0 2
New Towels 1 3 0 1
Full Clean 0 1 2 0
"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)
I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.
View 4 Replies
View Related
Nov 26, 2005
I have a mainform with 4 fields which are marked with 4 primary keys: soldtoId, shiptoID, PlantId and formulaID. PlantID is a long integer number field and the other 3 are text fields. there is no default values.
I have a subform1 that is linked by PlantID and FormulaID. These 2 field names are in the linked child fields.
I had a handful of records established already. Today, i added a command in the after update section of the PlantID field in mainform by adding Me.Refresh.
So when i change the plantID in the mainform, the subform gathers different plantID and formulaID data automatically. works nicely.
Here is the rub. I next tried adding NEW records to the mainform. that's the one with 4 primary fields.
i enter soldtoID and shiptoID fine. they have no refresh need. however, after the enter a PlantID, the me.refresh kicks in before i can get the the formulaID field. i get a debug error. something about a NULL. which i suspect is the formulaID that i didn't get a chance to enter to. FYI, later, i want to put a refresh on formulaID too.
so how can i make some kind of Refresh work for both New and existing records??
penwood
View 8 Replies
View Related
May 18, 2014
Is there any way to reset a database to have no records (start again) ?
View 1 Replies
View Related
Aug 6, 2013
I'd like to be able to show recruiters the database as an example of my work (to get an interview, etc.). Of course, I can't just show them a copy of the database with all of the private information on it, so I'd like to be able to show them a copy of the database with no records. Is there a way to export/copy it so I can do this in Access 2007?
View 4 Replies
View Related
Mar 27, 2014
I've got a database used daily by 4 users. It's split into a frontend (10MB) with all the forms/queries/reports/vba and a backend that's just tables (170MB), and the users access the database from a network drive. All additions through the forms are logged to a text file, and at the end of the day, a report is run that prints the day's work to a PDF. The database / workflow has been stable for the last few years, with only minor edits to the code, and no programmatic changes in the last 3 months.
Today, after running a compact-and-repair, I realized that the database contained no entries added within the last 2 weeks. I checked my log files, and sure enough I see that all of my records were at some point added to the database. This is supported by the fact that I have PDFs for every day in the last 2 weeks that show exactly what was done (roughly 30 new records/day).
My first guess was that compact and repair had corrupted the database, and knocked out a chunk of records. Fortunately, I've got daily backups, so I started restoring to yesterday's database. At this point I found that the records were missing from there, and from every backup from the last 2 weeks. Now, it's possible that my backup solution (logMeIn backup) is hosed, but the the log files are getting properly restored by the backup, which leads me to believe the backup is working. So, somehow these records were never saved in the database, yet they magically appeared in my end-of-day reports?
I thought maybe I was getting stuck in some state where the database went read-only and the edits were getting stored in memory but never written to disk, but that doesn't make sense as we occasionally restart the database during the day for other reasons, and the end-of-day reports are always complete, which knocks that out. Having restored to a prior version of the DB, I attempted to make changes / add new records and they appear to be sticking, but I find my faith in Access rather shaken, all the more so because I haven't a clue what went wrong before.
View 2 Replies
View Related
Feb 21, 2014
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
View 5 Replies
View Related
Nov 8, 2012
I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.
My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.
When I create a front-end query to query all the records in that table, it says I have 2160 records.
The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.
Using Access 2010
View 5 Replies
View Related
