I have a simple Query which very satisfactorily exports data to a Tab Delimited Text File to upload to a website.
The field titles are dictated by the Table Field Names but I would like to modify these for the export. Is there a simple criteria code by which this can be achieved.
Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief:
tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff
tableSignatories has ID for a primary key and has SignatoryTypeID as a field
tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
Any thoughts?
I have been successfully using the following statement in Access 2010 to retrieve data from a large csv relational database:
SELECT [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
FROM 1995_1 INNER JOIN 1995_2 ON [1995_1].RPT_REC_NUM = [1995_2].RPT_REC_NUM
GROUP BY [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
HAVING ((([1995_2].WKSHT_CD)="A000000") AND (Not ([1995_2].LINE_NUM)="09500") AND (([1995_2].CLMN_NUM)="0100" Or ([1995_2].CLMN_NUM)="0200"))
ORDER BY [1995_1].RPT_REC_NUM, [1995_2].LINE_NUM;
This query returns one long column of line numbers (LINE_NUM) representing the itemized salary (CLMN 0100) and non-salary expenses (CLMN 0200) and a total for each of the organizations represented in the data base. My question is: is it possible to modify the query so that it returns only organizations whose data passes that following test that checks to see if the itemized line numbers 00100 to 10099 equal the total for each organizations line 10100: SUM(LINE_NUM 00100:10099)=SUM(LINE_NUM 10100:10100).
I have tried several times to upload a sample file but have been prevented from doing so because of a missing security token. I have communicated this to the administrator.
I have a database with two tables. One for customers, one for the books that they buy.
I have a field for people's titles (Mr, Mrs, Miss, Ms) and I'd like to make a graph in Excell to show the relationship between gender and amount spent on books.
I'd like to just have two fields, male and female to put into Excell so the graph makes more sense than all four titles and then an explanation that Mrs, Miss and Ms must be added together to compare the total spent with the males.
How can I do this?
Where in Access 2010 I can modify the displayed fields of a Combo Box on a form. I need to format the display window and add criteria to one of the fields. Can't remember where I set this up and recently upgrading from Access 2007 isn't working.
View 5 Replies View Relatedautomatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
View 8 Replies View RelatedHi,
I create one database and the query and the forms for my clients in the sharing drive. Every time, the user open the form and select the field names and then execute the query depend on which fields the user select on the form, and then output to the screen. Here is the question, if there are multi user access the same form/query, one user pickup 3 fields to run the query, and another user pickup 5 fields to run the query. Becuase one user select 3 field names on the form, and then the code will modify the query contents, another user select the 6 field names on the form, and then the code will modify the query contents.
So, will the query affect each other?
Dim strSQL As String
strSQL = "SELECT [Company Name],[Vendor Name],[Account],[Statement Number],[Amount] from [Statements] "
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.view
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("Statement Informations").Command
cmd.CommandText = strSQL
Set cat.Views("Statement Informations").Command = cmd
Set cat = Nothing
DoCmd.OpenQuery "Statement Informations"
I used the above code to modify the existing query, "Statement Informations".
When I run it, it give out the error message:
Run-time error '3218':
Could not update; currently locked.
And then, I went to queries section, and open the query "Statement Informations" directly, close the query, and then run the code again.
Then, it works.
I don't know why.
If I run it on next machine on other day, then it give out same error.
How can I solve it?
Please let me know, thanks.
Hi all,
Is it possible to modify data using a query.
Say for instance I have a whole load of IP's in a table in the form of A.B.C.*, where and and B are fixed, C varies slightly and * can be anything from 1-255. Can I use a query to look at an IP and the append an IP record to a different table in the form of A.B.C.0. Basically, modifying the data so that whatever the value of *, it is changed to 0.
Cheets,
Matt
Hi all
I have a report rptTeamPickStats which source is a query qryTeamPickStats
The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.
I want to be able to modify the where using VB so I don't have to have several queries.
The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.
I want to query all the TLName where the date is between to dates.
DoCmd.OpenReport myReport, acViewPreview, "", _
"[TLName]='" & myFilter & "' And qryTeamPickStats!Date >= #" & myFromDate & "# And qryTeamPickStats!Date <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"
Hope this makes sense
Cheers
Kev.
Hi!
It has happen to me many times that when I create specific queries I'm then not able to modify the values of em...
Do you know the possible causes of this?
Is it because I'm filtering? Ordering by? Grouping? Using fields of differents tables related 1-many? Calculated fields? Expressions? Modules?
----------
One specific case that has presented to me is a query that returns info from 1(main)-many(details) relationship. When I use the sorting of a field of the main table it doesn't allow me to edit values, tho when I delete this sorting it works fine.
Nevertheless, I also have another query which just returns values from one table and whether I use sorting or not, I can modify the values.
What's happening??? :( :confused:
hi im a first time user of access,
can anyone modify this query statement...
select IDno, LName + ', ' + FName as Name, course, Status,
(select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno
)
as TotalBooks
,
(select sum(io.Penalty) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno) as Penalties from borrowers b
i also want to set the TotalBooks and Penalties to 0 when their values are null thanks
Is it possible to run a query/filter in order to hide/show a field in a record instead of it affecting the entire record.
What I'm trying to do is, in a form the user can enter information about a service call and they can enter a recommendation for the next visit. When they enter a recommendation they are able to select which month the recommendatin should pop up. This Service report get's printed on a monthly basis as a report. Now the recommendation field should only pop up if that particular month which was selected earlier is true. I've tried different methods, but it would affect the entire record as opposed to just one field.
Thanks.
i am very rookie with access.. infact i am doing my first project in access for a friend.. here is my doubt...
this frnd has toy making business.. he wants to keep track of this inventory... i have made one table (ItemId(PK), Item descripstion, noOff, parentItem(FK), stock)..
noOff is the no of child items required for a particular parent item..
now i have to run a query to get all the items which have same parentItem.. for this i designed a form... have put 3 text boxes and a subform.. one to accept the parentItem.. 2nd to display its description.. 3rd one to accept no of parent items which i wud be the multiplying factor for the child item noOff.. (default is one..)
http://img.photobucket.com/albums/v355/sledge045/171205/form_prd_data.jpg
now i can retrive the parentItem id.. from the form and use it in the WHERE clause...
SELECT ItemData.ItemID, ItemData.ItemDescription, ItemData.noOff
FROM ItemData
WHERE (((ItemData.ParentItem)=[Forms]![Display Product Data]![txtProdId]));
but i want to retive the value in the No off: box and multiply it to all the child item's no off...
i have tried to multiply ItemData.noOff with [Forms]![Display Product Data]![txtNoOff] but did not got the desired results....
i need help with this... or if u know ne other way to do it... plese post a reply... thanks...
Is there any way to modify the progress bar so instead of saying "running query" it states the name of the query?
Like: Running Query [qryFRED] <progress bar>
I created a db and made it back end. I added the another db and linked some tables into it. Everything works fine except that the title and department are showing up as the id instead of name in my employee details form. It is really confusing because it seems as if it is reading the info from the employees extended which shows the names but then puts in the id's.
I believe that location will do the same thing but it hasn't been added to the details form yet. I am sure whatever is the problem with the other ones will also resolve that one.
I have attached the be and the fe : Test.zip test_be.zip
How can I get a report to have the title of a field in my form? I have a command button which when pressed brings up a report revelent only to the record the form is currently viewing. At the monent the title reads "Current Method" but I would much prefer it to read tha title that Method has on the form.
Any Ideas?
How can I get a report to have the title of a field in my form? I have a command button which when pressed brings up a report revelent only to the record the form is currently viewing. At the monent the title reads "Current Method" but I would much prefer it to read tha title that Method has on the form.
Any Ideas?
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
[code]...
I have many menus snd sub-menus in an application. I see that each switchboard has a title. How do I display that title with the switchboard?
Robert
Hello everyone,
i am trying to make a small database for my uncle, but i am stuck in the report. can someone help me out and tell me how to hide the title, the value and a label only when the field is empty...
i have attached the file here plz look at it and help me out...
see the report i have made.. i want the whole line to be hidden when the value (1,2,....,10) is null.
plzzzzzz help me...!!!
Hi,
I'm new to this forum so forgive me if this thread is in the wrong section.
I'm currently creating an OHS&W (Occupational Health Safety & Welfare) database. My boss has just requested if I can possibly have it so that when a Employee has a job title selected for them it automatically lists the courses that are required for that job.
Can anyone help me please?
Cheers
I have a form that includes a field for the operating system of the client. I want this to be populated from a look-up table. I want the combo box to display the text name of the OS but enter the numerical ID number to the client table. How do I do this?
View 4 Replies View RelatedHi,
I have two tables of titles (DVDs and CDs). Each table has a price associated with each title in an adjacent column. I would like to match the titles between them and compare prices. Any help would be greatly appreciated. Thanks. EDS
I would like to create some custom icons and use them in Access. Does anybody have an experience doing this that can start me down the right path? Thanks:)
View 1 Replies View RelatedHi there
I have a listbox and all is well - the only problem is that the column heads are a little 'ugly' - my column names are usually something like 'catagoryName', 'productType' and 'price' instead of looking pretty, like 'Catagory Name' and 'Product Type' and 'Price' respectively.
Is there any way to edit/format the title/column heads without having to change all the tables and queries etc, so they look a littler prettier?
Many thanks
Edd