Seems like a query issue to me

[pokemasterflex]

Hey guys,

I have a multi-table Access 2010 database that I'm working with. The setup is like so:

Model Data holds 49 records. PK is ID

ID relates the other tables back to Model Data (with the exception of Weights, that's a standalone table) so essentially you get the Cable, Adapter and Kit for each ID. It should only show 49 records.

In the current setup (splitting the relationships into multiple queries) it shows the requested data, but once I create another nested query or try and manage all the relationships in one query and output the same data it goes haywire and outputs thousands of duplicate records.

The end result will populate the form Cable Information.

What's my issue here? I think this is just a case of my eyes not seeing it and it'll be a d'oh moment

Have I missed something? Is there a better, more efficient/elegant way to get to the result I need?

 

[plog]

I'm not entirely sure what you want the end result to look like, so I can't really help you with that. What I can help you with, is identifying an improper table structure. You have 7 too many tables. Whenever you store relevant information in field or table names, it means you are doing it wrong.

You shouldn't have tables with the same structure. You have 8 tables with a structure like this:

ID, __Kit, __Cable, __Adapter

Each table name stores what item type the table is for. Instead of storing that information in the table name (i.e. USB, AS142, etc.) that data should be in a field.

All of the data in those tables should be put into one table with an additional field to denote what item you are referencing. It would look like this:

ID, Kit, Cable, Adapter, Item


I think fixing that will go a long way to helping with the other issue you are having.

 

[pokemasterflex]

Hey plog,

Not sure I understand. What would that do other than clutter up the relationships?

A unique field is already used as the key that lines it up with a Model in Model Data

 

[plog]

Relationships are the issue you are currently having. My method wouldn't clutter them up, it would unclutter them.

My advice had nothing to do with how you were lining up tables, it was reducing the ones you have. You are currently storing attributes of your data in the table name. Instead of that you should move that attribute to a field. Thsi will make all those tables go into 1.

Suppose you were storing car information. You wouldn't have a table for Fords, a table for Chevys, a table for Toyotas and a table for Hondas. You would have one table for cars and a field to hold the make. Your situation is similar.

 

[jdraw]

I agree with plog. You may find these links helpful in setting up your database tables and relationships.
Database principles

ERD

Good luck

 

[pokemasterflex]

Thanks guys

Changed around the structure a bit. Still having the same issue in the Query

The objects involved are Form1 and Query1

Ideally I'd like to get to a point where the user selects an item in the dropdown list generated from Model Data and it returns all the records with a matching Item (F5 field) and populates the form with that data

 

[JHB]

Is it so you want it, ("Form1")?

 

[pokemasterflex]

Unfortunately no.

There are two criteria:

Model
Source

which results in one record

e.g. Adventurer SF40A

 

[JHB]

Now I had insert the one criteria, can't you self put in the other by looking at my solution?
So far I can see, the form hasn't any control with the name "Source, and no table has a field name = "Source".
So what/where is the source coming from?

 

[pokemasterflex]

Thanks JHB

Seems to be working now. What do you think? Anything you'd change?

 

[JHB]

Seems to be working now. What do you think? Anything you'd change?

Does it fulfil you requirements below, (then it doesn't matter what I think or what I would change! )?

Ideally I'd like to get to a point where the user selects an item in the dropdown list generated from Model Data and it returns all the records with a matching Item (F5 field) and populates the form with that data

 

[pokemasterflex]

Hm, on second look no. For some reason the Model combobox is not filtering