Search results

Hi, I have 2 queries, one showing returns with dates and the other showing sales with their sales dates (both have item number, customer name and sales person's name). For each entry in the return data, I want to find the matching sales date in the other query. I tried using the Dmax query with...

Hi, I have 2 queries, one showing returns with dates and the other showing sales with their sales dates (both have item number, customer name and sales person's name). For each entry in the return data, I want to find the matching sales date in the other query. I tried using the Dmax query with...

Thanks very much. It works perfectly!

I made the change to the code as I was trying to get the difference between closing date of previous loan and disbursement date of current loan (once it identified the record with the immediate disbursement date) ... is there a way of getting this? Thanks.

Hi, May I ask a couple another question on this? Does the data have to be pre-sorted before the query is run? The reason why I ask is that I can't seem to replicate some results from the query and am wondering if its due to how the data was sorted or the logic in how the code is written is...

Thanks very much for your help!

What I meant was that I had extracted the NewLoans table in the example but it actually is in a database with other tables. So I assume that in the module, where the code says, for example, "[ClientID]", that this should be replaced by "[NewLoans]![ClientID]". I tried this and it works - not...

I followed your instructions and it works. I changed the ret (default) to "" and that worked to. Thanks a million. One further question - if I were to have the module in an access database with other tables, would I need to include the table name before the field name in the module? Swee

Thanks very much for your pointers. I tried them out. DateDiff is easy enough, but I am not so sure about how to use DMax. See attached for my sample. I have tried out Expr1 (datediff) and Expr2 (DMax) in Query1. The plan is to embed Dmax into Datediff. The data has each client uniquely...

Hi, I have a list of client activity - client name, loan ID and loan date. I would like to create a new field that shows the number of days between one loan and the next. If I was doing it in excel, I would need to sort the data by client name and loan date and then calculate the number of days...

Hi, I have been trying to import and append data from an excel file into access and keep getting the message "The connection for viewing your linked Microsoft excel spreadsheet was lost." I have tried several options including: -saving the data in a new file - checking to ensure that the field...

Hi, I figured it out, I had too many fields after the SELECT statement (it included the same fields from both tables when I should have specified only one table). Thanks a million for your help!

Thanks! I am making progress but now getting the error "Number of query fields and destination fields are not the same". First part of query now reads: INSERT INTO [unique values - all fields]([Month], [Location], [Item No], [Description], [Customer Name], [Sum of Revenue], [Total Quantity]...

Hi, I am still having trouble with the "Insert" part. I replicated your screenshot with sample tables and that worked - it gave me the non-duplicated records. When I added the first part with the Insert statement, it gives me the message "Syntax error in INSERT INTO statement". See below for...

I am having trouble trying it out. I opened a query design and went to sql view and tried to insert it (with the changes to the name of tables and variables). Is this what I should be doing?

Thanks for the lead ... I did not know about the insert query and its ability to exclude duplicates. So that would work at the point of appending data to a table? Would you be able to point me to some leads the insert query?

The reason for the duplicates is that each period, I download data (eg, sales data) into an Access table for analysis. If the date range for the data downloaded overlaps between one period and another, then I will have duplicates. As a precautionary matter (because this can happen just due to...

How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table. Thanks

Thanks - that helped. There were duplicates in the item table. And yes, it was a left join I meant, not outer join. Thanks again.

I have two tables: the first has sales information by item number and date (and other information) for a particular period the second has item number and item description (it is a comprehensive list of all item numbers). I would like to put the item description into the first table, by matching...