I have an old table from a database with 2000+ rows deleted and it had no primary key (but it had a number column).
I am splitting another old table which relies on the number column of that old table but I am importing it into a new table with an autonumber primary key which the split table will rely on.
Thing is I need the number and the autonumber to match up accross all records
1 has to be 1
2 has to be 2 and so on
is there a way to "fill" in rows with the missing numbers? i.e. if I have 1,2,3 and 2 is missing the method adds a row "2" to the table.
Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.
I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.
I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.
The info for thre query is in 3 tables: tblStaff (name etc) tblShifts (start and end times for days that this employee works) tblDays (a list of days names so I can use numbers elsewhere)
My query looks like this:
Code: SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));
This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.
However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.
I've tried different join types but they all come back with "ambiguous joins" error when I try to run.
I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.
My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.
My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
I have system that developed by using Ms Access which has been used for 2 years. But last week, data in one of my table missing in the half way when user do the data entry. There is header table and the details table in the form. All record of the header table were gone but the details record was not problem.
Why all data in table were missing? Please give some guidance.
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I have two tables table1 his a master list of companies that I have already delt with table2 is a list of companies that I have delt with as well as new companies and I allow for multiple instances of company data to be in table2
table2 my have several records refering to "ABCD Inc" but table1 will only have it once
If new companies show up in table2 how can I get a mass copy of all the companies that are in table2 but not in table1 into table1?
I was wondering if there was a special character you entered into the column area if you have missing data.
For example,
I am trying to run some statistical analyses using a pivot chart. I want to make sure that every box in a specific column is filled. For some of rows I am missing numbers for some of there columns. If there is missing data do I put a wild card?
EXAMPLE:
First Last Middle Average
John Smith Adam 100
Smith John Peter (Blank) <--- no data
(Now if I were to find the average of both it wont run)
I have all my data sitting on a server in SQL. Within Access, I have linked tables pointing at SQL tables. I display the data on an Access form. Somewhere in their it is dropped a few records. This system is used for in and out processing and occasionally misses a few individuals. Could this have anything to do with the method in opening the table, ie dbopenTable vs dbOpenSnapshot.
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
I have a database which has one main form linked to a table which has 325 records in it. The problem is when you open the form it says there are only 324 records :confused: I have gone through the table comparing the records with the form records, I found what I thought was the 'missing' record. But when I did a find on the form I managed to retrieve the record. Can anyone help as I'm a bit baffled.
I am working with a normalized database that has MANY tables. Most of these consist of lists of options to select from for the primary table. The primary table is linked to the secondary tables (and those to tertiary tables) by Primary Key ID fields, and the other tables contain additional information.
For Example, the Name list table is related to the Actions table by the SSN field, and the Actions table is related to the PayStatus table by an ID number collected by a lookup field.
When I pull all three tables into a query to display all the information related to a particular individual an his action, there are more records if I remove the PayStatus table from the query. It seems to only pull reports for which a PayStatus has been selected.
How can I get the query to display ALL the records, whether or not the individual has a pay status? Whether or not the individual has one is irrelevant, but I want his name to be displayed, whether or not he has one.
I am having an issue with DLookup, which, for some reason which I do not understand appears to missing the first record in the table.
What I am trying to do is to extract the e-mail address of all users who have 'Admin' status to the database. In the table ('LogIn') there are 3 users who have 'Admin' status and this is confirmed by the DCount function in the following code. There respective ID values in the table are 1, 49 and 51.
Yet when I run the Loop to get all e-mail addresses it misses ID 1 and just returns records for ID 49 and 51.
I have tried changing other users status to 'Admin' and it finds them but always misses the first record.
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?
Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?
Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!
SELECT * from mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE FROM mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield **Incorrect syntax near the keyword 'INNER'.**
DELETE FROM mastertable JOIN secondarytable ON (mastertable.Identicalfield = secondarytable.Identicalfield) **Incorrect syntax near the keyword 'JOIN'.**
DELETE FROM mastertable WHERE (mastertable.Identicalfield = secondarytable.Identicalfield) **The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE FROM mastertable WHERE IN (SELECT * FROM secondarytable) **Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?! Thank you.
I'd like to learn how to do them quickly and effectively in Access. One of those things seems like it would be an incredibly simple, intuitive operation, but it's not, at least not to me.Say I have some data that really should only be on one table, but it's currently on two tables in Access: "List Main" and "List September Adds." They both have the following fields: "Full Name" "Email" "Address" "City" "State" "ZIP" "Phone."
The first table is my main list of contacts. The second contains new info, consisting of 1. a few new contacts, and 2. updated info for a few of the contacts already in table "List Main."
I would simply like to put all the new contacts from "List September Adds" into my "List Main" table, and I'd also like to fill in a few missing e-mails in "List Main" with newly gathered e-mails for those contacts, info that is in my table "List September Adds."I'm sure there is built-in functionality to do something as basic as essentially turning two pages of the same spreadsheet into one. In fact, I know I could import data from an Excel file and have it "append" to a preexisting table if the fields are all the same.
However, I'd like to know how to do it when the tables are already in Access (without having to learn any SQL, mostly because I'm fairly certain I don't need to know SQL to do something like this). I've heard of Append and Update Queries and given their names, it sounds like they'd be useful, maybe with use of "Totals" and "Group By" to get rid of duplicates; however, I can't seem to get any of this to work right.
Mostly, when I think I am doing an Append correctly, it doesn't add new data at the bottom of a table; it just wipes out all the data that was there, and replaces it with data from the source. For instance, when I've been running an Append Query to get my "List September Adds" rows into my "Main List" table, what I end up with is only the "List September Adds" rows, and all the "Main List" rows gone.
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this ID Username tblDailyLog TimeIn MorningBreakOut MorningBreakOut LunchOut LunchIn AfternoonOut AfternoonIn TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button 3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once) 3. The table is updated with the new time (using some dynamic SQL) 4. The table is repeatadly checked using a DO loop to make sure the the correct time went in. 5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens). 6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other) 7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs. I've built plenty of Databases in my time and have never come across this. __________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue) Dim TempSQL As String TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _ "#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _ "#" & Now & "#," & _ "'" & FieldUpdating & "'," & _ "'" & NewFieldValue & "'," & _ "'" & GetNTUser & "'," & _ "'" & TimesheetNumber & "'," & _ "'" & fOSMachineName & "')" ' MsgBox TempSQL DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False 'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table DoCmd.RunSQL TempSQL, False End Sub
Public Function CleanData(ByVal DataToClean As String) Dim TempData As String Dim i As Integer TempData = "" For i = 1 To Len(DataToClean) Select Case Mid(DataToClean, i, 1) Case "'" TempData = TempData & "`" Case """" TempData = TempData & "`" Case Else TempData = TempData & Mid(DataToClean, i, 1) End Select Next i CleanData = TempData End Function
I have created query and all my values come into the table from the query fine.
When i then go and generate the report all of the boxes are there for the data but there is no data in them. For owner and Job Id all info is there but job name, Department and a few others there is no data in the boxes.
I am accessing a SQL database using Access but its not letting me view the table in design so I can set a primary or letting me change the table names.
I am able to do this from my home PC but not at work. Is there something in options or ODBC settings on the work the PC that I need to change.
