MS Access Use Fld's frm Tbl1 and Data frm Tbl2

[jc17276]

Ms Access 2010-2016 64 bit or 32 bit

The Master Table(TBL1) fields and the Imported table(Tbl2 ,3,4 ... ) fields are matched up on a form called: RefFrmCORRELATION by hand (ME) , They are kept in a table is called : RefTblCORRELATION

Table 1 Fields Master Table ( RefTblCORRELATION)
'
Tbl1.[ID],Tbl1.[RefTABLENAME],Tbl1.[Report Number],Tbl1.[SHIFT],Tbl1.[Area],Tbl1.[Beat],Tbl1.[DISTRICT],Tbl1.[GRID],Tbl1.[Occurred Date],Tbl1.[Occurred Time],Tbl1.[Reported Date],Tbl1.[Reported Time],Tbl1.[Address],Tbl1.[Incident Type],Tbl1.[UCR Description],Tbl1.[MEANSOFENTRY],Tbl1.[WEAPON],Tbl1.[ENTRYMETH],Tbl1.[ENTRYPOINT],Tbl1.[TARGETS],Tbl1.[PROPERTY],Tbl1.[SUSPECT],Tbl1.[SUSPECT2],Tbl1.[SUSPECT3],Tbl1.[SUSPECT4],Tbl1.[SUSPECT5],Tbl1.[SUSPECTVEHICLE],Tbl1.[COPPER],Tbl1.[GANGRELATED],Tbl1.[LAT],Tbl1.[LNG],

Table 2 Fields names from imported tables
'
Tbl2.[ID],Tbl2.[REPORT],Tbl2.[DATE],Tbl2.[TO_DATE],Tbl2.[DOW],Tbl2.[TIME],Tbl2.[TO_TIME],Tbl2.[SHIFT],Tbl2.[ADDRESS],Tbl2.[GRID],Tbl2.[DISTRICT],Tbl2.[AREA],Tbl2.[LOCATION],Tbl2.[WPN],Tbl2.[ENTRY_METH],Tbl2.[ENTRY_POIN],Tbl2.[TARGETS],Tbl2.[PROPERTY],Tbl2.[SUSPECT],Tbl2.[SUSPECT2],Tbl2.[SUSPECT3],Tbl2.[SUSPECT4],Tbl2.[SUSPECT5],Tbl2.[SUSP_VEH],Tbl2.[COPPER],Tbl2.[lat],Tbl2.[lng],

** the different data fields are like : tbl2.TO_Time is equal to Tbl1.{Occurred Time}

Tlb1 ' master names. I would like to use when running sql, and then displaying it in a listbox control.
'
Imported table names are like :

Tbl2 ' Imported table from different source like csv, txt, xls....
Tbl3 ' Imported table from different source like csv, txt, xls....
Tbl4 ' Imported table from different source like csv, txt, xls....

I was using the table field Names and creating individuals sql , query for each. but more tables are being added and the database is getting large due to code for the sql. I would like to automate the whole process using the field names from my main table, and the data attached to the other table.

THis way i can create the proper column(Len) for display in my listbox and show only the columns need to run and create a report and query for later use.

QUESTION
1. using vba sql is there a way to refer to the Master table : RefTblCORRELATION field name but use the data from the imported fields as seen and display in my form listbox using the Master Table Names only ?

2. if not how can I use 1 vba sql string , to create each sql statement on the fly Using Import table field names, then show my data on my main form to a listbox control

[twinnyfo]

jc17276,

Welcome to Bytes!

First, please bear with us as we try to understand your post/question. I believe that there is an English language barrier which may make it more difficult for communication, but we will try to work through this.

Concerning your question, if I undersatnd it correctly, you ant to be able to use the Tbl1 field names in your queries of Tbl2, Tbl3, etc.?

If this is the case, you would simply provide an alias for those field names when you create the query. For example:

SELECT Tbl2.[TO_TIME] AS [Occurred Time], ...

provides you with a field in your query named "Occurred Time", but which holds the data of the field "TO_TIME" from Tbl2. This way the data may be interchangeable based upon Field Names.

If this is not the intent of your question, please let us know and we will try to work through it.

On a side note, I would also recommend you develop a more usable naming convention for your Db objects. "Tbl1", "Tbl2", etc. may make sense to you, but as you build your DB, at what point do you forget what "Tbl137" contains? I also recommend against field names with spaces in them, as this can make writing Queries more difficult in the lang run (even though Access allows it). If you are importing tables/spreadsheets, that can't be avoided. But in your master tables, you should avaoid it.

Hope this hepps!

[jc17276]

twinnyfo thank you for your response. first the tables have real names not tbl1,tbl2... the table names vary from each department that send them to me. right now I have regburg,vehburg,cms201710, cms201810... I'm looking for a way to programmatically use the tables send to me, but use the field name in my TBL1 as shown (without spaces) so that I can write one vba sql, no matter which table is selected , the data field names stay the same I can then build the needed reports and queries onetime and just change the header on each report for the dataset that is used..
In my example bl2.TO_Time ( is from vehburg )table,Tbl1.{OccurredTime} is the Field name I would like to use on any table selected

[twinnyfo]

I think I understand what you are asking for now. It seems like the import itself is as much of a project as the larger project.

I think this can be done, but it depends ont he data you are receiving. If the tables you receive are always in the same format with the same field names, you could build a table that properly references all these various tables and matches corresponding fields together.

Then, you would need to develop a SQL string builder that fully understood all the nuances of those corresponding field names, so that, when you are working on the vehburg table, the code knows to look for field TO_TIME and alias it properly as [Occured Time].

This would be quaite an involved project, but would pay dividends of automating a very slow manual process.

However, I would think you could build queries for the various tables that would alias those particular fields. You would just have several queries, and then you would have to change the Record Source of your Form. Or, you could save the query def to a common named Query that is used for the Record Source. A couple different options here. But, not seeing a more detailed picture, it's difficult to build anything remotely.