Tech Problem Aggregator

counting records in access

Q: counting records in access

i have an access file that has two tables. one table lists all the foremen and the other has the inspections. i want to print a report that shows how many inspections each foreman has. some foremen will not have any inspections. the two tables are linked together already.

A: counting records in access

If the inspection table has the field references the foreman, then use the following select statement.
select foreman,count(foreman) from inspection table group by foreman
Regards/Agoo

1 more replies
Answer Match 92.82%

I have a need to count the number of employees that are assigned to an associate director. I have them identified in the Employee Info Table as Associate Director ID = 24 and 9. On a form called frmDirectors Report Date, I have a choice to pick which Director, then the printer function calls the report and subsequent query to limit the data. One calculation on the report need to know how many employees in total are assigned to the selected director in a calc that figures average employee hours. I cannot figure out how to add the count to the existing query or any other way of approaching this issue. HELP. I have included the MDB for reference.
 

A:Counting Records In Access

I have already answered this question on your previous thread.
 

1 more replies
Answer Match 91.56%

Access 2000, Windows XP OS

I need to sort records in groups using the fields:

DATE-OUT , EQUIPMENT , LOCATION , INSTRUCTOR,
1/5/05, LCD-Pro, LAB 101, JONES,

and get a count for the equipment used in the location field and a count of the equipment used by each instructor. In other words what I need to know is

1. How many LCDs were used in LAB101 between 1/1/04 and now.
2. How many LCDa did Instructor JONES use between 1/1/04 and now

The DATE-OUT field has records from 1/1/02 thru 6/9/05, I need to use all the records from 1/1/04 to now. I tried using >12/31/03 in the criteria box, but when I use the group by for the LOCATION and INSTRUCTOR I get separate dates. I just need the date range not each date. They need to be sorted by EQUIPMENT, which only matches any reference to LCD in the field and they need to be grouped by LOCATION, and INSTRUCTOR. When I use group by for the two fields LOCATION and INSTRUCTOR I get nothing. Please help. Thanks.
 

A:Access Grouping and counting records

6 more replies
Answer Match 91.56%

I am trying to use "Count" to count each individual record in a Query. I only have 25 records in the database so far, and each time I run the query record #2 counts as "5". Everything else shows a "1". I have looked at my table setup, and all the records are set up just alike. I can't figure out what's wrong.

Anyone have any ideas?
 

A:Help counting records in Access Query

What kind of query? A Totals Query I hope?
What field are you "counting"?
Are you SURE it's record #2 and not 5 records for that field?
 

3 more replies
Answer Match 75.18%

I have a need to count the number of employees that are assigned to an associate director. I have them identified in the Employee Info Table as Associate Director ID = 24 and 9. On a form called frmDirectors Report Date, I have a choice to pick which Director, then the printer function calls the report and subsequent query to limit the data. One calculation on the report need to know how many employees in total are assigned to the selected director in a calc that figures average employee hours. I cannot figure out how to add the count to the existing query or any other way of approaching this issue. HELP. I have included the MDB for reference.
 

A:Solved: Counting Records

Closing duplicate. Please do not create more than one thread for the same issue.
 

1 more replies
Answer Match 74.34%

I have an issue with counting [COLOR=blue !important][COLOR=blue !important]records[/COLOR][/COLOR]. In one report all records and the one filtered by Director, I need to total up the records in the employee info table and have the count available for a calculation in the reports, Takes the total hours and divides by the number of employees. In the filtered form, Director Report Date I would like it to go and tally the records of all the employees for the director chosen in the form each time the report is run so if they add employees the calculation will always be current. The same thing needs to happen for the ALL employee report... count all records and store. Should this be controlled by the form? I have included a copy of the mdb.

Thanks again for your support.
 

A:Counting Records using form criteria

10 more replies
Answer Match 65.52%

Hello,
how can i add a new record between two existing recorfs in access 2003 ?

thank you in advance
 

A:add a new records between two existing records in Access 2003

Sorry I do not understand your question, in what way will the record be "Between" 2 other records?
 

2 more replies
Answer Match 57.12%

I have what I hope is a simple issue in Access. I have a simple set of results of targeted email campaigns. I have a Access report grouped by Salesperson and within that group, each email recipient is listed with an indication of whether or not that person opened our email. Such as:
Smith, George Opened
Smith, John Opened
Smith, Sarah Not Opened.

I am looking for the proper function/syntax to count the total number of "Opened" messages in each group. I've tried a DCount function, but haven't gotten it to work. I know I can work with a count in a query, but I'm interested to see if I can do this right in the report and learn something new regarding functions.
Thanks!
 

A:Conditional Counting in Access

6 more replies
Answer Match 57.12%

I assume I need to use SQL. I know how to access a specific database and find the record count. Does anyone know how I access the MS Access DB and know how many tables are in that database? I need to export this count so another programming language (autoit) can pick it up.
Thanks
 

A:Counting tables in a MS Access DB

If you can get record count, you can get table count.

dim db as database
set db = currentdb
debug.print db.tabledefs.count
 

1 more replies
Answer Match 56.28%

I have a report with students
I have grouped them by age and now I want to count the number of males and the number of females and have it show on the report
 

A:Access 2000: counting in a report

I removed your extra post and renamed this one. Next time, you can EDIT this one using the Thread Tools dropdown.
 

1 more replies
Answer Match 56.28%

Hi. Lets say that i have a table that contains check boxes. My report lists all the checkboxes, and down in the page footer, I want to have a text box that counts the number of checked boxes. My checkbox name is called checkbox and i've tried many variations of =Sum and =Count and can't seem to get it to work. My textbox always ends up showing =Error when I run the report. Any suggestions?

Thanks.
 

A:Counting Checkboxes in Access Report

Have you tried this variation?

=Sum(Iif([checkbox]=True;1;0))

The problem with this is that you probably want a total just for the page in question. Access doesn't do this without a little more complexity than you might expect, but it does do it. This article from MS shows you how it works:

ACC2000: How to Create Page Totals on a Report
 

3 more replies
Answer Match 56.28%

I have a database for a senior center membership. Members are from a number of area towns. I am trying to write a report showing the demographics by town. ie gender, age etc. I have grouped a count of members by town, and now I want break that down by age groups. It would look something like this:
ANYTOWN 50-60 60-70 70+
members = 20 10 5 5

NEXTOWN
members = 10 3 3 4

ETC.

I found a function on line that will compute ages from DOB and assign them to age groups. I use this function in a query that gives me the demographic info I'm looking for, including a column named "AgeGrps" My problem is "How do I get a count of each age group by town in the format shown above?" Any help will be greatly appreciated
 

A:Counting Age Groups in an Access Report

Ron, it may take 2 queries to do this, one to put them in to the age groups and then a second one to Group and count them by Town. Or you could possibly use a Crosstab to do the Grouping and Counting.

Can you post a zipped Access 2000-2003 copy of the Database for us to look at?
 

1 more replies
Answer Match 56.28%

Hi there,

I have a query that currently displays all data from certain fields in my database when I run the query.

One of those fields in the query is 'Date' another field is 'Name of student' and the other is 'description of duty'. What this is showing is on a particular date, what students have to do which duties around the school.

I need to create another field in the query which will count the number of students doing a particular duty on a certain date.

So for example:
On the 28/11/2011, there are 5 students doing the duty of cleaning up the common room.

Currently when I run the query this is shown as a date in one field, the duty in another field and the students doing the duty in yet another field.

What I want is another field called 'number of students doing duty' which would show (for the example I gave before) the number 5.

Any help ASAP would be appreciated.
 

A:Record counting in Access 2007

madbull94, welcome to the Forum.
This sounds a bit like School or College course work to me.
This may take more than one query to do what you want, one query to Group the Date and Duty and then a second query based on the first query with the original table added and linked via the Date & Duty fields to count the number of Students.
 

1 more replies
Answer Match 56.28%

I have a table with about a dozen Yes/No boxes. The Table has a column for each Yes/No choice. Each user will go to a form with 12 choices and can click from 1 to 12 choices. I would like to be able to have a report for each user that will show the number of selections.

Example:
User #1 picks 4 of 12, User #2 picks 8 of 12, and User #3 picks 12 of 12.
I would then like to be able to run a report that looks at at each row in the table and counts how many "yes" then divides that by the number of choices and displays a percentage.

Output on form would be something like:
User #1: 33%
User #2: 67%
User #3: 100%

What would be the best way to perform this task with me stating that my Access 2007 skills are in the beginner stages? Thanks in advance for any suggestions.

Danny
 

A:Counting Yes/No Boxes in Access 2007

6 more replies
Answer Match 56.28%

Hi All,

I am trying to put a combo box on a form that will allow the user to both scroll through the list and start typing the first name of the employee to find the desired name. Clicking on the desired name would open the appropriate record.
So far, I have successfully created:

A 'multi column' combo box, that shows the information that I want, called "cboSelectBEP"
The combo box lives on a form called "Basic Employee Profile"
The combo box is fed from a query called "Basic Employee Profile Query"
All the main data is kept on a table called "Employee Information"
What I seem to be missing is the following functionality:

The combo box shows the test record (ie the list) when you click on the arrow but does not allow you to type the employee's name in to search
When you do click on the test record, it doesn't actually go to any record - other than the default record - btw - how can I change that so the form comes up blank?
I think part of the issue lies in the fact that my query takes the First & Last names from the "Employee Information" table and puts them together in a 'full name' format. I know the topic of comboboxes with 'search while type' functionality has been covered many times before but I've been searching the internet for 3 days and can't seem to find where I'm going wrong! Any help is appreciated!

To make it easier envision the situation with the... Read more

A:MS Access searchable combo box on form to access records

It is a property setting on the form that is causing the problem.
I have added a find combo to my form in your original database that works fine.
 

1 more replies
Answer Match 55.86%

Is there a way to count the number of times a certain character appears in a string in access?

I have a field in Access containing the following string: "1_2_3_4_5_6".

I want to know how many times "_" appears in the string.

I tried:

count(instr(fieldname, "_"))

but that doesn't work.

Any ideas?
 

A:Solved: Counting # of characters in string in Access

You can use VBA

Code:
vArray = Split(vString, "_")

vCount = UBound(vArray) - LBound(vArray)
where vString is your string variable
Rollin
 

2 more replies
Answer Match 55.44%

Not totally sure how to explain my problem, so I will start by explaining my goal:

I'm trying to add a new report to an existing database (the creator left the company, leaving no one who knows how to use Access). I've learned a lot, mostly how to mimic the existing items, and have managed to create a few queries and reports on my own...but this one has got me.

I have a "template" Excel workbook which I load into the database using a macro I wrote. It creates two tables, one for a "running total" (this is for a monthly report) and one "temporary" table which I reference for month-to-date stuff (year-to-date queries/reports reference the actual database's table).

This template consists of columns containing our companies divisions, each row capturing a specific transaction processed by that division, and a column identifying whether that transaction was "late" (via ones and zeros). See below mock-up for an example:

The goal is to use this imported data to populate a report, summarizing each divisions performance with a column for each specific transaction as exampled in the following picture:

I'm able to create queries that capture the number of transactions per division...but I'm having trouble translating the queries into a report. After trying for a few hours, I achieved the following:

But I'm guessing I need to build a query that can create a separate column for each transaction type? Or is t... Read more

A:Solved: Access 2003: Grouping and counting for reports

6 more replies
Answer Match 53.76%

Hi:

I know its me again!!! 1 more question, I created a a form with the fields I want. SO now I have records in it but I noticed that everytimeI open this form to enter new records, it defaults to the first record, and when I want to go add another record, I can just click on arrow to go to the last record. I would have to hit tab through all the fields on the first record before I can go to another record. What am I doing wrong? Is there a way to where if I opne the form to add another record, it will default to a blank record?
 

A:Access - Records

This will work as long as you are using a button on another form to open the form in question.

In design View pull up the properties for the button that opens the desired form. In the properties box go to event procedures and OnClick Where it says event Procedure Click.

You should then be presented with the VB code behind the button.

Change the Code so it looks similar to this(You may have to play around with it slightly i dont actually have access on this machine so im kindof remembering.

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub
Hope this helps
 

3 more replies
Answer Match 53.76%

I just tried to learn access to create a database for healthcare testimonials for a new amazing anti-cancer drug my company came out with as well as for some of the other products we have . I wanted to create only 3 fields: Products, Condition and Data. I would cut and paste testimonials into the Data Field. I was able to do that using the wizard. I then expanded the Data field to accommodate room for testimonials in Design View. However, when
I tried to copy and paste a testimonial into the Data column in Datasheet view, I got a "the text is too long message." The text was only a paragraph.

It appears the program is seeing the record space as a field space which only allows for 255 characters, yet I appeared to follow the wizard in creating the form. I'm completely confused.

Regards,

Mark
 

A:records in Access

10 more replies
Answer Match 53.34%

I am trying to archive records that are displayed on a form from [video table] to a [deleted video table] from a button on the form using an append query. But just cant get it to work. It keeps coming back with 'about to append 0 rows' even though there is data in the video table.

Please can you help.
 

A:Access: Archiving Records

6 more replies
Answer Match 53.34%

Hi!
I am operating in Windows XP and switched from Office 2000 to Office 2003 about a year ago. I created a database for storing environmental data for my company near the end of 2005. I linked the tables in the database to worksheets in an excel file so that we could manipulate the numbers a little better, but have the convenience of the searchability of access. Everything worked fine until I switched over to 2003. When I went into my database it would not let me edit the data or enter new records. Since then, I've been having to logon to other computers in the office who still have Office 2000 to enter the info.

Well, two weeks ago all of the other computers in the office were switched over to Office 2003 and now, I can't get enter anything. I even recreated both the spreadsheet and the database and it still will not let me edit or add. Any ideas?
 

A:Access not allowing new records

14 more replies
Answer Match 53.34%

OK.. I have a database in Access 97. and it does everyting thing I want. the one thing I am having problems with is copying one record onto another. is this possible. the format of the database is as follows

you choose a customer. which will allow access the the subform on the same page call ordermain. with the key being order number. in this subform there will be details regarding an order , ie. date, contact etc. this will also have a subform linked via the ordernumber 1 to many ratio. called orderdetails on this subform you will have various items which are the main quote. what I would like to do if possible is copy the orderdetails subform onto a new record. how can I do this?

Also when I am using the orderdetails subform I have 4 boxes, item no. price description and qty. in the item no. I currently pu inthe number 1, 2,3 etc manually is there a automagic way of the database auto numbering the items in relation to the number of itmes I have on the subform.

Fuew thanks
 

A:Access- Copying Records across

I don't know what you mean by "copying the record"--i.e. where you want it to go. Should be as simple as running an append query with the appropriate criteria set, then maybe requerying the subform if you're trying to do it with the subform open.

As for setting the number automatically, you can use the BeforeInsert event of the form--this will not show on the form until the record is saved, but it will work correctly, together with the DMax() function, the appropriate criteria (ordernumber=x), and a + 1.

I'd suggest doing a little reading in Access help on append queries and DMax as a place to start. And you will have to be able to write (simple) macros to do it, so if you aren't familiar with them you should spend a little time learning about them in help.
 

1 more replies
Answer Match 53.34%

I have the following code in a text box on a form:

=IIf(DCount("*","[qryContactsExtended]","[ID]<>" & Nz([ID],0) & " And [ContactName] = '" & Replace(Nz([ContactName]),"'","''") & "'")>0,"Possible Duplicate","")

It just comes up saying #Name?

Any ideas?

Thanks in advance.
 

A:Access duplicate records

Quite a complex string to troubleshoot visually, #Name often means Access can't resolve a field or control name. Fully qualifying the [ContactName].[Table1] if its in tables and queries could help. Also if the text box itself doesn't have a unique name that can cause the error.

Also the DCount function is very touchy (technical term ) about its syntax. You might pull it out individually and make sure its working correctly.
 

1 more replies
Answer Match 53.34%

I understand VBA but I have only limited experience using it with Microsoft Access so maybe some of you can help! I have written a macro that takes information from an Excel workbook and uses it to populate a new record in an Access database table. The first field in the database table is an auto-number field. After inserting the new record I need to set a variable equal to the auto-number that the new record was just assigned. How is this done?

Rollin



Rollin
 

A:Access VBA - Inserting Records

You can always create a recordset and extract the value of the last record in that particular table and add one to it and this will give you your number you are looking for.

something like strNewnumber = Max of (field_PKID) +1
 

1 more replies
Answer Match 53.34%

Hi, I among my group of coworkers, am an access user. We have been using an older access database for a few months now, I have created a new database because the older one is becoming out of use. There was one problem in particular about the older database, it was located on a network drive, which 5 other users accessed to input records, it was a common problem where the database would lock up because more than one user was on the same record, is there any way I can work around this issue? I would like to put in this troubleshoot for the new db I have created.

Downwitchyobadself you can take a breather on this one bro, you helped me out in the past, if any other techie knows how to take care of this one I would appreciate your help.
 

A:Access locking records

Please always give version.

Anyway, Tools-Options, Advanced tab. Check the different locking levels.

(That's all until Down gets here, LOL)
 

1 more replies
Answer Match 53.34%

I don't know if this is possible but this is what I need to do.
I have a table that has the following info

Item #---------- Finished Good
101 -------------10554
101 -------------10585
101 --------------10875
102 ---------------10554

What I need, is for another table or form, combine the finished goods into one row, seperated by either a comma (or something like that) or each Finished good put into seperate columns for the Item # it matches.

Item # -----------Finished Good
101 --------------10554, 10585, 10875
102--------------- 10554

or

Item ---# Finished Good1 -----Finished Good2 ---------Finished Good3
101 ---------10554 ------------10585 ------------------10875
102 10554
Any suggestions?
 

A:Combine Access records

6 more replies
Answer Match 53.34%

hello again!
i just want to ask if there's a query where i can search for just one specific data from all of my database records, without opening each and every record?
 

A:MS Access searching through records

cutiemae, yes of course, you can enter your search requirement in a Form and a Query will display the results on a Seperate Form or a Subform.
You can also do the same thing using Visual Basic to set the Form's Filter as well.
If you post a zipped copy of your database (it doesn't need any data in it) and tell us which Field(s) you want to search we can show you how.
Or alternatively if you want to try it yourself there are 2 really good Threads on here that show other Posters building there databases, including "Searching".
 

3 more replies
Answer Match 53.34%

Hi,

I have a database with "Customer purchase Orders" records made up of 3 tables: The order has multiple fields.
Order Header Table
Order Details Table
Customer Table

When entering a new order, if it is a repeat order, to save on data entry, I want to copy the contacts of the previous order and make a new order (exactly the same records in each field, except the Order Number ID obvioulsy) Is there anyway i can do this?

Thanks
Simon
 

A:Access - Copying Records

It all depends on how you design your form, I would think. This is sort of guessing at your DB's setup because your terminology is a tad confusing, but if your form has a header with fields for, say, OrderID and CustomerID (among other things), and if your customer data ("contacts of the previous order") is stored independently of the orders--looks like this is the case--then you could use the CustomerID field in the form's header to populate the customer contact info fields in the form's detail section.

Is this what you're talking about, approximately?

chris.
 

3 more replies
Answer Match 53.34%

Hello everyone,

I am using Access 2010. I want the user to be able to search by name or ID to to find out what systems they can access and to what level they can use the system.
This is determined by the department and employee level. I have a separate table for the users with their personal information and another table with the system profiles.
Any help would be appreciated.

Thank you.
Roshni
 

A:Look up Records in Access 2010

You can do this with a query that combines data from the 2 tables.
Create a query based on the Employee table and then in design view add a criteria to identify the user then add the systems profiles.
You can then create a Form or Report based on this new query to display the data.
 

1 more replies
Answer Match 53.34%

I want to sort records by day of the week. Monday should come first and Saturday should come last in a Query. How do I do that?

I have a table called "DAY" which simply has the days of the week from Monday to Saturday. Another table is called "Schedule" which contains all other data and gets the names of the days from the "DAY" table.

I have a query that searches for strings in the "Schedule" table and displays the results including the day of the week. Since the "Day" column in the Query is set for "Ascending", Friday always comes first in the queries. However, I want the records for Monday to come first, followed by Tuesday, Wednesday and so on.

I also have a report that displays the data based on the Query made. Again, the report sorts the names of the days alphabetically (All Fridays first, then all Mondays, then all Saturdays, then all Thursdays, then all Tuesdays, and then all Wednesdays). However, I would like the records to be sorted having all the Mondays first, then all the Tuesdays and so on. How do I do that? Thanks...
 

A:MS Access: Sorting records the way I want....

By the way, I am using Access 2002 (XP).
 

3 more replies
Answer Match 52.5%

hi, I am complete beginner with all things programming but I have built a temporary database using Access 2007. It has lots of tables, forms and queries. I havenít created any macros or used SQL, just kept it really simple.
The DB is on one of our shared network drives and the db is used by 4 people.
I have made it possible for us all to open the database at the same time and I have changed the record locking type to edited record.

Up until recently we havenít encountered any issues, however now when one of the users changes a record or many records the details are lost when the person disconnects from the network.
Also when I look at the folder on the shared drive the information is wrong or out of date. an example; user a opens access db the locking file is created when she looks at the shared drive, however when I look at the shared drive I cannot always see the locking file.
Another example is the DB locking file shows me in the file when I do not have access open.
Weíve tried testing with creating notepad files in the same location to see if the same thing happens it doesnít.
Iíve moved the db to another shared drive and the issues are still there.

Any advise would be appreciated as our internal support are stumped.
 

A:Access 2007, records lost. Please help!!

Open a new database on your own computer and import all of the tables and check them in that pay particular attention to the sequence of any Autonumber fields to see if any and How many of them are missing.
 

2 more replies
Answer Match 52.5%

I have an Access 2003 database of only moderate size and complexity. I would like to add a field (column) to each table showing when that record was updated. I would like this to be automatic; that is, when a record is saved, a current timestamp should be inserted into that field ONLY IF the record has been modified. Any ideas??
Thanks,

ThomasNorman
 

A:Timestamping records in Access 2003

How long do your processes take? You will probably want to "freeze" the timestamp so that everything that runs for a particular process has the same timestamp. For Example, I used to have to run this complicated payroll process that took about 3 hours. If the timestamp wasn't frozen, some tables would be updated at 2:00, others at 2:10, etc... With the timestamp frozen, all tables appeared to be updated at 2:00. - And in fact, there were other indicators within the tiemstamp (PYRL0200RTC). This gives every record that was updated the exact same timestamp. This way if we found out the client sent us a bad payroll file and we needed to delete the records, we could delete by our timestamp field. Just something to think about there.

Do you have the timestamp field created in the tables that you wish to update? If not you will probably want to create a loop that creates that field in all of your tables.

If you are starting from "scratch" (that is no tables have the timestamped updated) and you want to pro-actively update them, you can create a loop for that as well. Otherwise if you are looking to update the new timestamp fields as they occur within the regular flow of your applicatoin, then you may want to create a sub that updates the timestamp and then everytime records get updated, call the sub.

HTH
Thanks,
Roger
 

2 more replies
Answer Match 52.5%

Hi all,

I have a problem with records disappearing on a report in Access 2007. This is with a fairly simple database to track overtime. It only happens when there are so many items in the report that it is forced to continue on a new page. The record that should be the first on the new page just is not there.

If I reduce the font size to squeeze everything into 1 page, there is no problem. When I set the size back to normal and it flows onto 2 pages, I have a missing record.

The SQL used to select the data is as follows:

SELECT Staff.FirstName, Staff.Surname, Staff.SemesterSubBank, Staff.StaffID, [Staff cover].StaffCoverID, [Staff cover].StaffCovering, [Staff cover].CoverDate, [Staff cover].Category, [Staff cover].Reason, [Staff cover].Lektionen, [Staff cover].Kontaktzeit, [Staff cover].Printed, [Staff cover].SubBankEligible, [Staff cover].Time, [Staff cover].Class, [Staff cover].Subject, [Staff cover].Notes
FROM Staff INNER JOIN [Staff cover] ON Staff.StaffID = [Staff cover].StaffCovering;

There is also some VBA to decide which of these records to display, and to do some adding up,

Code:
Public LektionenTotal As Single
Public KontaktzeitTotal As Single
Public MonthlyLektionenTotal As Single
Public MonthlyKontaktzeitTotal As Single
Public BankTotal As Single
Public StartDate As Date
Public EndDate As Date
Public RecordCounter As Long

'#########################################################
'#################Change dates at bottom##################
'#####... Read more

A:Access records disappearing on report

9 more replies
Answer Match 52.5%

Hi there,
I'm working in Access 2003. I'm running XP Pro, SP-2. My problem is this:
I have a form that is set up to drop data into 4 tables. As I was entering the data, the number of records increased. I also made sure to save frequently. When I finished for the time being, I could scroll through and see all 16 records that I had entered through the form. Then I closed the program. When I opened it up again and went back to the form to continue inputting data, I noticed that it said only 4 records were there. When I checked the tables I saw the data for all 16 records. How come I'm not seeing that in the form? I've tried looking everywhere and I feel like the answer is right under my nose. Any ideas?
 

A:Access Form not showing all records

8 more replies
Answer Match 52.5%

Hi all

I have got a database which i use to log customer details and repairs done for them.

Is there a way i can have a form which i use to search certain feilds in the database? so i can search for the customer details by entering there name or postcode and then have a list of results which ten allow me to open those details in my customer form so that i can see all of their details and the repairs done for them?

i dont know if all of this can be done in access but thought i would ask now before i get to many records to change things.

I have attached a database which is exactly the same as the one i use but with out the customer details, i have put a few made up records in there. There are also a few tables and forms that are not finished so dont worry about them.

Thank you for all help.

JPLamb
 

A:Solved: Search Access Records

6 more replies
Answer Match 52.5%

I wanted to verify that MS Access limitations is correct that you can not have more than 255 data elements in a table or in an active query.

I have an application with 1000 data elements so I divided it into 4 tables, for each table I created a FORM to add new records, I used the MS Access TAB and COMMAND facilities to create paths from FORM 1 to the other FORMS, but I can not pass the KEY from FORM 1 to the other FORMS?
 

A:Access Multiple Table Add New Records

11 more replies
Answer Match 52.5%

First of all, Im a total beginner and have only been using access for about a month - therefore, ive never run a query (so shouldn't have lots of tables, only the one im working on!)

My problem is that, after adding about 600 people's data to access, it has very randomly messed up about 5 records. Peoples names have turned into funny symbols etc. The database will not let me change their details; every time I re-open it they have gone back to funny symbols. Also, they cannot be deleted. Ive tried using 'delete' on the keyborad, the delete record function in access, deleting them from the spreadsheet view and so on, but it keeps coming up with the same message... "the search key was not found in any record", whenever I press delete. The database will also not let me run a query until these data are gone!

Help, Im totally stuck!!
 

A:Trying to delet records from access 2003...

claire, have you tried Compacting & Repairing the Database?
To do this open Access, (not the database) and go to Main Menu>Tools>Compact & Repair database
Browse to your database and double click it (or single click and click OK) and then when it asks for a name to Save it as, I would click on your current database and add something on the end to denote a newer version. I increment a version i.e.
databasename v 0.0.mdb increments to databasename v 0.1.mdb.

Is there only the one table or do you have other Tables with Relationships set up to the "Problem" table. If you do you might have to delete those relationships before you can delete the records.
 

1 more replies
Answer Match 52.5%

I have most of my contact information in hotmail. Is it possible to simply move it either to excel or access? If not, its a long, manual retyping effort.
 

More replies
Answer Match 52.5%

Hi there all

My task is to create an Access database to hold a company phone book. In essence its simple, but the area I am in a fix with is that there is a requirement to be able to add batches of sequential phone numbers (say 1234 to 2345), with identical "properties" in other associated fields, such as phone type, floor, company team name, manager, cost centre and so on, these other fields will be fine tuned later on.

I have a form which holds the starting and ending phone numbers and all associated fields.

Looking at my MDB, I can't figure an elegant way to have a commande button that will go off and add (2345-1234) records to the table in question.

I started with a simple loop that relies on the form being based on the source table:

For Telnum = HiNum To LoNum
DoCmd.GoToRecord , , acNewRec
mstrPRN.Value = Telnum
Next telnum

But this is a but crunchy and inelegant.

Any ideas?

Thanks in advance

Chris
 

A:Solved: Access ... Adding Records...

10 more replies
Answer Match 52.5%

I have a booking databasewhich includes room, date, session in a table called booking
I want to find a way of ensuring that before a new booking is added it checks the existing table to see if their is no booking with the same three field values and returns a warning explaining that the record cant be added otherwise it is!
 

A:Conditional adding records in access

Do you want to do this on a form? Or where?
 

3 more replies
Answer Match 52.5%

Hi
I have created several parameter queries in my database. They show the initial records, but if i add new records, the records show up on the form and in the tables but do not appear when i run the queries again. This happens on single table and multiple table queries.

As an example the criteria i use is: Like "*" & [Enter VRN] & "*"

Grateful for any help.
 

A:access query not all showing records

It is most unusual for Single tables queries not to show the records, but multi table queries rely on getting the Joins correct to show them if there are no "matching" records in one or more of the other tables.
 

1 more replies
Answer Match 52.5%

Lengthly, I know. My apologies.....

I know that there has got to be an easier way to do this than what I'm doing.... Here's the situation. We want to be able to get our questionnaire information into our Access DB.

The way the questionnaire program dumps the data, it puts about 5 fields of identifier info first and then "question number", "answer", "question number", "answer" all onto one row of a flat .csv file. We can import that to a table in Access fine. But, as different questionnaires have different numbers of questions, if we put everything into one big table, we have wasted space (and energy) since we would have to have the table big enough to support the biggest questionnaire.

We want to get it to where our Access table has each question in it's own record instead of all combined together. The way I've done it (which does work) is to import the csv file into a table, and generate more queries than necessary, all hooked up through a macro, to append each question's info into the "broke out" table. But, this gets cumbersome as I have to have 50 queries to cover a questionnaire that has all of the questions in it and then each one will have to be changed if anything is ever added to the identifier info (each one basically says to pull x and y columns, which would change if an extra field is added to the identification fields).

I know that there has got to be a way to either handle this befor... Read more

A:Access 2000 - Breakup Records

Open a Module window and then bring up Help. Search for the READ and WRITE methods. This will allow you to read and write to a streaming text file. If you can control your format in such a way that you have a header record that has your identifier fields and then some way of distinguishing between questions and answers, this may be the way to go.

Hope that helps.
 

3 more replies
Answer Match 52.5%

Hi,

I have created a list box in my form and I would like the choice of when clicking on a list item of that box be able to jump to that particular recordset instead of having to scroll thru the records. Could someone help me with the appropiate code in VBA to do this? Would this be an ON CLICK EVENT?

Thank you!
 

A:Accessing ACCESS records thru a ListBox

7 more replies
Answer Match 52.5%

I have an Access report that includes a sub-report in whihc I would like to print the records on one row, rather than one column. So far, I have been unable to find a solution.

Example Record:

Job Number: 123456
Version(s): 001,002,003,004
 

A:Access Sub-report records printed on one row

7 more replies
Answer Match 52.5%

the method access reccommends doesn't work for me. I need to delete duplicate records in a Foxpro database that is linked to Access.
Are there any programs I could write that would eliminate every other record in the duplicates query?
 

A:deleting duplicate records in access

8 more replies
Answer Match 52.5%

I can't seem to figure out how to get the data together into one table, one report for each. I hope I can explain this right.

I have 3 tables from a seperate database that I am using.
Table 1 - is the Item Master. I only need the Item # and Item Description from this table.
Table 2-3 all have Item #, Year, Warehouse fields in common. But then they each have different fields
Table 2 has Qty Received Period 1, Qyt Received, Period 2.... and so on.
Table 3 has Qty Sold Period 1, Qty Sold Period 2..... and so on.
Table 4 has Qty Issued Period 1, Qty Issued Period 2... and so on.

I need to have one record be combined for a Item #, warehouse and year - and have tables 2-4 data in the same record together. (This is where I am having issues) Do I use the aggregate group function? Is Access the best way to do this?

Then I need to have the final product be something to have the user pick the year, pick a single or multiple warehouses, and pick single or multiple Item #.
 

A:Access - how do I combine records for these tables

You need to break this down in to single steps at a time.
Create a query of the Table 1 data that you need, then add just table 2 data and get what you need. Then add table 3.

What version of Access are you using?
 

3 more replies
Answer Match 52.5%

hi again i am getting myself totally confused here with queries?, can anyone help please!

i have 2 tables - " messages" and " Textmessages" both tables have a field called message1

if i type a message into the textbox message1 on a form and click a button it then adds it to the table "Textmessages", how can i then copy that message into the table "messages" updating all the records in that table, using vba, i know i can do it manually copy and paste) but i would like it done in vba if poss.
thanks in advance

scouse13
 

A:Access 2007 moving records

6 more replies
Answer Match 52.5%

I am preparing an end of year report and some of the records have more than one episode...how do I hide the records with duplicate names and dob
 

A:hide dup[licate records in access

Something like:

SELECT DISTINCT MyTable.LNAME, MyTable.FNAME, MyTable.DOB
FROM MyTable;
 

1 more replies
Answer Match 52.5%

I am trying to calculate the elapsed time between records as shown below and want to apply that elapsed time calculation to the first of the two records. In the example below, I would want a field ElapTim which would be 0:00:42 seconds on record one, 0:18:01 min / sec on record two, and 2:06:45 on record three. Time could be in seconds and I could convert to hours or minutes as desired. Data would be sorted by User and ascending date / Time.
Any ideas how this is done?
Thanks! Al Farrar

User Date Time Location
16494 9/10/2013 3:00:47 PM BSTRT
16494 9/10/2013 3:01:29 PM B035A
16494 9/10/2013 3:19:30 PM B063A
16494 9/10/2013 5:26:15 PM B104-W4
 

A:Access - Elapsed Time between Records

Hello! I was able to figure this out with some SQL code I found on Mr.Excel

SELECT [qry-Tech-Scans-2].WorkID, [qry-Tech-Scans-2].User, [qry-Tech-Scans-2].Time, (SELECT MIN([Time]) FROM [qry-Tech-Scans-2] AS T WHERE T.[Time]>[qry-Tech-Scans-2].[Time]) AS [Next Time], ([Next Time]-[Time])*1440 AS Minutes
FROM [qry-Tech-Scans-2]
ORDER BY [qry-Tech-Scans-2].WorkID;

Al
 

8 more replies
Answer Match 52.5%

Hi all,
i am creating a small program which will use the database of my company access control program to calculate time and attendance details for employees. ie it will calculate overtimes and undertimes etc.
Employees scan their access cards at the door when antering and leaving the building.
a picture of The original database is attached (64kb).
Now during the day a single employee might scan in and out more than 10 times but i am only interested in the 1st and last scans, so i can calculate the time difference between these scans and thereby calculate overtime undertime etc.

So far i have been able to extract details for 1 specified date and copy them to a new database hwere i would now like to sort and delete all transactions which are not the first and last for a specific operator.

Here's part of my code :
Private Sub Form_Load()
Set db = OpenDatabase("C:\Documents and Settings\sebastian\My Documents\xxx\IT Project 2\ssAccLog.mdb")
Set dbTemp = OpenDatabase("C:\Documents and Settings\sebastian\My Documents\xxx\IT Project 2\DbTemp.mdb")

Set rs = db.OpenRecordset("ssAccessLog")
Set rsTemp = dbTemp.OpenRecordset("tbltemp")

Private Sub Command1_Click()
Dim v As String
Print rsTemp.RecordCount
If Option1.Value = True Then
z = 9
Else
z = 10
End If

On Error GoTo errorhandler

rs.MoveFirst
p = 1

v = Trim(Left$((Str(rs!DateTime)), z))

Reccount = rs.RecordCount
For i = 0 To Reccount
If v = Trim(DTPicker1.Value) Then
rsTemp.Ad... Read more

A:VB6 - selecting records from an access database

Look into the MIN and MAX SQL functions.
 

1 more replies
Answer Match 52.5%

I have a 12 year old access db that I have upgraded to 2010. Everything works fine on my development IIS servers. I have a new ISP this year. Sometimes, not always, still have not identified pattern, the execution of a stored procedure will return zero records.

If I build the exact same SQL statement and execute is as a text command, it works every time.
ie: use AdCmdText vs adCmdStoredProc

It is not just a problem with 1 query, it happens with many.

Searched high and low for anyone with the same problem, but no success.

Could it be some registry setting on the ISP's machine?

Any thoughts?
 

More replies
Answer Match 52.5%

So I'm not very familiar with Access or any database stuff to be honest, so bare with me on this one.

Access 2013, user has a shipping database with customer orders, etc.

Anyways, the database is updated every morning with a list of records in which the user will scroll through each record and do whatever she does (I believe check quantity requested vs quantity on hand). She doesn't actually modify anything, she just opens it up, scrolls through them, does something (not in Access), and that's that.

Well for the past few weeks now, when she scrolls through the records, she noticed it was skipping some numbers.

For example:

Say the database has 20 records. She'll be scrolling though them using the arrow buttons on the bottom left side near the record #. She does this and will get record 1, 2, 3, 4, 6.... for some reason completely skipping 5. So she presses the back arrow, and it will go too far back to 2. Then, if she uses the arrows again, 2, 3, 4, 5, 6, 7, etc.. until she realizes another record was skipped.

Has anyone else experienced this? I verified this myself when I remotely connected to her machine (she's in a different office), so I know she's not just accidentally clicking twice or something. Doesn't seem to be user error.

Thanks

A little more background info if needed:

Win 7 Ent x64
Office 2013 Suite
No other issues with the PC that we know of.
 

A:Access - Skipping Records when Arrowing

Are all the records there and are they in order?
You could give them a button(s) to press instead
 

2 more replies
Answer Match 52.5%

I have a table of data that is an export from a phone that I want to import into Outlook.
The problem is that for every different phone number I have a new record in the table.
EX. Ron Jones cellphone is line 1 Ron Jones Home phone is line 2, Ron Jones Pagor is line 3. If I import this into Outlook it will create 3 separate entries or records for Ron Jones.
I need to somehow bring together all the like records with all the phone numbers. Therefore Ron Jones will have his name and cell, home and pagor # on one record.
Any ideas would be appreciated.
 

A:Access or Excel merging records

7 more replies
Answer Match 52.5%

I am new with using Access. I am trying to maintain a contact database which consists of Company name, last name, first name, phone number, fax number, email address, address, city, st, and zip.
I am trying to find a way to merge records. For example:

Line 1 has Company XYZ, Smith, Bob, 555-555-5555 but not email
Line 2 has Company XYZ, Smith, Bob, no phone but [email protected]

Is there a way to merge the records to 1 row including all the information?
I am happy with using the address as a key.
Any suggestions would be helpful.
 

A:Help with Access 2007 merging records

16 more replies
Answer Match 52.5%

Hi, I have been searching online for answers to my question(s) and ran across your site. Very informative and a lot of helpful people here...

My problem...and i have already searched the forums on this but could not find it...I am new to Access and database creation so bare with me.

I have a database in Access 2007. It has 4 tables, Customer, Lease, Logo and Structure. I have forms created for each table. I am trying to create a search to search for customers.

1) I open the form and add a combo box.

2) I rename the combo box to SearchCustomer

3) I go to Row Source and create a Query Builder using the Customer table and add the CustomerName to the field. The CustomerName is the Key and always contains a unique identifier (lpus I dont want to have two of the same customers in my database). Save the Query builder.

4) My bound column is 1, Row Source Type is Table/Query, colum count is 1, column width is 2", and no column heads.

5) I go to the Form property and select the Record Source which promts me to create a Query builder for the for. Using the Customer table I add all of the fields. In the CustomerName field, I add the following criteria... Like [forms]![customer].[searchcustomer] & "*" I then save the Query builder.

6) then go back to the SearchCustomer combo box and under the event tab - After Update, I add the code...

Private Sub SearchCustomer_AfterUpdate()
Me.Requery
End Sub

I save everything and open the form and select the names in my cus... Read more

A:Access 2007 Search records

16 more replies
Answer Match 52.5%

Hi All,

I have a table in access which comprises of sales order details. I want to copy a whole record in this table. Every field will be identical except the primary key (order number). Is there anyway this can be done easily?

Ideally, I would like to have a form with a cmdButton labeled "COPY ORDER". When I click, it would ask me for the old order number. It will then copy the order and provide me with the new order number. I can then go into the new order and amend any fields I wish.

Any ideas?

Thanks

Simon
 

A:Solved: ACCESS -Copying records

9 more replies
Answer Match 52.5%

Hi,

I'm not that proficient in Access, so this might be a stupid question, but I'd appreciate some advice anyway.

Is it possible to limit the number of records in an Access table? I'm trying to create a standalone tool which only requires one record and am not sure how to go about it.

Any clues?
 

A:Restrict records in an Access Table

12 more replies
Answer Match 52.5%

For my AS Level coursework - we are managing a Village Hall Booking database.

Within the database there will be occasional and regular bookings which are set on certain days, and times.

What i want to know - is how you can insert automated records for the regular bookings within Access.

For example i need Play Group to be a regular booking on Monday and Wednesday Mornings.

How can i do this in Access?

Cheers,

Jon
 

A:Microsoft Access automated records

Anybody - any ideas to where i can get it the info from?
 

2 more replies
Answer Match 52.08%

Hi

I hope this is an "easy" answer.

We use an Access database to track class registrations for recreation programs.

Each program has a limit on class size.
How can I set a limit in Access so that when that number is reached, it displays a message....

I'd like to be able to continue to add records if it's decided that the class size can be adjusted, I really just want the message to appear.

I have a course table which has the course name, course code, start date, end date, and cost

I have a registration table in which all the registrants info is entered and the course is selected from the course table.

Thanks for any help with this.

Bobbi

 

A:Access 2K-Limit records based on a field

I would do a record count and see if it is equal to or less than the class limit. It is was, then I would do a messagebox or pop up window to give a message. you could to the message box with a yes or no. yes would add it, no would do nothing. the easiest is the message box. just look up msgbox in access help or give some more information on what you are doing.
 

2 more replies
Answer Match 52.08%

I'm trying to make a monthly report showing inventory that has moved and the cost allocation between operational units within the company.

I only need the records with the latest date in the date field to populate the report - there are multiple records with the same item (TagID) that have moved from Group A to Group B over the course of the month. We want to make sure Group B is charged the cost from Group A.

All items in records have prices, locations, users, etc related from other tables, so it's just this one table with the moves that's my primary concern.

Any ideas on how to write the query for this?
 

A:Access: Report showing latest records

I would suggest 2 queries, the first uses Totals and Group by on TagID and Max on the Date field.
Then the second query uses the first query and the Table (joined by TagID) to add the other data.
 

3 more replies
Answer Match 52.08%

Hi,

I am trying to delete & insert records from my Access database using excel macros. I have been using Select query with recordset which I tried to extend to Delete & insert commands but I realize that I will have to use Execute Method(DAO) to do so. All the example seems to be with QueryDef.. can some one to direct me to a resource for the same or help me find a way around ..thanks
 

More replies
Answer Match 52.08%

I have a table like this:
|==============================================|

|Type |Name |Weight | Date of weighing |

|==============================================|

|Children | Gablu | 22 | 4/4/2007

|----------------------------------------------|

|Children | Gablu | 26 | 5/8/2007

|----------------------------------------------|

|Adult | Gablu | 20 | 1/3/2007

|----------------------------------------------|

|Children | Sumo | 18 | 7/8/2007

|----------------------------------------------|

|Adult | Sumo | 30 | 9/10/2007

|----------------------------------------------|

|Children | Sumo | 27 | 9/10/2007

|----------------------------------------------|

NOW, I wan to write a query that will be something like this.
SELECT Name, Weight

FROM MYTABLE

WHERE TYPE=Children AND DateOfWeighing=Max(DateofWeighing)

This should return two distinct records (i.e Second and Last one) with latest date for a Child->Name

Can anyone help.

With regards.
 

More replies
Answer Match 52.08%

I am in the process of developing an Access 2002-based system while simultaneously learning Access. I have been able to sort things out pretty well with one notable exception. I would like to be able to invoke an action that will automatically create a new record in a Transaction Table for each record that exists in a Customer Table. My logic is that at the beginning of each billing cycle, a transaction record is created which can subsequently be manually processed when payments are received. Is this doable from within Access, or will it require VB or ???
 

A:Access 2002 - Auto Create New Records

juandeaux said:

I am in the process of developing an Access 2002-based system while simultaneously learning Access. I have been able to sort things out pretty well with one notable exception. I would like to be able to invoke an action that will automatically create a new record in a Transaction Table for each record that exists in a Customer Table. My logic is that at the beginning of each billing cycle, a transaction record is created which can subsequently be manually processed when payments are received. Is this doable from within Access, or will it require VB or ???Click to expand...

From what I understand, you want to periodically take a "peek" at the Customer Table, and then create a new record for each customer, in a Transaction Table.

Yes, it can be done using Access - but the simplest way to do it uses VBA (within Access).

I am not sure what kind of interface you have (forms, menues, switchboard or what) - but basically you can create a snippet of code that would say something like (this is just a sample... so it won't 100% work if you cut and paste it):

dim db as dao.database
dim rst as dao.recordset
dim sSQL as string

set db=currentdb
set rst = db.openrecordset ("Select * from CustomerTable;")

docmd.setwarngings false 'so you don't see warnings for each insert
while not rst.eof
tempCustomer = rst!customer_id
'.... etc... get the rest of the customer data you want to grab for the transation table
sSQL = "INSERT IN... Read more

1 more replies
Answer Match 52.08%

Hello all,

I am working on someone else's form that has several
combo boxes and text boxes. In this example, there are
four tables, "table_A" , "table_B" , "table_C" and "table_D" .
We will call the form "form_A"

There is no pertinent VB Code.

The record source listed is "table_A"

The control sources for the objects in the form come from fields in "table_A"

When I run the form, I click the >* at the bottom in
order create a new record. I then get focus on Combo Box "combo_A"

For object "combo_A" ,
the Row Source Type is Table/Query
the Row Source is a Query, we will call is "query_A"

"query_A" is a join between the other three tables, and returns only one
field, the customer name.

At this point I select "Records/Refresh"

So what the [email protected]#!ell happens at this point? All my text boxes are getting
filled with data
which I think is coming from "query_A", even though only one field is
returned in the
query. How can I determine what is happening behind the "Records/Refresh"
command?

Any help would be greatly appreciated!

Thanks
 

A:Solved: Access:Records/Refresh-What Happens Behind this event?

The query is probably populating the Customer name field on the form, when you records/refresh Access picks up the form's fields from the record associated with the Customer Name.
Is the Combo "bound" to the Customer name field on the form?
To actually find out what is happening I would need to see the database, can you post a "zipped" copy on here. Do not forget data protection, so if there are names and addresses etc involved you need to remove them and create some dummy data for the version you post.
 

2 more replies
Answer Match 52.08%

I have a bound form that is not registering new records that I added in the corresponding table, any ideas on what the issue could be? I am still learning the in's and out's of access 2010 but have never had this issue.

Many Thanks!
 

A:Form in Access is not registering new records in table

tookiemouse, welcome to the forum.
I do not have Access 2007/2010 but I can ask some general questions.
Is the Form's Data Entry Property set to "Yes", as it is does not allow displaying of olod records, only the entry of new ones?
Can you enter Records in the Form?
Do they appear in the Table?
 

1 more replies
Answer Match 52.08%

I have a data entry form, which if any fields are left blank will disable the save button. What i need is for it to also allow me to close the form without saving, or even attempting to save (which results in errors) the record.

I found this online which works fine apart from getting a prompt that access cannot save the record and the option to close the form, but i want it to not save, just close.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.ItemID) Or IsNull(Me.Title) Or IsNull(Me.Category) _
Or IsNull(Me.Type) Or IsNull(Me.HireFee) Then
Cancel = True
strMsg = "Please complete all fields," & cbCrLf & _
"or press Esc twice to undo."
MsgBox strMsg, vbExclamation, "Blank fields"
End If
End Sub
Any help would be great, cheers,
Mark
 

A:Access 2010 - Prevent blank records

7 more replies
Answer Match 52.08%

Iím creating a website that will handle tons of data, using MS SQL Server for the active content, basically dropping records into a variety of webpage templates for the end-user. But, my back-office first has to sort through tons of raw data and whip it into shape, drafting and editing text, cropping/resizing photos, assigning links, etc. Iím using Access 2003 for that process, to handle everything that is not yet ready to be imported into the SQL Server for publication to the live website.

I understand that I can display data from an Access db on a webpage, somehow (just a facsimile of the real webpages in which it will be displayed, later, one record at a time). It would be very helpful for the back-office staff to have an in-house, interactive way to see how the data theyíre editing will eventually appear on the website, giving them insights into how they need to adjust it further.

My head is reeling from all of the different stuff Iíve read about how I might do this. Can you all give some general advice on how I should get started, which direction I should take?

Thank you.
 

A:Displaying Access data records on a webpage

Access 2000-2003 uses Data access Pages to display data on the Web. They are an html version of a Form and can be created by saving a Form as an access Page.
See
http://support.microsoft.com/kb/264080
 

1 more replies
Answer Match 52.08%

Hi there. This one has me baffled. Below is only a sample of my data in a table in Access. I want to delete only the records that have repeated codes. But I want to keep only the first code on the list.
CODE VEND_PO RECV_DATE UNITPRICE
B115 090187 20040610 2.72
B115 90203 20040610 2.72
B115 90172 20040121 2.64
B115 090126 20040108 2.64
B115H 90308 20060504 5.36
B115H 090173 20040610 5.2
B115HH 90280 20050805 9.16
B115HH 90242 20050311 9.16
B116 90522 20050214 1.25
Here is what the finished table would look like

CODE VEND_PO RECV_DATE UNITPRICE
B115 090187 20040610 2.72
B115H 90308 20060504 5.36
B115HH 90280 20050805 9.16
B116 90522 20050214 1.25

Notice that only the first duplicate is left and the others have been deleted. If the code has no duplicates then that code is not deleted.

Thanks

Mario
 

A:Solved: Deleting only certain records in an Access Table

6 more replies
Answer Match 52.08%

How do I filter information from records that contain year -2003 from date format 01/17/03 (Do not want to filter by day or month)?? What is the correct criteria information??
 

A:filtering records/access 2000/criteria

Create a query with desired fields.

Put this in the criteria for the date column:

Between #1/1/2003# And #12/31/2003#
There's another way to do it by using just the year, but I'm not sure of it.
 

2 more replies
Answer Match 52.08%

Juts like the subject say's..... How do I copy, migrate, import teh records from one table to another table in the same DB.. Access 97 by the way.... Dreamboat I am gonna time you on this one... Your my hero..
 

A:(Solved) Access copy records from one table to other

9 more replies
Answer Match 52.08%

Hi Guys
First timer - so please tread gently! I'm sure this is a similar situation, but wasn't sure whether it warranted another separate conversation.
I have a situation on an unbound list box in Access 2000 and haven't yet got to the bottom of it. I have a box which displays incorrect records for the user to correct. When they first go into the box, they see all 500 records (for example) and use a dble click event to edit the one they want, when they return to the box it will only display the first visible amount of records. They can use the scroll bar but the records do not scroll!

The Code behind ( and here's where I get lost - I don't do VB!) opens another form, allows you to edit the record, saves the changes, closes the form and returns you to the original form. We have tried keeping the original form open, as it was closed and reopened, but no change.

The DB has been inherited, so I have no idea why a List box has been used an not a combo.
Forms data is taken from a query.

I'll work out how to post a copy of the empty DB to you!
Jacally
 

A:Access 2000 - Not displaying all records on Form

6 more replies
Answer Match 52.08%

I have Access 2000. I have gotten one of my databases imported into it and created a query and report.

Now when I go try to change a record, I am getting "updating data in a linked table is not supported by this isam"

Help tells me that i need "installable isam". What do I need to do? I've never run into this before.

It's a simple subscriber database I use to print mailing labels. I had it in MS Works, but the new works is too complicated and I can't figure out how to print my labels in it. :S
 

A:Editing Records in Access 2000 table

I don't know about the "installable isam", but I did just import an Excel spreadsheet into my new DB and noted the option to "link tables" rather than actually import the data. It sounds like you may have accidentally done this. Unless you've drastically changed the data in your table, I would change the name slightly and import the data again to test this. Save the new table as the name referenced by your query and report and see if you get the same error.
 

1 more replies
Answer Match 52.08%

I currently have a macro that selects all records in a table and deletes them. The problem is when the macro deletes the records, it prompts the end-user for confirmation... Which I don't want it to do. I just want it to delete the records and continue with the other macro procedures.

Is there a way that I can setup the macro so that it doesn't prompt the end-user for confirmation? I know I can turn off deletion confirmations all together but I don't want to do that either.

FYI - The part of the macro that I am referring to is setup like so:

Open Table (Opens the respective table)
Run Command (Selects all records)
Run Command (Delete Record)
Close (Closes the respective table)
 

A:Access 2K: Macros - Delete Records Without Confirmation

8 more replies
Answer Match 52.08%

I am relatively new to access 2007, so forgive me if this is simple.

I created a database to store Overtime hours for our employees(50 or so)

There are only two tables:
Employees(Employee #, Last Name, First Name, Active(yes/no))
Overtime Hours(Employee #, DateWorked, Hours Accepted, Hours Rejected)

The tables are linked with a one to many relationship on Employee#

I was able to import the data from the excel sheet we use, and generate the reports we need from that without a problem. However, I am stuck on the hour entry form.

What I would like is to have the form to have a date entry text box on the top, then display the list of active employees(gotten from query), and next to each employee have a place to enter the hours accepted and hours rejected. Everything I have tried so far has failed.

I tried making a multiple item form bound to the active employee query, with 3 text boxes(HoursAcc, HoursRej, DateWorked) that has sql that executes from a command button using the following code

INSERT INTO OvertimeHours (EmployeeNumber, DateWorked, HoursAccepted, HoursRejected) VALUES ('" & EmployeeNumber.Value & "', #" & Format(DateWorked.Value,"mm/dd/yyyy" ) & "# ," & HoursAcc.Value & ", " & HoursRej.Value & " )

This has three issues,
1. Filling the Hours Accepted, or Hours Rejected text box next to any employee causes the text boxes for all the employees to be filled.
2. It only ... Read more

A:Access New Records from Non-bound table in a Form

WolfRoolz, welcome to the Forum.
I think that you have 3 problems with your current setup.
1. You are you using Unbound Text boxes for some reason?
2. The form for Hours should be based on the Overtime Hours table.
You are using an Insert into SQL Query when you don't need to.
Ideally you would have an Employee mainform which also has the date selection/entry on it, the Employee selctable from a Combobox.
The Subform would have the Employee # as it's Master/Child link to the Mainform and the date would be transferred by some simple VBA (or you could have it entered on the subform)
 

1 more replies
Answer Match 52.08%

Is it possible to view and edit existing records in a Subform? And by existing records I mean those created in a previous session of the database (saved and closed, then reopened). I've already set my subform properties Continuous form, Data Entry = No, Allow Edits = Yes. I can view existing records on my main form but not on the subform.
Help!
 

A:Access - Edit Existing Records in a Subform

15 more replies
Answer Match 52.08%

Hello everyone,

Nice to meet you guys, was wondering whether you guys can help me out...kinda in a mess right now.

I got a few access tables each in different access db, each contain millions of records and I am sure each contain duplicates.

When I try to use my PC to jus find dup, either the status bar goes half way(for 24hrs) or said out of memory.
Can someone tell is the a way to delete these dup's in a fast and less resource consuming way?
Thank you so much!
ken
 

A:how to do a fast delete of dup in millions ms access records?

15 more replies
Answer Match 52.08%

I have a table called "Employees" which has the type of data you might expect. For one field called Home Phone, I originally had a input mask with parenthesis around the area code. I later decided to go with straight dashes. However, 4 of the employee records still have the parenthesis. I changed the input mask in the design view of the table, I tried deleting the phone numbers in question and re-entered them, I even deleted the entire employee record and re-entered it - with no success. I believe that the only difference between these records and the rest is that I imported the bulk of the records from an existing Excel Spreadsheet, the records in question are all newly-entered. Which must mean that even though I have changed the input mask for this field, it still is applying the parenthesis for some reason.

I really don't want to delete this table and start over. I've added a lot of fields and tweaking that I really don't want to do over. Any insight would be welcome. Thanks.
 

A:Solved: Persistant Format in Certain Access Records

Try a find and replace on the phone number field. Replace ( with nothing and then go through again and replace ) with nothing.
 

2 more replies
Answer Match 52.08%

I have a 3 PCs peer-to-peer network sharing to use a Access DB file which resides in say PC-A. Most of the time PC-B and PC-C would open file to do data entry.

PC-B never has any problem.
PC-C the person has been complaining records missing... say she puts in new records 2 days ago, now when she opens the file she cannot find those records. This case does not happen all the time, just hit & miss!
All PCs are WIN98 running Office 2000.

Ques:
1. Do I need to apply any fixes (SR1?)
2. Is there any sharing issue that I missed?

Currently all I am doing is making PC-A with a shared folder to house the Access data file.

Pls advise.

Andy
 

A:Access 2000 database records not updated

6 more replies
Answer Match 52.08%

This report works ok for all records but my boss wants to be able to select just the records that he wants and print out the report. I know in the query I can do a "[Enter Record Number]" on the "WO" field" but that only works with one record number.

Can I make the query pull up a list/table of all the jobs in this table (With the first 3 columns displayed) and check the jobs I want and then the report would just pull up the selected jobs?

This is over my head. (OBP hope your out there)

Thanks, Joe
 

A:Solved: Access, select records for a report

I got it figured out kinda. If you are following this you can comment on my other post: &#8220;Access form, clear check boxes when closing&#8221;
 

1 more replies
Answer Match 52.08%

Hello again guys

A while back I had some excellent help with a new database I've been working on. Thanks especially to OBP I've solved a few issues and the old topic can be found here:

http://forums.techguy.org/business-applications/1112787-assigning-matching-ids-one-table.html

Basically it's a compliance testing database for my company's internal processes. Once again I've uploaded a working version of the DB if anybody wants to take a look.

In short, in the initial Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).

Here are some new things I've been struggling with so far.

1) When I change the Sample Descriptions in tab #3 ("Samples") I get the following error:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.Click to expand...

It still lets me change the description, but always with the above error. I'd like to know if there's any way to suppress it, or better still, alter the underlying code so that new records are only added if the Item_ID and Sample_ID combinations do n... Read more

A:Solved: Access: Updating multiple records through VBA

16 more replies
Answer Match 52.08%

I'm running a query off of a linked table and it is duplicating 112 records. I've run the query to find them already now I want to delete them. The "Delete" key under the Records tab is inactive and therefore doesn't allow deletion. I'm assumming it's because it is running off of linked table. Is there a way around this?
 

A:Deleting Duplicate Records in Access 2007

sylnz, welcome to the Forum.
Have you tried creating a Delete query using the Duplicates query as it's data source?
 

1 more replies
Answer Match 52.08%

originally had 1 big table with 190 records. i had to split it to 2 tables with a relationship set on a specific field. i modified my form to combine the contents of both tables via my relationship. as i went back and added 10 more records, these records can be found/seen directly on my first (original) table but they were not inserted in the second table.
on my form, i can only see up to the original 190 records, and NOT the last ten records added after the table split.
is there something that i need to do to my form to go beyond 190 records?
 

A:Access Form is not showing all records in my tables

EVM welcome to the forum. You will get a quicker response to Access questions on the Business Programs Forum.
You sound a little confused over the use of the second table, it's data will not be automatically updated when the first table is updated on your Form. You need to have form, usually a Subform on your mainform to update the second table.
 

1 more replies
Answer Match 52.08%

I'm sorry, I know this is more like a Microsoft Office post but I cant get any answers when i post topics over there, and I was hoping someone here could help

Here is the link to a zip file containing my website, the database is in the database folder

My Web Site

To download it, type in the verification text, wait the 30 second delay and press the grey download button.

The problem I am having is that I cannot write any new records to the database, or edit it or delete them. I am hoping that someone could run the web site on their machine and tell me what is wrong with it. I think it may be a problem with permissions or something like that, but I have no idea how to fix it.

Thanks in advance

A:Can't Add, Delete or Edit Records in my Access Database using ASP or ASP.NET

idiot

4 more replies
Answer Match 52.08%

Hi.
I'm suffering from a bit of Friday morning madness and can't think or find help for a simple problem.

I have a report that details vehicles, and these are sometimes given a 'Decision'. I want a count at the foot of the report that counts only the number of instances of one of the specific decisions.
I tried:

=Count ([Decision]="P")

This fails because it counts all records with any decision (for which the decision field is not null).
So I tried:

=Sum([Decision]=P)

This fails because it gives me a negative number - the number is correct but that negative is wrong. So, it's giving me -30 instead of 30.

I'm sure this is sooooo simple, and I'm sure I've done it before, but my head's rattling like a charity worker's collection tin.

Thanks,

Gram
 

A:Access 97 - count a selection of records on a report

Here ya go:

Create a textbox in the detail section of the report. Set the property as not visible, and Running total=yes.

Put an iif statement, something like:

=IIf([clearance code]=2,[cash value],0)

Then, in your report footer, you put a regular sum box, but use the iif as control source.

(You can drag the iif off to the right, make it as small as you want, so it doesn't interfere with your design view.)
 

2 more replies
Answer Match 52.08%

At the end of my Query and Report I want to show a total of records returned. Can anyone tell me how to do this please. Thankyou.
 

A:Access - Query/Report records total

7 more replies
Answer Match 52.08%

What is the best way to check an Access table for duplicate records?
 

A:Solved: Access Table Duplicate Records

9 more replies
Answer Match 52.08%

I'm using Microsoft Access 2003.

I have a table with data containing information related to Customer Number, Customer Name & Product. Some customers have more than one product (so there are multiple records in the table)...example Customer: 1243 has a row for Product: A and another row for Product: C.

I want to be able to say if Customer 1243 has multiple products than only show me the record for Product C.

Note: Not all customers have multiple records

Does anyone have any ideas on how to do this?

Thanks
 

A:Solved: Duplicate Records in Access Table

Hi am1st1,

Welcome to the forums.

Firstly, you should Normalise your data. http://en.wikipedia.org/wiki/Database_normalization.

Your current data, has a relationship of Many-to-Many, a big no no, in properly normalised data - an important paradigm of database design. So you should have a table with your customers, one with your products and then a table linking them together, with the unique ID from your customer table and the unique ID from your products table.

I'm not sure how to select product C over A without doing it programmatically using VBA, as I do everything that way, however it may be possible, and someone may be able to give you the answer to that one, but you should normalise your data first.

Failing that, I could write some VBA for you, however, I need to know all the potential options, as well as the data fields, once normalised, the best thing would be to remove any sensitive data, replace it with good dummy data, and then upload the database. If you'd prefer, you can email me the database, just send me a private message, and I'll let you know where to send it.
 

2 more replies
Answer Match 51.66%

Hi all,

Iím really in need of some help from some Access experts! Iím a newbie to Access, but I got myself busy with quite a big project... Iím trying to map out the product supply to shops in the whole country. Iíve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops donít have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have ... Read more

A:Access help needed with combining tables and consolidating records

Josephine87, welcome to the forum.
1. I would add the Quantity_counted to the Reported_quantity table, (as it has the most fields). You can use a query to update the Quantity_counted with the data from the table Stock_counted. The 2 tables will need to be joined by both Shop_number & Product_code.
Is the Price the Wholesale Product price or the price of product in the shop?
2. Which has the duplicates?
3. That will probably have to be done manually.
 

1 more replies
Answer Match 51.66%

Q: Using a front-end / back-end database, is there an automated way to move records that have a field marking the record as "closed" to a second database (along with their linked table records) to store "old" information. Here is the background information on the database.

Database was originally suppossed to hold information on client contact information & file notes. However, over the last year, the boss kept requesting expanded information be available, and I would create new tables and relationships to hold billing information, doctor, scheduled fees, and insurance information.

The main client information input form is set up with the main record information and clientID field. 4 subforms (added using a Tab contol with the subforms linked via the clientID field) are also on this main form. I moved the billing (money owed / money paid and the running totals) to a seperate form completely, which is opened by a command button which opens the billing form with specific data from that clientID. I thought that this would speed things up some, but it hasn't

Things were rocking along fine, until temporary help was hired to input last years clients information in the database as well. So the database has gotten extremely large and rather slow when the main input form is opened (Once it's open, it cycles through the data fine with minimum delay). I added a field in the main table that the clerks can check to mark the client as "closed"... Read more

A:Solved: Access 2K / Moving closed records automatically?

9 more replies
Answer Match 51.66%

I'm pretty good with databases, but this one has me stumped as I can't find a solution anywhere, nor do I really know what the deal is - but I'm sure it'll be something so simple that I overlooked that I'll feel retarded.

I have an access database that has a table with the following structure:

company, address, state, zip, state, phone (all with data populated)

I then created another table named "new" and added a field called phone.

I put in XXX amount of phone numbers into the new table named "new".

I basically want to compare the records from Table 1 Phone to the Phone in "new" table, and remove them from the original table.

Everytime i do that, I get a "Recordset not updateable" error, I've tried all sorts of queries, etc. - no avail.

/squirm - help!
 

A:Solved: MS Access 2003: Cannot Compare &amp; Delete Records

10 more replies
Answer Match 51.66%

Access 2010 somehow gets into the mode where it automatically saves changes to objects.
Not to be confused with other posts where folks want to suppress auto saving records.
Create dummy query and I cannot get past having to save it.
Modify an existing query and it auto saves it.
Modify VBA and when closing the db it auto saves the code.
Docmd.setwarnings = True does not fix it.
Neither does compact and repair.
I have to create a new database and reimport all of the objects.
Database is fine for a while then this pops up again.
Very disastrous when making temp changes and cannot back them out.
Anyone know the solution to this?
 

More replies