Building a query from three tables with differing infomation

[SNA400]

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

 

[PieterLinden via AccessMonster.com]

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

 

[KARL DEWEY]

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?

 

[SNA400]

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

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

--



.