I have a text file that I will be importing into a new table once a week (every week) that I will need to add about 30 more columns to before I export it to a new text file in preparation for importing it into another Access application.
Is there a way through a query to add the columns without having to open the table and manually add them each week?
Thanks,
David
anyone know how to do a query update from on field in one table to another field in another table.
table 1
cno serno
1 123
2 345
table 2
cno serno host
123 elmer
345 fudd
i want the cno in table 2 updated where ser no matches.
anyone? (using query area in access).
Hi!
I'm working with an Access database with chemical data. The different parameters are in one coulmn each. Some stations don't measure all parameters. I want to create a recordset with all data from a station within a time interval. I will then display the data in a html table, and would like to avoid empty columns.
Is it possible to create a recordset, which only consists of columns with data, with some sort of filter? How do I filter on the column level, and not on the row level?
Martin
Ok, I'd like to appologise first as I wasn't sure that this question/problem would fit appropriately into the "Forms" forum, and also for the length of this post.
In the database I'm currently building, I have a form that is used for adding new product purchases for a given customer. So far, after much googleing and and head racking, I've managed to get everything in working order, save this one problem I can't solve.
The form uses the following SQL to populate its field list;
SELECT Products.*, [Expense Codes].[Montant de Paquet] FROM [Expense Codes] INNER JOIN Products ON [Expense Codes].[Expense Code ID]=Products.[Expense Code ID] WHERE (((Products.[Commencez Date])>=Date()));
Sorry for the french text, this is for use by a French company
On the form I have a combo box that lists the product packages available (combo 1 for reference), Prod 1, Prod 2 and Prod 3. This is a field in the products table which is linked to a seperate table called Expence Codes. This table holds my master list of product codes, and their off the shelf price in a field called 'price'.
By using the above SQL, I have the price field ("field 1" for reference) on my form auto update when ever the value combo 1 changes. This is working as I would like it to.
Now for the issue I'm having. I would like to take the value from field 1 to be copied to a field in my payments table, this is to facilitate a final billing value being correctly calculated and invoice being created. However, I'm at a loss as to how to acomplish this.
I have thought of using an update query to add this information to the payments table, as the relivent record will already be created, but Im not sure if this is the right way to go. So any ideas on what I'm doing wrong, could do better or need to do are more than welcome.
Thanks for reading this far and I await any replies :)
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
Hello,
I have just created a query so that I may base my report on it. The query ties together 4 tables. I only included the fields that will be in my report. I created the report using the wizard and used my new query as the source but it does not want to work. I tried opening the query and it was empty even though I have entries in the database.
ANY IDEAS :confused:
Hello, I have writen a piece of code:
Code:DoCmd.RunSQL "SELECT tblTrades.tradeID, tblTrades.TradeConfirm, tblTrades.Laurent FROM tblTrades WHERE (((tblTrades.TradeConfirm)=No) AND ((tblTrades.Laurent)=Yes));"
How do I check to see if it returns any results?
i have a form which has as button that opens a query
how can i make it so that if there arent any results from the query then a error message will pop up saying "no results found" and the table dosent show?
Hi...I have the following requirement:
I have a table called "tblselectrso" with the following fields:
stockno rso1 rso2 rso3 rso4 rso5
s1 1 3
s2 2 4 5
stockno is a text field and rso1 through rso5 is a Number field.
Given the value of "stockno" I want to run a query to list all the other fields (rso1 through rso5) that are NON-EMPTY.
Is this possible at all? Can anyone help me please?
Thanks
Please find attached a sample db.
I have a Query pulling data from several tables. If there are empty cell with no data in them, they cause the Query to completely skip that record. It does not show up in the Query. Not practical since most of the data int the table will not be entered until some time later.
What gives? Is there a way around this?
Thanks.
Query not responding: empty fields?
I have a table with a field 'Fax number', type: text (since occasionally we write a comment in there, like 'prohibited').
Some records have fax numbers, others are empty.
I want to find all records which do have a fax number. So I wrote into the Query: "is not null", expecting to get only the records which have a fax number or some text in them.
In fact, all records came up in the query, empty as well as non-empty fax fields.
I was wondering if the 'empty field' had a blank space in them, but could not find any. Tried backspace key, but there was nothing to backspace on.
I used the find-replace utility and searched for single space in Whole Field. It picked out quite a few records, but not all - so something invisible seems to be there.
However, when I opened the 'replace' window of find/replace, and had the replace window empty, then clicked 'replace', the msg came 'Access cannot find the specified text'.
What am I doing wrong? What do I have to do to get the query to work?
Thanks,
Adrian
Hi All
I have a feeling that this is an absolute newbie question.
I have three queries, qryVisitsDue, qryVisitsOverDue, qrySiteView.
The query qrySiteView uses SiteID to produce a listing of all sites which includes a count of visits for those sites. If there is no visit data the site does not display. To display the counts I'm using the other two queries. In the first two I'm querying a table, tblVisits, to find what visits are due or overdue based on the current date. They work fine where there is visit data. My problems start when there is no data for a site. I need to be able to show the sites where there is no visit data.
I'm not very experienced so basic explanations are probably the best.
Any help much appreciated.
ChrisD
I have a dataset showing sales by salesperson for each product. I'd like to summarize this data in a table that looks like this:
Note that some salespeople don't sell certain products and that these records show up with "0". My problem is that, in my actual dataset, if a salesperson doesn't sell a product, that records doesn't exist at all. Is there any way to append empty "0" records to my dataset? See attached for an Access file.
DatasetProblem.accdb
Can someone tell me the VBA code for testing whether a query recordset is empty or not?
View 1 Replies View RelatedI'm trying to create a query which will only show fields that have information in them, but I can't figure out how to do it.
View 2 Replies View RelatedI have a main form with two unbound text boxes that filter a subform with a select query in the record source. The problem is that it only works sometimes.when I copy and paste the SQL into a query instead of the form it works all the time. for example when I open the query and the form side by side with the same SQL the form does not show results but the Query does, and yes I have "requeried" both of them after entering criteria into the unbound text boxes.
Here is the SQL:
SELECT [Quote_ID] & "-" & [Version] AS QuoteIDv, tblQuoteID.Quote_Type,
tblQuoteID.Quoted_By, tblQuoteID.Agency, DLookUp("[Contact]",
"tblContacts","[QuoteID] = '" & [Quote_ID] & "' And [Version] = '" & [Version] & "'") AS contact,
tblQuoteID.Quote_Date, tblQuoteID.FileName, tblQuoteID.FileLocation, tblQuoteID.Amount,
tblQuoteID.Active, tblQuoteID.Quote_ID, tblQuoteID.Version, tblQuoteID.Selected
[Code] .....
I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas?
Regards:confused:
I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.
So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.
1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)
I've got 2 saved queries:
1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;
2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];
And I've got a form called 'HomePage' that has
1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process
It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).
I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.
Public Sub InsertTransactionsBttn_Click()
Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef
Set db = CurrentDb()
[Code] ....
I have a query in Access and a word document that opens up the query. Currently the query contains every record in the database. If I alter the query in Access to select a certain set of records (based on dates) when Word opens the recipient list is empty. Checking for errors it says there were no records or no data records matched query options.
How can I get Word to open and use the modified query?
I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.
View 2 Replies View Relatedi removed the DIsTINCT in my query to move some field to be updateable on the form. Once I did that my detail section of my form was empty ..why and how do I fix this problem.
View 1 Replies View RelatedI'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code
Code:
Dim cnn As ADODB.Connection
Dim recordst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook
[Code] ....
I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?
View 2 Replies View RelatedI have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
Hi,
I'm a newbie to Access. I have created a query showing the result base on other calculation. The returned results are in numeric. I would like to know how to sum up the returned results on row to a new column.
A b new column
100 200 300
200 150 350
Your kind help will be highly appreciated
Hi everyone....
I have imported a data sheet into Access that had certain columns a different color...green...they were colored so that the end user would
know which columns were to have input. (visually)
Is there anyway that I can keep this format or color the columns in Access?
Also the end result is from a Query...does that make a difference ?
Thanks....