Query Lists Repeat Data

Nov 22, 2005

Hi, all... welcome back to the new Access World Forums... glad we're still operational, even if some data was lost. It's the minds, anyway, not the data...

I have a query that pulls from 3 transaction tables.

tblTransactions - lists purchaser, amt tendered, other details
tblPurchased_Items - lists products and services paid for
tblTherapy_Treatments - lists treatments paid for

(Note: these are not separate lists of available items. They're two subforms' tables, one that deals with insurance and one that doesn't.)

I can pull this data fine, but in a case where someone had 1 treatment, but purchased 2 products, the query lists the treatment twice. I do have unique IDs for each table's items, but am not sure how to "list" each transaction once, but each thing paid for once, also.

For example:
Joe Blow comes in and has physical therapy and buys 2 books.

My query will show (bogus fields for illustration purposes):
TransactionID Name TherapyID TherapyCost ProductID ProductCost
33 Joe Blow 10 180.00 15 12.00
33 Joe Blow 10 180.00 17 19.00

So, if I total TherapyCost it really gives me twice the correct amount.

Can someone point me to a solution?
Pardon my rank ignorance, yet again.

View Replies


ADVERTISEMENT

Tables :: Populate Table With Repeat Then Increment Then Repeat

Aug 7, 2013

Beginner with Access 2010 - making additions to one field in a JunctionTable. Need to extend a field by adding some new numbers but have found it easier to Export to Excel - make changes and replace in accdb.

I have found an Excel formula that does what I want but starts from the number "1".

=INT((ROW()-1)/9)+1

Which starts with number "1" and repeats this 8 times then increments by one and repeats 8 times.

But I want to start with a number of choice (7270 and go to 9028). Have seen some VBA versions but none give me control of the start number. I am then able to update the second empty field on the JunctionTable from a query. (this is a fixed number).

View 9 Replies View Related

Repeat Queries Of The Same Data

Jul 6, 2005

I read a lot for work and the material is from many different disciplines. To keep track of all this information, I set up a simple db to keep track of all my references and have fields containing various bits of information for each record. In the beginning, I assigned one keyword to each reference as a means of retrieving articles on related subjects. Over time it has grown to three keywords per record although some of the keyword fields are null. All keywords are in a single, separate table and I add a keyword to a record from a pick-list. When I increased the number of keyword fields to three in tblCatalog, I just added more links to tblKeyword. Now, I am having a tough time trying to query tblCatalog. Obviously, my basic db organization is not right so I am looking for suggestions on how to straighten out this mess. Here is what I would like to do:

* During data entry, select form one to three keywords for each record
* During data retrieval, concatenate all the keywords (one, two or three) and
list the resulting text in a single control on a form.

This seems like it ought to be easy but not today. There must be something fundamental that I do not understand.

View 2 Replies View Related

How To Identify The Repeat Data?

Aug 28, 2005

Hi there,

I need to sort out the repeat customer, e.g. I have a list of customer's name, like Nike, Emerson, Alcan, etc... and if they sign another contract with my company again this year, I will name it like Nike 05, Emerson 05, etc...

So how can I get the result of how many customers have signed the 2nd contract with us. I have no clue now... :confused:

Anyone can give me some hint please! :)

Nadine

View 1 Replies View Related

[Continuous Form]Don't Repeat Data In Every Recird

Sep 8, 2005

I have a continuous form based on a query.
I''ve added a combo box unbound, and the problem is that any data I enter in any cb are repeated in every record.
Any ideas how to avoid that?

View 2 Replies View Related

General :: How To Repeat And Save Recurring Data

Jul 8, 2012

I need to design a simple clinical database that will allow me to collect and display recurring data. Example. Every year a patient comes into clinic for follow-up. They complete a fixed data set questionnaire, their vital statistics are recorded and any new clinical results recorded. I have created all the tables/Forms but i don't know how to automatically add a new event that repeats the same data. So far i have 5 excessive years worth of follow-up data. Sadly, I have had to make massive tables by copying the identical data with either a 1,2,3,,, to signify a different year.

View 6 Replies View Related

Data Thelp With Copy And Repeat O Next Point In A Field

Oct 21, 2005

Hi there

I am new to Microsoft access and I am currently exported an advanced revelation database in CSV format to access now because advanced revelation is a multivalue pick database I needed to create a the key for the multivalue fields which I have separated the data which is NI.

Now all the data is separated in order but the NI field only shows once then its blank going down till the next NI which is the next record this is due to to the multivalue fields data that has been separated.

How can I get access to copy the NI number down into the blanks slots till it reaches the next slot and then copy that one and so on in a loop once this is done I can use this as a key field and separate the data further..

Regards

Norman

View 2 Replies View Related

Forms :: Updating Old Database - Repeat Data From A Given Date

Feb 20, 2015

I am currently updating an old Db which rosters staff. One of the enhancements will allow the user to automatically allocate staff details to a Roster after a given date. i.e. Roster date 20th Feb 15, repeat for 3 days. 21st 22nd 23rd Feb 15. I have created a form with a medium date field and then a Combo box with drop down values of 1 thru 7 days. I want to link the combo box value to the Date box.

View 3 Replies View Related

Repeat Last Entered Value In Data Table In Access 2002

Apr 11, 2012

In my office we have used Access 2002 for many years. We enter data directly into a table. Many values are repeated row after row, with only slight changes in a few fields. I am looking for a way to automatically have the previous value in a field entered into the current row, and allow this value to be modified if it has changed, but then become the new "default" value for following rows. I have searched the web and found solutions when using forms, but nothing for entering data directly into a table. I have also seen the suggestion for Ctrl + ', this works but I would rather it be done automatically.

View 3 Replies View Related

How To Repeat Query?

Apr 10, 2006

Hello,
I have a DB table that only has 12 rows that is continuosly edited throught out the day. What I need to do is log all changes made to the 12 rows throught out the day. I have built two queries (a without match and an append query) which compair the source table with the log table where I store the differences. This works fine but I have to manually trigger the query. I would like to run the query about every 3-4 minutes. Is this possible or is there a better way?

View 3 Replies View Related

How To Repeat Previous Value With Macro Or Query ?

Sep 23, 2007

i am having a table like this :

Phone No......Bill Date.......Amount
_____________________________
222000........12-02-07.......5689/-
Null value.....12-04-07.......2356/-
223223........12-03-07........568/-
Null value.....12-05-07.......1256/-
224224........12-01-07.........568/-
225225........12-07-07.........125/-
Now my requirement is like this:-
''if any field contains null value then repeat the previous value otherwise keep the original value''. Could anyone solve this problem either by query or macro for access 2003?

View 1 Replies View Related

Forms :: Append Query Is Able To Repeat Entry

Jul 3, 2014

I am running an Append Query [qry_AppendToWeatherFromGP] to update some comboboxes as part of an import function.My query is:

Code:
INSERT INTO tbl_Weather ( Weather )
SELECT tbl_DetectionImports.Weather
FROM tbl_DetectionImports;

The function is:

Code:
Private Sub btn_SaveImport_Click()
On Error GoTo btn_SaveImport_Click_Err
DoCmd.SetWarnings False

[code]....

View 4 Replies View Related

Repeat Macro Until Record Count Of Query Equals To 0

Aug 9, 2012

I have a macro that needs to run repeatedly until the number of records in a query =0 but I can't seem to get the Repeat Expression set properly.

My query is called sqMatchCount:

SELECT Count(sqCompare_Parts_Matched_1st.BRP_Entry_Num) AS MatchCount
FROM sqCompare_Parts_Matched_1st;

The theory is if I don't have any more matches then I do not want the macro to run any more. In other words, MatchCount will = 0 when there are no more matches.

I have tried a variety of syntax using DCount in the Repeat Expression:

=DCount("*","sqMatchCount") ...... Macro runs in a continuous loop
DCount("*","sqMatchCount")=0 ...... Macro does not run (although I thought a couple times it actually DID run with this syntax)
DCount("MatchCount","sqMatchCount")=0 ...... Macro does not run
=DCount("MatchCount","sqMatchCount")=0 ....... Macro does not run
=DCount("MatchCount","sqMatchCount") ....... Macro runs in a continuous loop

What IS the proper way to write this Repeat Expression for the RunMacro Action if I want it to run as long as sqMatchCount.MatchCount>0?

View 14 Replies View Related

Modules & VBA :: Automatically Repeat Last Record Under Certain Conditions In A Table / Query

Jul 15, 2014

I’d like to repeat the last not = 0 record under certain conditions, in a table or in a query.What I have:

Calendar_All Dates
Calendar dateMyValue
7/6/20140.00
7/7/2014108.94
7/8/2014107.71

[code]...

Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table).Then repeat the last not = 0 value of MyValue. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.

View 14 Replies View Related

Convert List In Query, Like IIF But With Two Lists...

Apr 14, 2006

Hi,

I need to output data in a specific format for a certain client..
for example where

Select * from Falls - would normally give me:

01/01/05 Joe Smith ICU, Assisted

I need in this intance to get:

01/01/05 Joe Smith 5, 2

where locations, or types etc are converted to a corresponding number.

I can imagine a query like:

Select FallDate, PatientName, somefunction([Location],(MS2,ED,OB,CAFE,ICU),(1,2,3,4,5)), somefunction([Fall_Type],(Accidental,Assisted,Unanticipated),(1,2,3))

Is there some more powerful version of IIF or way to write this in the query without having to create a report with a bunch of functions?

View 5 Replies View Related

Queries :: Getting Two Lists In One Query For A Report

Dec 18, 2013

I need to get a query back that may have more than the given number of records on either side, but need to not duplicate in either of the two columns. Works better to show what I need, I think...

Query needs to return: PersID.....Name.....Assist.....Device

Base data looks like this (after using query with Person and AssistType and Device tables):

Assistance:
1.....Adams...Dress
1.....Adams...Bath
2.....Jones....Bath
3.....Case.....Dress
3.....Case.....Bath
3.....Case.....Feed

Devices:
1.....Adams...Walker
1.....Adams...Lift Chair
1.....Adams...Wheelchair
3.....Case.....Wheelchair
4.....Doe.......Walker
4.....Doe.......Cane

What I've been asked for is something that looks like this:

1.....Adams.....Dress....Walker
1.....Adams.....Bath.....Lift Chair
1.....Adams.....-----....Wheelchair.......... ===> Null or blank in the empty column is OK; I can work with those
2.....Jones.....Bath......-----.............................................
3.....Case.....Dress......Wheelchair
3.....Case.....Bath......-----
3.....Case.....Feed......-----
4.....Doe......-----.....Walker
4.....Doe......-----.....Cane

Regardless of join type, I get variants on:

1.....Adams.....Dress.....Walker
1.....Adams.....Dress.....Lift Chair
1.....Adams.....Dress.....Wheelchair..........
1.....Adams.....Bath.....Walker
1.....Adams.....Bath.....Lift Chair
1.....Adams.....Bath.....Wheelchair..........

I can print it with two subreports (one for each column), but that is running slower than I would like. It's workable if we absolutely have to do it that way, but I'd rather see if I can get it out in one query and use the grouping functions in the report (there are some other fields involved, but they don't really affect what I am doing here)

Is there any way to do a query (even a couple of queries if it takes that to set it up) that will give me what I am looking for?

View 7 Replies View Related

Queries :: VBA And Query Criteria From Multi-Value Lists

Jul 29, 2015

I have been working with Access for some time managing some databases for my company. But now I have some problems making the database easier to use by automatically creating a Query based on different criterias.The code looks like this:

Sub Multi_value()
'Definitions
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb

[code]....

The problem is that the "Business Field.Value" is inserted into the Query-criteria with brackets which for some reason are not supposed to be there when I use the .Value-parameter instead of the "Business Field"-parameter.

View 8 Replies View Related

How Do I Repeat A Process For Many Db's?

Jan 6, 2006

I have about 125 .mdb's I want to go through, and in each one, select a table and run a "find duplicates" query, creating a table of duplicate record info for research into why there are duplicates.. I am aware I can manually do this within each .mdb, but would be happy to be able to automate the process.

I wasn't sure what to search for, which is the reason for my posting. If anyone has an idea (and not questions on why I want to do this, or probs. I might encounter), I would be most appreciative.

Thanks!

View 4 Replies View Related

Repeat Information

May 25, 2007

I am implementing a quoting system at work. Many times, a customer wants the same job that he asked for last year or 2 years ago, so basically it's the same information. So when the customer calls, I look for his last quote, and then would add a button that would create a new quote (record) but with the same information. It would be a burden to enter all the same information each time the customer calls for the same job over and over again.

Thank you

View 4 Replies View Related

Repeat Last Entry

Aug 2, 2005

Hi,

I would like to know what code I need to populate a field with the last record information - similar to the Ctrl apostophe.

I can upload the database in question if required.

Thank you

View 11 Replies View Related

Repeat Last Record

Jan 17, 2008

I'm using Access 2003 to input patient records. I did not set up the database and have limited knowlege of the workings of Access.

My problem:

Normally to repeat a field from the previous record I would use the c>' keys in a blank field and it would be filled with the info that I typed on the preivious record.

However all of a sudden it does not repeat the privious field but one from several years ago.

Can anyone tell me how to turn this setting on.

C

View 4 Replies View Related

General :: Repeat Same Action But For Different Row?

Mar 27, 2013

I have an Access Form called Database_Form, with a text box called Text22, and a button called Command42.

I also have a table, and query, as following:

Table 1, as in the below example:

Row_Number as Column header, and values 1, 2, 3, 4, 5, as rows.

Column1 as Column header, and values 0026007101, 0026007102, 0026007103, 0026007104, 0026007105, as rows.

Query 2, which has the criteria [Forms]![Database_Form]![Text22] for Row_Number Field.

Basically, i want that each time when i press the button Command42 and having the text box filled with value 1 (for row number 1), to repeat this step, but taking the next row_number and so on, until it finishes the last row.

The Query 2, should sequentially select the row number given in the text box, based on a VBA Code or something.

Is there any VBA code in order to achieve this ?

View 3 Replies View Related

Forms :: How To Repeat Fields Entered In A Subform

Dec 16, 2013

I have a form (based on ArtistSong) and a subform tied to the ArtistSongID. This subform is a set of 11 variable attributes of that Artist & Song combination. Also, there can be multiple records in that subform, all associated with the Artist & Song.The user would like, when he makes an initial entry of 11 attributes, to go ahead and repeat that particular combination in the next New Record.

View 3 Replies View Related

Queries :: Preventing Repeat Values In Only One Field

Apr 7, 2013

I have a query which generates a full list of times and appointments for a given day from two separate dates using a right join and enters a "-" in the field for unallocated time slots using an ISNULL function.

I want to refine the query further so that if a single task/appointment is block-booked in multiple adjacent time slots, I only want to return the start time of the first slot, the end time of the last slot and display the task name once.

The intention is that this will be used to create a daily timetable list in an Access (2000) report with the date being specified in a Combo box in a form in which it will be embedded - though I'm not concerned about the form/report design at the moment, only how to do the query.

I've tried several variations of SELECT DISTINCT and using nested queries to no avail.

View 1 Replies View Related

Reports :: Repeat Header On Every Page Of Report?

Aug 1, 2014

Any way to repeat the Report Header on every page?

I had my header in the Page Header so that it would repeat on every page, but it did not have the CanShrink option so I put it in the Report Header so it shrinks when I want it to, but now it doesn't repeat. I was hoping there was some kind of code I could implement that would let me repeat the Report Header on every page.

Or is there a way that I could get the CanShrink property in the Page Header?

View 6 Replies View Related

Reports :: Repeat Page Header On Multiple Columns

Aug 24, 2013

I have a report, a phone list, which has two columns, see the attached file for a screen shot.

The Employee Name, Mobile Phone and Desk Phone text is in the "Page Header". The Data fields that retrieve from the table are in the "Detail" section. The whole report is 8.89cm wide. The two columns fit perfectly across a portrait A4 sheet.

When I run the report to Print Preview, I get the two columns fine, but the Employee Name, Mobile Phone and Desk Phone text doesn't repeat at the top of the second column. Is there some way of achieving this? I want the part I have circled in RED to appear over the top of the second column as well.

I would have thought there would be a simple check box somehwere that would be something like : "Repeat Page Header on multiple columns", or something as equally useful. Strange that this is so difficult to do, as am sure that this sort of thing is something that lots of people would want to do?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved