If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query result in relation with table
Hello to all and please help me.
I have a database (about 5000 rows) with 10 columns (person, date, time, amount, city, country, category etc..). I would like to see all these details (all columns) but only for people with e.g. more than 3 transactions in one day and with total amount greater than 5000 (both conditions used together). I have a query that returns a list of people with total amount (5000) and at least 4 transactions a day - it works o.k. But I'v spent a lot of time trying to connect this result of query and data in table without any success. Many thanks. |
#2
|
|||
|
|||
query result in relation with table
Since you didn't post the table structure or the query SQL this is going to be
a guess on the possible solution. SELECT SomeTable.* FROM [SomeTable] INNER JOIN [SomeQuery] ON [SomeTable].Person = [SomeQuery].Person AND [SomeTable].[Date] = [SomeQuery].Date John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Valdemar wrote: Hello to all and please help me. I have a database (about 5000 rows) with 10 columns (person, date, time, amount, city, country, category etc..). I would like to see all these details (all columns) but only for people with e.g. more than 3 transactions in one day and with total amount greater than 5000 (both conditions used together). I have a query that returns a list of people with total amount (5000) and at least 4 transactions a day - it works o.k. But I'v spent a lot of time trying to connect this result of query and data in table without any success. Many thanks. |
#3
|
|||
|
|||
query result in relation with table
You have a 10-column table with person info... but where are you keeping the
"transactions"? If you are saying you are using one row per transaction, and repeating the person, etc. data, you don't have a relational database, you have a spreadsheet! To get the best from Access' relationally-oriented features and functions, you need to feed it well-normalized data, not 'sheet data. Access is not a spreadsheet! Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Valdemar" wrote in message ... Hello to all and please help me. I have a database (about 5000 rows) with 10 columns (person, date, time, amount, city, country, category etc..). I would like to see all these details (all columns) but only for people with e.g. more than 3 transactions in one day and with total amount greater than 5000 (both conditions used together). I have a query that returns a list of people with total amount (5000) and at least 4 transactions a day - it works o.k. But I'v spent a lot of time trying to connect this result of query and data in table without any success. Many thanks. |
#4
|
|||
|
|||
query result in relation with table
If you have a query that returns the primary keys of the rows you are
interested in, you can code a query: SELECT * FROM MyTable WHERE MyKey IN (SELECT MyKey FROM MyQuery) -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Valdemar" wrote: Hello to all and please help me. I have a database (about 5000 rows) with 10 columns (person, date, time, amount, city, country, category etc..). I would like to see all these details (all columns) but only for people with e.g. more than 3 transactions in one day and with total amount greater than 5000 (both conditions used together). I have a query that returns a list of people with total amount (5000) and at least 4 transactions a day - it works o.k. But I'v spent a lot of time trying to connect this result of query and data in table without any success. Many thanks. |
#5
|
|||
|
|||
query result in relation with table
Try this query using your table and field names with the query name that
finds the people meeting your criteria -- SELECT YourTable.* FROM YourTable INNER JOIN qryMeetCriteria ON YourTable.Person = qryMeetCriteria.Person; -- Build a little, test a little. "Valdemar" wrote: Hello to all and please help me. I have a database (about 5000 rows) with 10 columns (person, date, time, amount, city, country, category etc..). I would like to see all these details (all columns) but only for people with e.g. more than 3 transactions in one day and with total amount greater than 5000 (both conditions used together). I have a query that returns a list of people with total amount (5000) and at least 4 transactions a day - it works o.k. But I'v spent a lot of time trying to connect this result of query and data in table without any success. Many thanks. |
Thread Tools | |
Display Modes | |
|
|