UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Joining Multiple 1 To 1 Tables In Query, Office 2003    
 
   
Paxman
post Apr 12 2018, 07:29 AM
Post#1



Posts: 89
Joined: 14-September 06



Just noticed this should be in Access Section and i have managed to post in the Excel forum. Can it be moved?

Hi

I am reading a large text file with multiple values into multiple tables with a 1 to 1 relationship between them all. The file is too large for one table and as the text values have natural groups i split them up into several tables.

I can populate the tables but am having problems thereafter.

I would like to have a form/forms to modify the data in the tables.

However, trying to produce a recordset to use for the form is proving difficult.

I tried putting all the tables on to the one query grid but Access did not like it (although Access should allow more).

I then produced several smaller queries that did show the records. But i now cannot seem to join two or more of these smaller queries on the grid to give me the full set of records.

I am possibly doing something daft here so can anyone give me some pointers as to how i might get this to work.

Thanks

neil
This post has been edited by Paxman: Apr 12 2018, 07:33 AM
Go to the top of the page
 
GroverParkGeorge
post Apr 12 2018, 07:40 AM
Post#2


UA Admin
Posts: 32,388
Joined: 20-June 02
From: Newcastle, WA


I suspect the root of this problem is that the tables are not really set up to support relational data, i.e. the kind of data structure required for a relational database such as Access.

First. One to One tables are quite rare, so that alone raises suspicion, but the telling comment is here: "the file is too large for one table and as the text values have natural groups i split them up into several tables." If this is done properly, you usually end up with one-to-many tables, although an occasional one-to-one does occur.

Your comment about not being able to put all of the tables into a single query also points to the same kind of problem. Perhaps you are exceeding the 255 column limit? If so, that's a symptom of the underlying problem--tables not properly designed.


Let's dig into your design for these tables. If you can, uploading the database with some representative data would be the most efficient. Short of that, a relational diagram showing all of these tables would be helpful.

Thank you.
This post has been edited by GroverParkGeorge: Apr 12 2018, 07:47 AM

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 12 2018, 07:41 AM
Post#3


UA Admin
Posts: 32,388
Joined: 20-June 02
From: Newcastle, WA


Have you studied the materials we provide here?

--------------------
Go to the top of the page
 
Paxman
post Apr 12 2018, 08:13 AM
Post#4



Posts: 89
Joined: 14-September 06



I have attached a snippet if you like of the relationship window.

So multiple tables all with SetID as a foreign key from the Main td_Filesets - FilesetIdx

The overall system idea is to read in a multiline text file which has say 290 values (in 26 groups) stored as text but are in effect a config file that a program uses/displays as boolean. text, date, decimal etc.

Having read in all the values i want to be able to compare differences between sets and to alter and save new configurations.

I did look at having a table called settings with GroupName, SettingName, SortOrder (used when writing the modified file) and settingvalue. But the Value types differ.

If i stored them all as text somehow then how would i have a form that treats boolean etc. correctly. Currently i have a lookup table during import that identifies the data type of each value and imports it as such. If that makes sense. I also have code that writes the file correctly but my current problem is how to show the records in a form.

However it may be a fundamental design change is needed (and that is fine) if i know a direction to proceed.

I have designed many Access DB's over the years but this is something i have never done before and it seems to have affected my logical thinking!

Any help appreciated.

Thanks

neil
Attached File(s)
Attached File  relationships.png ( 18.39K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Apr 12 2018, 08:30 AM
Post#5


UA Admin
Posts: 32,388
Joined: 20-June 02
From: Newcastle, WA


Is that the Entire set of tables? Quite clearly we don't even see the full set of fields in these tables.

I see at least two places where the data does not appear to be properly normalized, which is what I suspected led to this query problem.

You have tables called "Manual1" and "Manual2", almost always a sign that the data represents the same entity and should be normalized into a single table. It's not clear what "Manual" actually refers to. Not always, but definitely to be evaluated further.

Within the table called "Manual2", you have two repeating columns, TradeDirectionLong and TradeDirectionShort. When you see two or more fields with names like this, e.g. XXXX1, XXXX2, XXXX3, etc., you need to zoom in and correct that.

There are, other, similar problems visible in the table called Manual2, "Long_Grid_UpperLimi???" (twice) and "Short_Grid_UpperLimi???" ( also twice). And that's just in the partial view of some of the tables we can see. I would bet that there are many other, similar problems throughout these and other tables.

Here's a good set of blogs explaining the problem and how to fix it. Get that done, then revisit this query problem. It will go a lot better with properly normalized data.

--------------------
Go to the top of the page
 
Paxman
post Apr 12 2018, 09:16 AM
Post#6



Posts: 89
Joined: 14-September 06



Hi

I understand all your comments and if it were the case that i had repeating data where i would just store the foreign key I would have fixed it hopefully.

I can assure you every field and value is unique.

I have a text file with over 280 values and some of the names are similar that is all.

The table names as mentioned are how the unique values are grouped in the text file nothing more than that. I may well be able to combine tables but I do not see that will completely fix the problem.

The text file has a value per line. Here are some of the first few lines to give an idea.

You will see a few of the group names that have been turned into tables Which may be where the problem partly lies?

I don't really want to post the entire file but would be happy to give more snippets or PM it to you if you have the time to look.

Hope this helps clarify somewhat.

Thanks
Neil

setGENERAL=---------- GENERAL PARAMETERS
PriceActionCheck=0
NearPoint=10
FarPoint=50
AlternateStartUp=1
AutoReplaceTP=0
BanStopLoss=0
SetStopLossForHedgedTrades=1
CloseAllTradesOnNetProfitTrigger=0
NetProfitTrigger=10.000000
AccountHistoryLookBackDays=1
GMT_StartTime=06:20
GMT_EndTime=19:00
LotSize=0.300000008
GridMaintenance=1
MaxNumOpenTradesControl=1
MaxNumOpenTradesValue=1
setMANUAL1=---------- MANUAL GRID DIRECTION PARAMETERS
TradeDirectionLong=0
TradeDirectionShort=0
setMANUAL2=---------- MANUAL GRID RANGE PARAMETERS
Long_Grid_UpperLimit=81.50000000
Long_Grid_LowerLimit=80.50000000
Short_Grid_UpperLimit=81.50000000
Short_Grid_LowerLimit=80.50000000
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd April 2018 - 06:23 AM