This is a tough one. I am developing golf management software. There is a table of caddies. And there is a table of members. The Caddies carry two bags each one for each member. They are assigned their jobs through an assignment form. Therefore, a single caddie will carry for Member1 and Member 2.
Now I need to produce a report of how many times each member plays. So how can I query the assignment table where Member 1 and Member 2 fields exist so that I can have a single field of allMembers so that I can count their rounds in a report.
I have a query that accesses data from 6 tables. The query displays 6 records where as there are 7 records that have to be displayed. The one record it doesnt display is similar to other records. I dont know why its not being displayed. Kindly help.
I have a table of ~165k records and need to delete records that are similar (2 or more records have the same lastname, firstname, dob). These records are not duplicates as other field values in these records are different. I've tried several sql scripts and Access methodologies but can't get valid results.
This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?
Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks
I have a query that shows member check-ins to a fitness facility. Customer scans their membership card and it logs their customer id, scan date/time (date and time are one field), name, etc. Sometimes the customer will scan their card multiple times if they've left and come back, but I only want to count their first scan. I have a second query that runs off of this one and counts visits by hour for a date range.
So my question is, how do I filter out subsequent scans on each day in a query and how do a I do that when running the query for multiple days? So in the multi-day query I want to count each day they came, but only the first scan of each day? How to do this in the query design grid...
I am creating a simple inventory db. I have a form that we enter into the table inventory. Right now we enter in everything once as inventory, then again to out to coating or ship to customer. I’m looking for the code to create a button enter the record twice but make two fields change.
So in short terms I want the form to enter a record with all the data as displayed. Then create another record with all the same data but the quantity would need to change to a negative and the Status would change to “Coating”.
I know this code won't work but this is what I want to do.
Hi guys, need your help again with the following problem i'm having. I'll give an example to make the problem clear. I have a table, eg table 1 with the following records: Tag________Desc________Msgno____DateTime 016GB001___Alarm OFF___1403______21/4/06 11:02:02 016GB001___Alarm OFF___1605______21/4/06 11:02:02 034GB005___Alarm ON____1403______21/4/06 11:02:04 016GB001___Alarm ON____1403______21/4/06 11:02:07 016GB001___Alarm ON____1605______21/4/06 11:02:07 048GB001___Alarm OFF___1403______21/4/06 11:05:31 048GB001___Alarm OFF___1605______21/4/06 11:05:32 048GB001___Alarm ON____1403______21/4/06 11:06:51 048GB001___Alarm ON____1605______21/4/06 11:06:52
I need a query that looks at records with similar Tag fields that occur with a 1 second difference between them and deletes those with "1403" in the Msgno field. I'm a newbie and i've got no idea where to start. The query should return the following records: I have a table, eg table 1 with the following records: Tag________Desc________Msgno____DateTime 016GB001___Alarm OFF___1605______21/4/06 11:02:02 034GB005___Alarm ON____1403______21/4/06 11:02:04 016GB001___Alarm ON____1605______21/4/06 11:02:07 048GB001___Alarm OFF___1605______21/4/06 11:05:32 048GB001___Alarm ON____1605______21/4/06 11:06:52
I hope the problem is clear. I know this is going to be one heck of a challenge, and i'm not sure whether this can actually be done in access!! Anyhelp from anybody would be enormously appreciated. Hope to see a solution soon!!
I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street 123 st 123 street job 1 123 st job 2 etc....
So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:
Private Sub ProjectName_AfterUpdate() Dim stLink, pName As String pName = Me.ProjectName stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'") If IsNull(stLink) Or stLink = "" Then
[Code] ....
I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present.
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code: LastName FirstName SSN Address Phone Email Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code: LastName FirstName SSN Address Phone Email Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
I am trying to query on a table, predominantly made up of check boxes, so that I can get a summary which essentially shows the number of 'checks' in each field within a given date range. I have attached a Word file, which should explain exactly what I would like to achieve.
If anyone can help me I would be extremely grateful.
I need a help. I have one table that has a field called "Initial ZIP CODE", another field called "Final ZIP CODE", and a field called "AREA".
I have a data base that will give me the specific "ZIP CODE", that will be between the "Initial ZIP CODE" and "Final ZIP CODE", and I want to get the "AREA".
I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.
I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.
My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.
They table layout is all the same. It will be like this:
TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....
TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.
I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.
I need to copy over subform contents, for which I use an Append Query (actually, I use its SQL in VBA code to pass it the right parameters).
Now, naturally, I ran into Key Violations, because taking 50 sub-records from the original they would have their own AutoNumber Keys (ID's) and I can't append them into the same table.
My question: how do I copy over the same records (appending FROM and TO the same table) but cause the table to insert AutoNumbers for the records being appended?
Hi - I am working on a database where i need to update a table with a "UniquePersonID"
I have a master table which stores: UniquePersonID Surname Forename DOB SEX Postcode
This table is approx 600k records, now the problem i have is that i have further tables which contain Surname, Forename, DOB, Sex and Postcode and need to resolve these with the "UniquePersonID", unfortunatley in some cases names, DOB's may have been spelt slightly differently, (i.e. "Abbas" should have been "Abbass" or the DOB has been entered with a typo)
I have managed to do an exact match and update the majority of records but i am still left with just over 16,000 records to match manually... I have created a form which allows me to tick which boxes to search against to resolve record by record, but estimates suggest that this will take around 12 solid days work :(
Is there a way of matching similar records and coming up with a probability score that anyone out there is aware of???
I have 5 tables which comes from different departments in our company. All of them have the same key "project no" but hold different information in other colums. As a starting point I have secured that they all have same no. of records meaning all "project no" are in all tables but some table might not contain other information in that record than the project no. I have put them into a quirie and on a form I am able to look at all data from all 5 tables. I have linked them one to one and it works fine untill I add a record to one of the tables. Afterwards I am not able to write into fields from other tables because there is no automatic creation of the record in the other tables. Please How would this be best/easiest to make for me. ? The reason why I have to keep the 5 tables is that regular updates are avaiable form any of them.
Appricate any comments which can make me any progress.
Hi there, I'm quite new to Access.I have two tables which have very similar Data, but laid out differently. The main similarities are things like FirstName, LastName, Company. What I need to be able to do is merge the two tables together into one super table. I've tried linking FirstName with FirstName and LastName with LastName on both tables, and running a query, so I can check for duplicate names. But all that brings up is a list like this:Steve Smith Steve AbrahamsSteve Smith Steve McDonaldSteve Smith Steve PerrySteve Smith Steve Vere(those columns would be Old FirstName, Old LastName, New FirstName, New LastName)There are fields in the old table that are not in the new, and vice versa. But the core fields are labeled the same. What's the best way to proceed here?Cheers,Hob
I have supplier records where the supplier name in each record is different. It may be Supplier ABC, AB,C, ABC!, ABC Inc., or several other variations. In addition each of these suppliers has their own supplier ID. The supplier ID can be one of 5 different versions since the data is from 5 different ERP systems. I am trying to create one table that does not have the supplier listed many times, but has one correct name and one ID that will be able to link back to the original file with all the different names and IDs. I hope this is a good explananation. Any advise?
I would like to take information from a table and combine all the like terms. For example, I have a list of items, and some appear multiple times. I would like access to group the items with the same UPC together and give me a running dollar total insted of viewing the same UPC item 3 times. Is this possible?
I'm trying to add a column to my make-table query that will give me the sum total of all rows in the source table which have a matching property.
For example all my orders have multiple lines and I'd like to have this column search the entire table for all the matching orders and then put the same total $$ amount in that column for all rows with the same order number. This is very easy to do in excel using a sumif command and I have played around with the crosstab query in access to no avail for this purpose.
If anyone has some advice to point me in the right direction, I'd appreciate it.
I have 3 tables, with the same field in each of the three tables. I need to find numbers (within those fields) that are similar in all 3 tables. If a number appears in all 3 tables, then all the data for that number need to be pulled from each table and placed in one row, all corresponding to that one number.
I can get this to work for 2 tables, using a query, but not three.
How would I go about joining tables with similar fields? I currently have three tables that show standings of teams (East, Central, and West). They all have the same fields:
East Team Name Wins Losses Ties Points For Points Against
Central Team Name Wins Losses Ties Points For Points Against
West Team Name Wins Losses Ties Points For Points Against
How would I combine them in a query so that all of the data from the tables are compiled into one table:
Overall Standings (East/Central/West) Team Name Wins Losses Ties Points For Points Against
I work for a voluntary organisation in a hospital environment. Older patients coming into A&E will get sent a letter from us offering help.
We send out about 3,000 letters a year and have just over this number of records on our simple Access 97 database.
The problem I have is when we enter data using a Form in the field Address Line 1 if we have sent a letter to this address before I want somehow for the program to inform me of this. However, I do not want it to stop me from entering the same address again so I can't use the No Duplicates option. The reason being, we may have sent the patient a letter six months ago in which case we would want him to receive another but if we only sent him a letter say 3 weeks ago then we wouldn't send him another. Alternatively, his wife may now be a patient and will get sent a letter.
At present we look at the admissions list, sort our table into Surname order and check if they have been sent one previously, however this is very time consuming and would value anyone's thoughts.
Unfortuantely, I can't program so tend to use the design features and wizards of Access 97
Does this makes sense?
I appreciate the time any of you may take in answering this Thread. As you can see I am a newbie to this. Thank you in advance.
