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
|
|||
|
|||
Building a query from three tables with differing infomation
Hi - Not too sure if this is possible, or if i'm being a dillweed! And
please bear with me - it may seem a long explanation but it does get there in the end! I have an CSV file that has to be imported every day, with the following fields DATE, Client, Client Name, M/House No., Mailing House, Notified Bags, Notified Items, Receipted Bags, Receipted Items, Outstanding Bags, Outstanding Items. This file is normally around 15000 rows I then have to remove the Notified Bags and Items and the Outstanding Bags and Items, sort this by Receipted Bags, Remove all zero entries - which takes the file down to around 350 - 400 rows, THEN sort by Client (which is a Number), move certain types of client together - bear with me we'll get there soon - Then add in a field of receipt depot and then go through manually adding in the three digit depot code to each row where the mailing house goes to that depot. THEN we have to sort by depot, keeping the client types together to show which depot has receipted how many bags by client type. This process will take me around 20 - 25 minutes, but I don't have to do this now - it's been passed onto another member of staff and it takes him an hour+ to complete. Now we get to the question - I have created an Access D/B with a table that assigns depot number to postcode area (DPT1) and a table of all the mailing houses with their individual postcodes on (MHP1). I have assigned relationships to the different fields of Postcode (between DPT1 and MHP1) and Mailing House (between MHP1 and the CSV file) I have tried to write a query that removes all zero entries and assigns the correct depot number to the mailing house (utilising the relationship) , but all I seem to be getting is every entry assigned to every depot HELP!!! Simon |
#2
|
|||
|
|||
Building a query from three tables with differing infomation
Simon,
Well, at least you outlined the sequence of events... that's a big help! What if you were to do something like this: Use TransferText to import the text file into a temporary table. I then have to remove the Notified Bags and Items and the Outstanding Bags and Items, sort this by Receipted Bags, What do you mean? Delete the records? Just use a parameterized delete query. e.g. Remove all zero entries - which takes the file down to around 350 - 400 rows, ... up to this point seems pretty straight forward - just a few update/delete queries. You can automate these really easily in VBA. DBEngine(0)(0).Execute "ActionQueryName",dbFailOnError (Check out Allen Browne's site on this... www.allenbrowne.com) Sort by client --- this is just simple select query. Why do you need to "move the client types together"? Because you can process them as a unit? What kind of processing? How do you determine the values for "field of receipt depot"? It sounds like you should be able to [outer] join this table to a "lookup" table and return that value. The Count of bags bit just sounds like a totals query... I have tried to write a query that removes all zero entries and assigns the correct depot number to the mailing house (utilising the relationship) , but all I seem to be getting is every entry assigned to every depot This sounds like at least a partial cartesian product, which leads me to believe that you're missing a join somewhere. (A Cartesian product is a query between to tables without a join - it returns every possible combination of values from the two primary keys - so if you had table A with 2 records and table B with 3 records and you built the query without a join, running the query would return (3 x 2) records. Post the SQL of your query. You should have a line in there similar to FROM Depot INNER JOIN [Mailing House] ON Depot.MailingHouse = [Mailing House]. MailingHouseID -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
Building a query from three tables with differing infomation
You need to post the SQL of your query and some records of raw data from the
..cvs. What is the name of the tables and fields you use to move certain types of client together and to add in the three digit depot code? -- Build a little, test a little. "SNA400" wrote: Hi - Not too sure if this is possible, or if i'm being a dillweed! And please bear with me - it may seem a long explanation but it does get there in the end! I have an CSV file that has to be imported every day, with the following fields DATE, Client, Client Name, M/House No., Mailing House, Notified Bags, Notified Items, Receipted Bags, Receipted Items, Outstanding Bags, Outstanding Items. This file is normally around 15000 rows I then have to remove the Notified Bags and Items and the Outstanding Bags and Items, sort this by Receipted Bags, Remove all zero entries - which takes the file down to around 350 - 400 rows, THEN sort by Client (which is a Number), move certain types of client together - bear with me we'll get there soon - Then add in a field of receipt depot and then go through manually adding in the three digit depot code to each row where the mailing house goes to that depot. THEN we have to sort by depot, keeping the client types together to show which depot has receipted how many bags by client type. This process will take me around 20 - 25 minutes, but I don't have to do this now - it's been passed onto another member of staff and it takes him an hour+ to complete. Now we get to the question - I have created an Access D/B with a table that assigns depot number to postcode area (DPT1) and a table of all the mailing houses with their individual postcodes on (MHP1). I have assigned relationships to the different fields of Postcode (between DPT1 and MHP1) and Mailing House (between MHP1 and the CSV file) I have tried to write a query that removes all zero entries and assigns the correct depot number to the mailing house (utilising the relationship) , but all I seem to be getting is every entry assigned to every depot HELP!!! Simon |
#4
|
|||
|
|||
Building a query from three tables with differing infomation
Thanks Pieter and Karl for your help -I think I am making it too complicated
within Access - I'm going to just restart the whole database taking out all the extra rubbish I had added in. I'm always overcomplcating things. I will be using Allen Brownes site though (cheers for the link!) I'll post on here how I get on! 'A little less confused' Simon "PieterLinden via AccessMonster.com" wrote: Simon, Well, at least you outlined the sequence of events... that's a big help! What if you were to do something like this: Use TransferText to import the text file into a temporary table. I then have to remove the Notified Bags and Items and the Outstanding Bags and Items, sort this by Receipted Bags, What do you mean? Delete the records? Just use a parameterized delete query. e.g. Remove all zero entries - which takes the file down to around 350 - 400 rows, ... up to this point seems pretty straight forward - just a few update/delete queries. You can automate these really easily in VBA. DBEngine(0)(0).Execute "ActionQueryName",dbFailOnError (Check out Allen Browne's site on this... www.allenbrowne.com) Sort by client --- this is just simple select query. Why do you need to "move the client types together"? Because you can process them as a unit? What kind of processing? How do you determine the values for "field of receipt depot"? It sounds like you should be able to [outer] join this table to a "lookup" table and return that value. The Count of bags bit just sounds like a totals query... I have tried to write a query that removes all zero entries and assigns the correct depot number to the mailing house (utilising the relationship) , but all I seem to be getting is every entry assigned to every depot This sounds like at least a partial cartesian product, which leads me to believe that you're missing a join somewhere. (A Cartesian product is a query between to tables without a join - it returns every possible combination of values from the two primary keys - so if you had table A with 2 records and table B with 3 records and you built the query without a join, running the query would return (3 x 2) records. Post the SQL of your query. You should have a line in there similar to FROM Depot INNER JOIN [Mailing House] ON Depot.MailingHouse = [Mailing House]. MailingHouseID -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
Thread Tools | |
Display Modes | |
|
|