Table Relationships Help

[SantasLittleHelper]

Apologies in advance... i'm quite a rookie with Access.

I have a table (tblCompanyInfo) with all of the information of different companys within. Within this table is a field where users can select a 'CompanyType' from a combo box. I'm looking to find a way to split the companys up into different tables.

Here's the type of think I'm looking for:

IF 'CompanyType' in 'tblCompanyInfo' is equal to 'Manufacturing'
THEN create a new record in 'tblManufacturing' adding the 'CompanyURN' from 'tblCompanyInfo' to 'CompanyURN' in 'tblManufacturing'

I'm guessing it will be some sort of relationship but I'm not too sure what to do when it comes to relationships.

Any help is greatly appreciated!

 

[Joe4]

No, this isn't normally the way you want to do it.
In a well-designed normalized database, you would not have multiple tables with the same structure. You would have it just as you do now.
You "split" the different company types in queries, not create new tables for each one.
You just use the Criteria field under the "CompanyType" field to designate the different options.

 

[SantasLittleHelper]

Thanks Joe

I'll give you a quick run down as to my issue (this is linked to other questions I've posted)

A company has currently got 4 different databases with company information... The only difference in databases is the type of companies in the table.

E.g. [TABLE="width: 500"]
<tbody>[TR]
[TD]Database[/TD]
[TD]Company Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

These produce batches of 100 (it's currently set up so that record 1-100 is Batch 1.. 101-200 is Batch 2 etc..

I'm trying to merge all of the databases into one single database and all the company information into one table. So I'm trying to find a way to differentiate each record's company type so that each Batch contains a single company type.

Is something like this achievable?

Hopefully this makes sense but feel free to ask me anything... or if this sounds like an impossible task, let me know too.

 

[Joe4]

I am sorry, but that doesn't seem to make much sense to me.
I think we need a better understanding of your data structure and what it is you are trying to accomplish in light of that (and what it all looks like, data-wise).

Where many new users get thrown off is that they often think that you need multiple tables that are similar in structure. You don't, and as matter as fact you shouldn't.
If you have similar table structures, they should probably be combined into one, and then you use Queries to differentiate them.
Queries are probably the single most important thing in Access, and where most of your work, calculations, and filters are actually done.
And you can use Queries as the data source of just about anything that you use Tables for (like Forms, Reports, Exports, etc).

 

[SantasLittleHelper]

Yeah... queries or SQL are probably the best way to do this.

Basically, I've joined a company who have previously used 4 different databases for different types of companies. I want to combine these into a single database and since all of the tables have the same structure, I can to add the company info into a single table.
The difficulty is that an automatic batch number is created in each of the databases using SQL:

Private Sub txtSelectByBatch_AfterUpdate()
If IsNull (Me.txtSelectByBatch) = True Then
Me.FilterOn = False
Else
strStartNum = txtSelectByBatch/Value * 100
strEndNum = (txtSelectbyBatch.Value * 100) + 99
Me.Filter = "RecNo between " & [strStartNum] & " and " & [strEndNum]
Me.FilterOn = True
End If
End Sub

This is why I can't merge them all easily, because the batches (which are allocated to staff) need to remain in sets of 100 records of the same company type. If I merge the tables, the way it is currently set up, we will get (for example) 50 of Type X and 50 of Type Y instead of 100 of Type X.

It's such a weird query but I'm hoping there is some sort of solution somewhere

 

[welshgasman]

As long as you are able to assign the batch numbers the same way then just add the company type to the filter.?

Something like

Me.Filter = "RecNo between " & [strStartNum] & " and " & [strEndNum] & " AND CompanyType = " & Me.CompanyType

That is assuming the CompanyType is numeric, being a value from a combo of CompanyTypes.

If you are using the actual value then

Me.Filter = "RecNo between " & [strStartNum] & " and " & [strEndNum] & " AND CompanyType = '" & Me.CompanyType & "'"