We are creating a database to log data on a project. There will be thousands of files. Can we input data, using the same table, at separate locations and then merge the data into a master table? We will need to do that many times.
View RepliesI am trying to add the current date and time into separate fields after an ID is entered.
Code:
Option Compare Database
Private Sub ID_AfterUpdate()
Me.Date_Received = Date()
Me.Time_Received = Format(Now(), "hh:mm AMPM")
End Sub
We're a small shop building a modest database to keep track of our product listings on eBay. Two people on non networked computers are doing the data entry into cloned copies of our database. This includes attached photos for each record. At the end of each week, we want to combine the records created separately into the master database on my system.
It seems like it would be simple enough to just cut and the paste the records from the two databases into the identical master. The problem I'm anticipating concerns the attached photos.
Some strategies are to transfer the records from the data entry computers to the master database while keeping the attached photos intact.
Is there an effective, efficient way to set this up or would it be best to use the OLE format for the pictures instead of attaching them (which I prefer)?
Currently am trying to get together two separate Access Databases into one, which is the way it was always supposed to be.
Basically, this person accidentally created a duplicate of the database in question, and has been updating both independently at different times, meaning that whilst a lot of the database information is duplicate, there are some instances where each separate database copy has unique information on both existing contacts, and new unique contacts.
The database itself is about schools, and has tables concerning information such as calls made, contact details, school details, etc etc. Obviously all of this needs putting back together into one set of tables in one file.
I've been messing around with append and import functions, but am having issues with duplicate ID fields with append queries, and a general lack of direction on the best way to go about fixing this problem, if its even possible.
Here's an example of what i've been trying to do:
INSERT INTO Contacts ( ContactID, ContactTitle, Name, Surname, JobTitle, TelephoneSTD, Telephone, FaxSTD, Fax, Locale, SubjectKeyword, SchoolID, Reply received] )
SELECT [1Contacts].*, [1Contacts].ContactID, [1Contacts].ContactTitle, [1Contacts].Name, [1Contacts].Surname, [1Contacts].JobTitle, [1Contacts].TelephoneSTD, [1Contacts].Telephone, [1Contacts].FaxSTD, [1Contacts].Fax, [1Contacts].Locale, [1Contacts].SubjectKeyword, [1Contacts].SchoolID, [1Contacts].[Reply received]
FROM 1Contacts;
Hi, I have a db (MS Access 2000) that currently has 37 tables in it, each table holds about 30 rows and 72 columns of data. I need to basically create the entire database again but change all the column names slightly so that they still hold the same information but with different column names, then I need to merge each newly created table into the existing corresponding table.
As there are about 86400 entries I would rather change 2000 odd column names than re-enter all the data again. I would be extremely appreciative if someone could suggest the easiest and quickest way to do this. Many thanks in advance.
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day)
- contains 'name' 'date' and '# of hours' worked
- an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month)
- contains the number of contracts worked that is derived from seperate system
- this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January)
- the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours)
- employees dont have access to this system to enter their own # of contracts keyed on a daily basis.
- for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.
I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.
Thank you very much if anyone could point me in the right direction.
I have an database that uses a couple of different date ranges, so I created a table that shows the different date ranges that may be required (xReport Dates) so I didn't have to keep manually editing queries or entering dates every time.
I have one query that appends data from one table into another based on a date range that you need to manually enter when prompted; I can't seem to get it to refer to my xReport Dates table for the range.
Its currently set up as below:
INSERT INTO 001_M_Gross_Telesales ( UpdateDate, OMSNumber, MediaRoute, ExecName, SaleType, Name,
[Reporting Campaign], [Reporting Team], [Sales Leader], [Reprting Name], [Media Route2] )
SELECT Max(L_ExecTracker.UpdateDate) AS MaxOfUpdateDate, L_ExecTracker.OMSNumber,
L_ExecTracker.Campaign, L_ExecTracker.ExecName, L_ExecTracker.SaleType, Z_Ref_Agent_Table1.Field23,
[Code] .....
Have 2 tables:
...TableA: 1 indexed field "Name" and many data fields in ~ 1000 records
...TableB: 1 indexed field "Name" and 2 data fields (DataB1 and DataB2) in about
~ 50 records
Tables currently have NO relationship set.
Want to add TableB data fields to TableA if Name are identical.
DataB1 and DataB2 fields are Not currently in TableA but could be added very easily.
Update (or whatever) will be done weekly where all previous data for DataB1 and DataB2 will be cleared from TableA before job is run.
Helo…please really need your help.
I designed a small desktop database to automatically import some Log files. A sample of a transmit log file (emails sent from our rural email stations) looks like this when imported in access.
Date Time Direction SenderMessageID
03062005 133501To InternetemailX@ab.comBlablabla
03062005 125001To InternetemailQ@ab.comBlablabla
03062005 125001To InternetemailZ@ab.comBlablabla
03062005 125001To InternetemailA@ab.comBlablabla
I would like to be able to query all emails sent between one date and another. However, the date here is recorded has a string or text. I would like to automatically add a field with the date as Date/Time datatype in the Table. I think I should be able to do this with a few SQL statement like ALTER TABLE myTable ADD COLUMN NewDate AS datetime…and then another SQL statement to separate data and put it into this new field.
Then I could query for BETWEEN Date1 and Date 2 easily.
I’m pretty close but been trying for a while now and always error messages as results. I think I really NEED help this time.
Thank you,
Ghislain Bob Hachey
I have a form with several tabs on it, where the user enters various info.. I want to have the user hit the save button and have all info saved to a single table. Is there an easy way to do this. Please excuse my ignorance but I am fairly new to Access. Thanks in advance!
View 9 Replies View RelatedI have two table
1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID
i want to entered data in Saving with each and every EMPID with Master
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
Any way to transfer the data in an unbound textbox into a separate table.
User enters an amount into a textbox2, and then it does some calculations that involves textbox1 and textbox2. Finally, textbox3 has the final answer. Can textbox3 overwrite textbox1's data on the table and on the form?
Also, is there an "auto date" feature in tables? If new data is put into tables, Today's date appears on the date column?
I am developing a form where students have to enter their ID number but I want my form to check through a table of students and their IDs and flag up if the ID number does not exist in the table.
View 10 Replies View RelatedHow I would go about appending data to a table that has been entered by a user on a form.
My initial idea to tackle this was to create a number of text boxes as a method of user input which would all then transfer to a table but this doesn't have much longevity to it(if the database requires additional columns to be added, etc.)
The setup at the moment is two tables, a main table, and a temporary table(which is where I intend to first store the user input, this is so that the user can view what they have entered and make any necessary changes(undo))
I'm wondering if there are any easier ways to go about this such as, a msgbox appears and asks the user to enter each individual column data for a row. This doesn't sound like the most efficient way but efficiency isn't a priority right now and is something I'll look at later.
I'd like it so that a user can input data into a number of text boxes, a button has an onclick event that will append all entered values to the temporary table, this is then relayed(I'm assuming through requery?) back to the user as a way of checking before really adding it to the main database, and then if they're happy there is another button which is then enabled so they can add it to the main database.
In my office we have used Access 2002 for many years. We enter data directly into a table. Many values are repeated row after row, with only slight changes in a few fields. I am looking for a way to automatically have the previous value in a field entered into the current row, and allow this value to be modified if it has changed, but then become the new "default" value for following rows. I have searched the web and found solutions when using forms, but nothing for entering data directly into a table. I have also seen the suggestion for Ctrl + ', this works but I would rather it be done automatically.
View 3 Replies View RelatedI have a notes field in the customer table that is a memo field. An example of one customer's notes field data :
<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>
[Code] ....
I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
I have a database (split into front and backend). Users populate the table using a form on the front end. Recently, it has been pointed out that some of the data entered into one specific textbox is being changed on the table. The data entered is always has a minimum of a letter and number value i.e. "A1", "A2" etc. Upon examining the table, this has changed into numeric values i.e. "1291", "1061", "852"... etc. Looks like it is translating them into both 3 and 4 digit numerical values by the looks of things. Where more complex data is entered such as "2(A1, A2)" these seem unaffected.
The field is set to text, I have set no validation rule, format or input mask. Just can't see why access is changing these values. It seems to be translating them, but I can't see a pattern.
I have an excell spreadsheet with member names
the member names are duplicated up to 6 times each depending on how many months from october - march they have been members.
The excellspreadsheet looks like this
Member name Member number October care level November care....
triscuit83 1234 1
Triscuit83 1234 1
Is there a way in access I could make triscuit 83 show up once and have all the care levels stay on the same line.
Please let me know if there are any clarifications needed.
I need help hardcore
****edit****** the example doesnt show up formated right basically 1 will be in the october column the first time and a 1 will be in the november column the second line.
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
View 14 Replies View RelatedI have two seperate files with the same type of data. Each file is sorted into multiple batches. The first file is a current data base still being used that was created in an older version of access. The new database has some records, but is in a newer version of access. I need to know how to copy the records from the currently used but older version of access into the new database. I need for the records to be active to run reports. Sorry if its a basic question.
New job.
So i'm not sure where this post belongs as it may encompass several items.
I have an excel sheet where every row has an email address and a product purchased. I've imported it all into access but would like to run a query to find out how many people that bought x purchased y. Or tell me how many people that x did not buy a,b, or c.
Part of the problem is that each record is one product. My data looks like this:
email: Product:
e1 p2
e1 p7
e1 p33
e2 p2
e2 p9
e3 p1
e4 p1
e4 p3
I was able to group some of the data by email address in access but can't figure out a good way to query it. I'm thinking I probably need to merge the data where each unique email address is a record and the products have all the products purchased instead of just one but I really have no clue how to do that.
Is this easily do-able? how would I go about this?
Thanks alot!
Hello
I'm currently doing a query, when a report is received, we enter a date, i would like a query which would show records where the received date has not been entered. i have tried putting in isnull() but does not work.
Can anyone help?
Thanks
I am having trouble parsing the month from a date like 05/12/2007 and I want to return the leading zero with the month. Example of 05/12/2007 and I want to display 05 only.
Please send any suggestions.
Thank You,
MJ
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony