Massive Tables!

[psahdBg]

I am a newbie to access, so please be gentle!
I have 2 excel tables. One has about 148 fields and the other has 175.
There are 5 common fields in the two tables

Mun_stand =
ErfNumber
Mun_strno =
StreetNo
Mun_strnme =
StreetName
Mun_subnme =
Suburb

Quant =Toilets
​

Of these, the primary would be the Mun_stand.
I would like to draw an exception report on each of the common fields.
The Mun_stand is not unique.

Any help would be appreciated.

 

[CJ_London]

I would like to draw an exception report on each of the common fields.

what does this mean? you want a list of what is in one table but not the other?

If so then use a left join - in query builder, make the joins between table 1 and table 2 for each of the common fields, then double click on each join and select the 2nd option.

this will list all records from one table plus those from the other table where there is a match. To only see the unmatched records, add a criteria to one of the fields in the second table of

Is Null

 

[psahdBg]

Trust a newbie to confuse it from the start!
tblVenus contains data(fields) that needs to be verified against fields in tblAudit

Here is my query in English:
Match tblVenus.Mun_stand with tblAudit.ERFNumber
Diplay value of tblVenus.Quant and value of tblAudit.Toilets
Calculate and display result of value of tblVenus.Quant - value of tblAudit.Toilets

My query should display:

Erf/Stand Venus Count Audit Count Difference

5687 8 10 -2

Hope that makes what I am looking to do a bit clearer!

 

[CJ_London]

OK - we'll build a query based on your statements, but in the order they will appear in the sql code

Diplay value of tblVenus.Quant and value of tblAudit.Toilets
&
Calculate and display result of value of tblVenus.Quant - value of tblAudit.Toilets

SELECT tblVenus.Quant, tblAudit.Toilets, tblVenus.Quant- tblAudit.Toilets AS Difference

Match tblVenus.Mun_stand with tblAudit.ERFNumber

FROM tblVenus INNER JOIN tblAudit ON tblVenus.Mun_stand = tblAudit.ERFNumber

Put it together and you have

 SELECT tblVenus.Quant, tblAudit.Toilets, tblVenus.Quant- tblAudit.Toilets AS Difference
 FROM tblVenus INNER JOIN tblVenus ON tblVenus.Mun_stand = tblAudit.ERFNumber

 

[psahdBg]

Thanks CJ_London. My head has been spinning like a top since early this morning! I hav managed to implement a lot with that bit of help. I will have more queries later! I am trying to understand why some records are not pulling through (missing info in them) and on the Difference calculations in some instances the AS Difference result is a nul value. ie, 3-2=1, 2-3= -1, but then "blank" - 3= "blank"
I will research more before wasting your time!

 

[CJ_London]

use the nz function

nz(tblVenus.Quant)- nz(tblAudit.Toilets)

 

[psahdBg]

CJ London, I have made great strides! So, I have 11 linked tables, each the same, but dealing with different suburbs. I would like to run a query across all the linked tables looking for, as an example: all erf numbers on Smith Street. Smith Street could be in multiple suburbs.

Regards
Peter

 

[Galaxiom]

So, I have 11 linked tables, each the same, but dealing with different suburbs. I would like to run a query across all the linked tables looking for, as an example: all erf numbers on Smith Street. Smith Street could be in multiple suburbs.

Why are the suburbs in different tables in the first place. Shouldn't be that way.

 

[CJ_London]

I'm with Galaxiom on this - why not one table with a city or whatever identifier?

 

[psahdBg]

Because my input data is coming from Excel tables that are being updated regularly, I have linked to those excel tables. The source data is in multiple tables because they are arranged by a billing cycle. I hope that makes sense.

Regards

 

[psahdBg]

Let me clarify - Excel spreadsheets! Not tables..

 

[Galaxiom]

Because my input data is coming from Excel tables that are being updated regularly, I have linked to those excel tables. The source data is in multiple tables because they are arranged by a billing cycle. I hope that makes sense.

Your working data structure should be dictated by design. Same data goes in the same table. Otherwise it means you are storing data in the object metadata instead of the record and databases are not designed to work like that.

You should be importing and updating the data from those spreadsheets into your normalised structure, not using them as the working storage.

 

[psahdBg]

Hi Galaxiom, you need to break that down for me!! That was way above my grade! Thanks in advance. (feeling very dumb right now)

 

[Galaxiom]

The most vital element of database design is the table structure. For best performance we follow the rules of data normalization. At the ground floor these rules state that each piece of data is only stored once and that it is impossible to store records that don't make sense in the context of other records.

Never store data in the structure of the database. This is what you are effectively doing when you have a suburb embodied in a separate table. The name of the suburb should exist in one place only, in a record, not in the name of a table, field or any other part of the database's metadata. During your import you add the record to your table with an extra field for the suburb name that came in on the spreadsheet.

When this is done right it is incredibly simple to build forms that interact well with the structure and the retrieval of data is very efficient. Forms and queries lead themselves to generalisation such that a much smaller number of objects with much more versatility do the work of a multitude of queries that otherwise have be specifically written because they use a field or table name as data. Metadata cannot be passed to a query as a parameter.

When you are presented with data that is not in the same form as your properly structured tables you build queries and procedures that take that raw data and process it into your cordon bleu table structure.

It is usually a good idea to import the raw data as text to prevent any potential import exceptions where the datatype isn't what is expected. Then use Update queries on the destination tables in an outer join. This way you have full control over the conversion of the source data into your tables.

You don't want to use the data as it comes to you as your storage.

For example the spreadsheets come to you for each suburb. You build an import process that does whatever it has to do to reconstruct the data for your tables. Then you dump the original spreadsheet out of the database.

Don't ever let the format of the data as it is given to you dictate the structure of your storage. And while we are on the subject of things that might influence design, never let the specification for how the data is to be presented to the user override the storage structure decisions.

 

[psahdBg]

Well, I have learnt more about databasing in the last two weeks than the previous twenty years! Its all about NORMALIZATION! Get that right and most things seemingly will fall into place.

So, here is my question: I have 3 sets of similar data (all about municipal stands and their related attributes). The first is the municipal database and the second is from the national Deeds Office, the third is from an audit that we conducted physically.

There are many fields in each that are the same. My PK would be the StandNo. I would like to query by StandNo, pulling in from all three tables and selecting the "Best Fit Data" to create an improved database.
I would like to hear the steps you would take to eat the elephant!

 

[psahdBg]

Hi Galaxiom

I am struggling with a query. I am drawing info from tblWindeed.streetname. I need to include all records that have a partial match. For example the street name is John Wayne St. I need all the records that have "John" or "Wayne" to be included.
I cannot specify the text every time - not enough years left!! SO, I want to use a control field that has the correct name (John Wayne) and incluyde all variants of that.
Is this possible?

Thanks in advance

 

[Galaxiom]

This would require some VBA.

Use the Split() function to get an array of the words in the search control. Exactly what you do with the words depends on how you want to display results. That may be a form filter, a Where Condition on an OpenForm command or a query SQL. Whatever way you choose, it is done by looping through the array and concatenating the values into Like phrases.

Another function you may be interested in is the weighted Damerau-Levensthein match. It returns a score for the similarity of two strings. Ideal for matching when the data may have typographical errors.

See post 10 in this thread.

 

[psahdBg]

Thanks! - I will crawl under my rock until I get my head around it!

 

[Uncle Gizmo]

I think you should crawl under a table!

Sent from my SM-G925F using Tapatalk

 

[Galaxiom]

I think you should crawl under a table!

What sort of form would that be showing?