Challenging Query

[Guest]

I have a table with InvoiceNumber, LineDetail, Amount, Date and VendorName

I would like to pull only the InvoiveNumbers that have the same Dates and VendorNames

I have a problem with the subquery..

Thank you in advance for your help

Diana

 

[John Spencer (MVP)]

Can you restate your problem? What you are asking is totally unclear to me.

Do you want to get all Invoices where there are duplicated Invoice Numbers,
Dates, and VendorNames?

OR ...

 

[Bill Taylor]

Use a summary query and create a field like so...
VNameAndDate:TblName.VendorName & cStr(tblName.Date) as well as the Date and
VendorName use the count function on the vendorname field. Group on this
created field (should be first in query builder). set the criteria of
vendorname to > 1 . Now create a second query using the first as a subquery
(source object) and the Table..... join them on both the fields Date and
VendorName. set the join properties to "All records from Table and only the
records that equal from the first query" and the results should be a list of
Invoices created on a given date from a certain vendor with more than one
invoice.

HTH

 

[John Spencer (MVP)]

So for a specific vendor and specific date you want a list of invoices, is that correct?

SELECT Distinct InvoiceNumber, [Date], VendorName
FROM YourTable
WHERE [Date] = [Enter a Date] And
VendorName = [Enter Vendor Name]

That is the simplest possible query. You will get two parameter boxes, asking
you for a date and a vendor name.

I am guessing that is not what you want, but your explanation is still leaving
me baffled as to what you do want. I hope this helps.