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
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).
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.
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.
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?
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 RelatedHi 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
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 RelatedIn 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 RelatedI have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.
Ex. The red column is what I am trying to create
Account Date_To Counter
12345 06/30/2015 1
12345 05/31/2015 2
12345 04/30/2015 3
19999 06/30/2015 1
I am trying to find an algorithm to identify patterns in my data.
My task is to accomplish whether the data shows a very sharp decline and whether or not it follows previous fluctuation.
If it declines sharply and doesn't follow previous fluctuations it will indicate a production problem.
My time series data is as follows.Also sharp decline according to the below data is highlighted.
Data
-0.027663709
-0.057051957
-0.077941988
-0.070009989
-0.033860193
[code]....
I have been consistently getting an error almost every month when I try to load some data that has been input into access, then I have it linked to an excel sheet so that whenever I refresh, the access data imports into excel and updates my pivots, charts, etc.
When my data entry ppl enter data, sometime they forget to enter a code, or something, and when they forget to enter that, it creates some kind of error in the query. The query still runs in access, but shows something similar to " #ERROR#" in the field IF I ever do find it in the access query. The issue is that I cant filter to find that error. I literally have to scan and scroll through thousands of lines of data to try and find this error. When I try to refresh the data in excel, the following error message pops up;
"Data could not be retrieved from the database. Check the database server or contact your database administrator. Make Sure the external database is available, and then try the operation again."
In past months I can usually find the #ERROR# by scrolling through access and finding it. Some months I have EXTREME trouble finding the error. It can take hours out of my work day. Is there any way to more easily identify which line these errors are in rather than scrolling through thousands of lines of data? Is there a way to still export the data to excel with the errors still in them?
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!
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
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?
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
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
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 ?
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?
Hi all
I have field Name called EMPNAME
The records like this,
Brian,Smith
Ray,Vernner
Ken,Ibrahim
Now I want to separete them as Firstname: Brain
How do I find the comma "," in a text?
thanks
:confused:
I have a control (command button) on a form that opens up a report which filters out a single record. The filter is based upon a value in a text box located on the form. Within the VB code, I put some code to prevent the form from opening if the text box had no data in it.
When i click on the button (assuming the text box has a value in it), I get the error message I defined, and the report opens properly. This tells me there is an error being generated.
My question is, how I determine what error has been generated? And how do I fix it?
Here is the code I used:
//This is the code for the form.
Private Sub cmdButtonPreviewLabel_Click()
On Error GoTo Form_Closure_Error2
DoCmd.OpenReport "reportLabels", acViewPreview
Form_Closure_Error2:
MsgBox "Sorry, but I cannot print a label without a Job Number.", vbOKOnly, "Notice"
Exit Sub
//This is the code for the report
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Form_Closure_Error1
tempvalue1 = Forms![frmjoblog].[JobNumber]
Me.Filter = "[tableJobLog].[JobNumber]= " & tempvalue1
Me.FilterOn = True
Form_Closure_Error1:
Exit Sub
End Sub
End Sub
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 RelatedI 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.
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]....
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?
Hi
i have a query that takes fields from various related tables to give me the information that i want
The primary table has 199955 records
The query displays only 199939
16 recordss are missing
I know why this is: the primary table (CWT reqd equip) has a field on it that is a primary key on another table (cwt Equip). On "CWT reqd equip" 16 records must have an entry in this field that is not present on the table where the entry is a primary key (cwt Equip).
I dont know how to identify what these 16 records are on the primary table. I want to get rid of them so i can enforce referential integrity between the two tables.
can anyone help? Have i wrote this clear enough?
thanks
p.s i've attached a doc showing how the query is built. the other tables other than the two i mentioned aren't important, for this.