86,87,89,113; Strange Jump In Table Rows...
Sep 19, 2006
Hokay, so I have a very simple little database which has been written to make my job a little easier by logging the details of certain calls.
Everything on it seems to work fine, but there is one piece of strange behaviour that I just can't fathom...
I currently have 89 records stored (these were ported over from the old Excel table I used to use to log the calls). They imported just fine, and I can manipulate them as required. However, if I go to add a new record the next rwo to be added is 113 for some inexplicable reason. I can still add the record fine, and add subsequent ones as records 114,115,116, etc., but what I don't understand is why Access thinks the record number that comes after 89 is 113!
The table looks - to all intents and purposes - fine; just with its last rows going from 89 to 113 without listing 90-112 in between.
So...um...where are my missing records, and why does Access seem to think that 113 logically follows 89???
View Replies
ADVERTISEMENT
Sep 9, 2005
I have a table called 'students' with the appropriate fields. There is another table named parents_prof which contains records of common professions. The tables are related through the 'ID_parents_prof' of the 'parents_prof' table to two fields on the table 'students', thus 'students.father_prof' and 'students.mother_prof'.
1st question) Is that possible? Is it possible to create a relation between the key field of one table to two different fields of another table? Is it a good practice?
I did this because I want to build a query which checks for both fields together, meaning I want Access to return a record if the appropriate profession is in either field of mother's or father's profession.
SELECT students.surname, students.name, students.father_name, students.father_prof, students.mother_name, students.mother_prof
FROM parents_prof INNER JOIN students ON (parents_prof.ID_parents_prof = students.mother_prof) OR (parents_prof.ID_parents_prof = students.father_prof)
GROUP BY students.surname, students.name, students.father_prof, students.mother_prof, parents_prof.parents_prof
HAVING (((parents_prof.parents_prof) Like "*" & [enter profession] & "*"));
Access cannot represent the above graphical, so I had to enter the
OR (parents_prof.ID_parents_prof = students.father_prof)
in the SQL formula.
2nd question) Is the above proper query, according to the rules?
View 11 Replies
View Related
Nov 14, 2005
Hello,
I have a strange problem which have to be solved in Access in all way, if it is possible. :-) I have to create a table similar to an Excell sheet. Row headings are name of persons, column headings are workplaces. The aim of this table is to register worked hours at each workplaces per persons. At one person in one shift more workplaces are possible. The number of persons and workplaces is changeable. Does anyone know how could I solve that to store in different records each workhours? For example if a person works 4 hours at 1. workplace and then 4 hours at 2. workplace, this would mean only one row on the form, but two records in the table.
I would be very grateful if somebody would have a good idea! Many thanks in advance!
VoiD
View 1 Replies
View Related
Mar 2, 2007
Hello,
for some unknown reason while attempting to add data to a field in a form I got some strange character which I have tried to remove from the table itself but getting an error message:
The seach key was not found in any record.
Any idea of what could have caused this and how to fix the problem? Thank you.
View 1 Replies
View Related
Dec 28, 2006
I have a main pool of records (about 20,0000). Next to each record is a button that opens up a form. The popup form allows different fields on that record to be edited. I need to be able to have a requery code in the popup form that will requery the main form's records and automatically jump the the last record that was modified (or if that record was deleted jump to the next record). I have no idea where to start!
Thanks,
Paul
View 2 Replies
View Related
Feb 2, 2006
I've been using a combo box to jump to a specific record in a form. But, even with the comb box set at 15 lines long, with many hundreds of records in the form the vertical slider on the combo box is a little small and can be jumpy (moving 1/32" jumps 10 names, etc.). The combo box is formed from:
SELECT [cust num], [last name], [first name]
FROM [this table]
ORDER BY [last name], [first name];
I'd like to put in some boxes that will jump the combo box to a certain set of names. For instance, put in a box with a label on it that says "M". Pressing this button would jump the combo box to the first [last name] that starts with the letter M. For instance, if I have records for:
Lupold
Macaroni
Martin
Then it would jump to Macaroni. But, if Macaroni is deleted, then Martin would be the first name that begins with M and pressing the "M" box should jump the combo box to Martin.
View 2 Replies
View Related
Apr 17, 2015
How to create a field to that would be available only if a certain answer appears.
In my form I have a field named Issues created with a combo box within the table for this field is "Broken" When Broken answer is selected I want to jump to another combo box "Reason" so the user can select how it is broken.
View 2 Replies
View Related
Jan 14, 2015
I am working on a project in which we have to print word documents of each policy so if the policystatus is "Live" then we have to print a document with header(barcode) on it. If the policystatus is "Cancelled" then print off a different sheet. The following code is written that works fine but the only thing is the print order is not right. documents jump up the queue. The documents spooled last get printed off first sometimes. Any other alternator to write the following code so that sheets prints off in order:
Code:
Public LivePol As Variant
Public CancelPol As Variant
Sub GetDataFromDataBase()
LivePol = 0
CancelPol = 0
Dim myDataBase As Database
[code]....
If we can write the code in a way so that printing sheets come up in the order.
View 1 Replies
View Related
May 20, 2015
I am creating a inventory database for work. I am trying to get the cursor to jump to the next text box for data entry using a barcode scanner. I have a limit on how many characters there can be in this field but yet even after it is all filled the cursor stays in that same field. I am fairly new to access, especially in code building. Do I use "After Update" under "Event" in the "Property Sheet"?
Capture.PNG
Capture1.PNG
Capture2.PNG
Capture3.PNG
View 4 Replies
View Related
Nov 10, 2014
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...
What is the best approach?
View 3 Replies
View Related
Mar 10, 2015
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.
View 8 Replies
View Related
Jan 13, 2006
A SELECT works:
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.
View 2 Replies
View Related
Sep 19, 2013
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.
View 7 Replies
View Related
Dec 15, 2012
Assuming the following table:
Album ID AlbumTitle Song Rating
New
When I enter data, Album ID increases every time I jump to the next field. For example, when I enter: AlbumTitle, Song, Rating, Album ID is already at 4 and this is just the first record.
View 6 Replies
View Related
Jun 1, 2007
The following is for a successful pet grooming business.
I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed
View 10 Replies
View Related
Jun 14, 2007
I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?
View 4 Replies
View Related
Nov 7, 2006
Hi.
How I can delete all rows from my table whit on query? Also I want that when I delete rows from my table index and other settings will stay in table.
View 1 Replies
View Related
Dec 17, 2004
Is there any way to get the last 4 rows which have been entered into the table?
Thank You.
View 2 Replies
View Related
Nov 14, 2005
Hi,
I have a table with four columns - A, B, C, D
I want to compare the values in columns A, B and C in row 1, with the values in row 2 and if they are the same, I want to copy the value that is in column D in row 1 into column D in row 2. I then want to move to the next row in the table and compare the values in row 2 with the values in row 3 in the same way. This will continue for all rows in the table.
Any help would be greatly appreciated as my VB is not up to much.
Thanks in advance
View 2 Replies
View Related
Apr 5, 2012
I am working on a project that requires to calculate interest on the amount. There are 2 tables, 1 with Interest rate for a product for different and periods and another is product table with amounts in different period.
Table 1 (Rates table)
Product From To Rate
1111 1/1/2012 1/20/2012 .75
1111 1/20/2012 1/28/2012 .50
1111 1/28/2012 4/6/2012 .40
Table 2 (Product table)
Product From To Amount
1111 1/1/2012 1/17/2012 10000
1111 1/17/2012 1/24/2012 15000
1111 1/24/2012 2/25/2012 20000
What i need to do is to calculate interset based on the above 2 table and insert into another table (Interest). However, the problem is with identifying correct rates for the periods in Table 2.
Eg.
for period 1/1 to 1/17: applicable rate would be .75
for period 1/17 to 1/24: From 1/17 to 1/20 the rate would be .75 and from 1/20 to 1/24 it would be .50
this is what i want to achieve, basically to split the period between 1/17 to 1/24 into 2 so that appropriate rate can be applied.
View 1 Replies
View Related
May 2, 2012
My work has a sql server db, and all the tables in our Access db is linked to them for reporting reasons for the secretaries. Why all the rows for one table are not showing up in Access?
I go into SQL Server Management Studio and I can see all the rows, then when I try to go into Access 2010, I cannot see all the rows. I have already updated the table in Access through the Linked Table Manager. However, nothing changes.
View 3 Replies
View Related
Mar 13, 2008
Anyone know why the following would happen:
I have a form that is linked to a single table. For some reason some controls populate one line in the form and others populate another line.
Each time I complete the form it creats two records with some data in one row and other data in another row.
View 1 Replies
View Related
Oct 19, 2006
Hi
I have to make a query which deletes duplicate rows from my table. How I should start to make it? Can I do it whit one query or does this operation needs more queries? I know that DISTINCT works but how I can set it to DELETE operation?
I have try to use command DELETE DUPLICATES FROM Table; but this command deletes all rows from table why?
View 1 Replies
View Related
Oct 4, 2004
I need to do a count of the total number of rows in a table or query. Say I have a table with 7 records, I want to be able to get total number of rows instead of the sum of the row and save it to some other table.
View 5 Replies
View Related
Jun 5, 2007
Good morning to you all.
I have a problem that I thought would be quite simple......
But 3 days later I am still trying.
I have a form which populates a table by one set of users (Simple enough) they can only add records.
I also have another set of users who want to access that table through a similar form but with a couple of added fields.
They will not be able to change anything only add their comments to what they see in the new fields supplied. Once this has been done it cannot be edited ever.
So I made a "Make Table Query" and added 2 new fields to that, so when I clicked on the query it came me the last update from the previous table. Problem was everything that was added in the extra fields were deleted!!!!
I would like to update the 2nd table with items that have been newly added to the first table, leaving all the items on the second table the way they were!!!!!!!
Anyone got any ideas please
View 2 Replies
View Related
Nov 7, 2012
I have an existing database being used for for order processing (normalized, working). The order table houses the general order info and a separate detail table holds 1 or more orderdetail records for each order in the order table, related by orderID.
A user has asked that I find a way to import her data into the system from an excel spreadsheet. She has individual columns for each type of order detail record, so for each "order" row, there may be 1 or more columns of "orderdetail"s that I will need to parse into the correct tables.
I imported the raw excel into a table, but I need to append that data into the order and orderdetail table rows (i.e. I have to create the order and orderdetail records that match every other record in the system).
It looks like I need to somehow perform a looped INSERT INTO [ORDER] (field1, field2....) VALUE (val1, val2) but there are those related detail records to contend with (the orderdetail table entries) which may be more than one insert....
Test set of data is attached.
View 5 Replies
View Related
