Access :: Deleting Similar Records Within 1 Second Time Frame!!

Display Remaining Units Within Time Frame

Jan 4, 2013

I have a database being used to track and bill therapy units. I have a table that stores the pre-authorizations that tells me within a [start date] and [end date] I am limited with X amount of units (that is all the client's insurance and/or funding source will pay for within the time period). I also have about 4 different codes I can be authorized for which is also stored in this table - each code a separate pre-auth record.

Another table is where I enter in the type of therapy (service code) I've completed with the client. I put in the Service date, start time and end time and it will automatically give me the amount of units to bill (1 hour=1 unit). If I happen to choose a code or enter in a service date that does not match up with a client's pre-auth (Service date is between start date and end date and matches code from pre-auth table) I have a simple text box that says "No auth for this code or service date" due to an IIF statement in a query. I'd like to take this a step further and give me the remaining units for the pre-authorization left when it does find a match.

What is happening is when it finds the authorization, it's not taking into consideration all the services I've done for that time frame. For example: I have a pre-auth from 12/1/12 to 1/31/13 for code 90806 for 12 units. I served the client for that code a total of 10 units thus far. I can't figure out how to link my 4 service records dated 12/3/12 (2 units), 12/16/12 (4 units), 12/27/12 (2 units), and 1/3/13 (2 units) to add together and then subtract from my auth for 12 units. I know the dates are the key but I'm lost.

View 3 Replies View Related

Queries :: Show User ID When There Are No Errors During Defined Time Frame

Apr 16, 2014

I have an error database that I'm attempting to build. I need the results of the query to show User ID when there are no errors during a defined time frame.

View 9 Replies View Related

Queries :: Aggregate Sales Totals Within Specific Time-frame?

Mar 23, 2013

stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.

Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]

[URL]

As you can see, the PRODUCT_ID column is not combining together according to their IDs.

[URL]

This how my Design View looks.

The SQL for my current query is:

SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));

View 3 Replies View Related

Similar Records In The Same Query?

Oct 28, 2005

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.

Thanks.....Jeff

View 2 Replies View Related

Delete Similar Records

Jul 31, 2006

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.

Thanks,
Phil....

View 1 Replies View Related

Deleting All Related Sub Records When Deleting The "main" Record.

Apr 12, 2008

I have two tables.

1. Persons (list of persons)
2. Job history (list of jobs)

each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.

how can i do this? vba or simple properties options?

- Roy

View 1 Replies View Related

Query Accesses Certain Records But Not Others Which Are Similar To These

Nov 13, 2006

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.

View 2 Replies View Related

Unique Values On Similar But Different Records

Jan 31, 2008

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

View 6 Replies View Related

Queries :: Filtering Out Similar Records

Jun 20, 2013

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...

View 2 Replies View Related

Forms :: Make Two Records That Are Similar?

Mar 3, 2015

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.

Private Sub Command78_Click()
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31

[code].....

View 9 Replies View Related

Modules & VBA :: Database With Records - Search For Similar String

May 20, 2014

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.

View 14 Replies View Related

Linking To Similar Records From Same Table When Creating New Record - Call Log

Apr 21, 2014

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.

View 8 Replies View Related

Tables :: Merge Duplicate / Similar Records Into 1 Record In Access 2010

Jun 9, 2014

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

View 2 Replies View Related

Deleting Records

Aug 4, 2005

I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?

I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.

View 3 Replies View Related

Deleting Records

Nov 11, 2005

A strange request but I hope someone can help with this one

I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number

e.g On a form text box I enter the number or records to be deleted (e.g.6000).

The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:

View 1 Replies View Related

Deleting Records

Feb 1, 2006

How can I go about deleting records fully? So that there autonumber, etc can be used again without access moaning.

Thanks :D

View 1 Replies View Related

Deleting Records

Mar 14, 2006

I have a query (Query1) which returns results In one field recordID.

How do I delete records in a table where the RecordID is in the list Query1.recordID?

Thanks

View 4 Replies View Related

Deleting Records

Jan 25, 2008

Hi,

how would i go about deleting a set of records? i can get a list of records together in a query taken from 4 tables and would need to, if necessary, delete a single line. not all information needs to be deleted from all 4 tables though? the info to be deleted would only be deleted from 1 or 2 tables being the last 2 in the relationship.

i guessed it might be an append query but im not too familiar with them.

any chance of a possible suggestion?

many thanks,

NS

View 2 Replies View Related

Deleting Records

Oct 17, 2007

I am having trouble with my autonumbers when i have to delete a record.

Can anyone help me out with auto numbers?

View 9 Replies View Related

Deleting Certain Records Using SQL

Dec 14, 2006

Hi there, i have a master reset to delete all the data in the database. Although, as there is a username and password entry to get into the admin module, i wish for one entry in the table participantTable to not get deleted (to save one password/username so its possible to log into the admin module after the reset). The code below will delete everything, how can i change it so it keeps the first record in the table, and then deletes all the other records after.

DoCmd.RunSQL "DELETE * from participantTable"

Thanks in advance

View 1 Replies View Related

Deleting Records And Autonumber ??

Jan 25, 2006

quick question guys...

if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted..

example

sales order 1001
sales order 1002

if i delete all the records on the table and clean it all out

the next new autonumber will be 1003...

i want it to be 1001 again...
and yes its set to an index of no dups

View 1 Replies View Related

Deleting Records And A One To One Relationship.

Mar 19, 2007

Hello all,

I have 3 tables:
1. customer
2. DVD collection
3. Borrow

The aim of this system is to create a home borrowing system.

The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises.

I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table.

I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help??

Thanks

EDIT: the button i used was from the wizard that comes up when you create a button.

View 2 Replies View Related

Deleting Records - Message Box

Nov 24, 2004

Looking for hint how to avoid systems message box ("you are about to delete n records...yes, no") when deleting records from the table.
respect

View 3 Replies View Related

Db Deleting Records From A Table

May 18, 2005

Hello.

I am using a large database, which usually works fine, and is set to compact on close.

Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before)

Does anyone have any ideas as to what is causing this?

Thanks.

View 14 Replies View Related

Deleting Records And Autonumber ??

Jan 25, 2006

quick question guys...

if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted..

example

sales order 1001
sales order 1002

if i delete all the records on the table and clean it all out

the next new autonumber will be 1003...

i want it to be 1001 again...
and yes its set to an index of no dups

View 1 Replies View Related