473,669 Members | 2,514 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query design help....Create Table?

54 New Member
Hi all

I have a database with the following tables:
tblEquipment
tblSuppliers
tblManufacturer s
tblEquipment_Ty pe
tblModels


Each table has an Auto increment number field that is the ID for that table:

i.e. tblManufacturer s has a Field called Manufacturer_ID
tblSuppliers has a Field called Supplier_ID

At present the only place where all these ID Fields 'come together' is in the table tblEquipment, as that holds every instance of every item of equipment.

However I want make up a new table called Identifiers that will bring all the ID Fields from each of the tables together..i.e. the new table will have the following fields:

Model_ID
Supplier_ID
Manufacturer_ID
Equipment_Type_ ID

and I need to have it based on the Model_ID field of the Models table...

I tried creating the new table and added all the Model_ID's into it o.k. but then I just couldn't get all the related fields from the other tables to write to the table. I tried using INSERT INTO and I was hoping to do this one column at a time but it just kept failing all the time. I though it would be dead easy (isn't that always the way when you get stumped totally?)

Anyway any advise appreciated.

Cheers
Mike
Feb 22 '09 #1
8 1469
NeoPa
32,569 Recognized Expert Moderator MVP
What would Identifiers mean? I can't see how this could make sense Mike.

Would any of these identifiers be linked?
Feb 22 '09 #2
ZaphodBBB
54 New Member
Hi NeoPa

The reason for the identifiers is that at the moment the only 'link' between the various tables is via tblEquipment.

So lets say you wish to add a new Model to the database, you can do that but it wont be linked to say a supplier, Manufacturer, Equipment Type, unless you have added at least one item of that model to the Equipment Table.

Also when adding a new Item of Equipment to the database at present you have to either select the various items such as Equipment Type, Supplier etc from ComboBoxes (thus relying on memory of who supplies what etc). When adding a new item of Equipment it is optional to have a supplier, as this may well not be known (suppliers change and there is a lot of old equipment).

Now that there is a fair amount of data in the tables, many suppliers, Equipment types, Manufacturers, I'm modifying the database to automatically provide the Supplier Names, Manufacturers etc if they are known already... but this requires an already existing item of that equipment be in existance to hold all the 'links' to the other tables.

The purpose of the Identifiers table will be to link all the various Model_ID's to their various Manufacturer_ID , Supplier_ID, Equipment_Type_ ID fields in the relevant tables Without having to have an actual item of that model currently in the database.

As far as relationships go the tblIdentifiers Fields will have One to One relationships with the Equivelent _ID fields in the other tables.. i.e.

tblIdentifiers. Model_ID will link with tblModels.Model _ID
tblIdentifiers. Supplier_ID will link with tblSuppliers.Su pplier_ID
and so on.

Its the only way I can see to be able to link all the various identifying Fields from the various tables where I do not actually have an item of that type entered (as yet) into the database.

PHEW hope it sort of makes sense?

Thanks
Mike
Feb 22 '09 #3
NeoPa
32,569 Recognized Expert Moderator MVP
I don't mean this as criticism, as you've clearly given it a good shot, but you don't really answer either of my questions Mike.

The reason they were posed is to help you come to a realisation of why what you say is not really going to be helpful. Give me a few minutes and I'll see if I can come up with something that may be a bit clearer to understand.
Feb 22 '09 #4
NeoPa
32,569 Recognized Expert Moderator MVP
Mike, I'm sorry to say I think your fundamental reasoning is flawed.

Check out (Normalisation and Table structures) for a more complete explanation and things to consider, but for your current situation let me see if I can give a bit of direct guidance.

Let me start off by saying that I think tblIdentifiers is a complete non-starter (sorry). I see no way that it can help. In fact, I can see no way that you can populate it in a way that would be helpful.

What you possibly need to consider is a table structure that reflects the hierarchy it's supposed to match. I'm guessing that the Equipment table has a many to one link to the Model table, which in turn has a separate many to one link to the Equipment Type table. The Model table will have separate links also to both the Manufacture and Supplier tables. Whether or not these are simple many to one links depends on your industry, so I cannot tell you. Let's assume for simplicity that they both are, and we can deal with the possibility that they're not later if necessary.

It seems to me now, that as long as these reference tables (Model; Equipment Type; Manufacturer & Supplier) are properly populated, there should be no issues with the data you enter into tblEquipment. It may be that you need a facility whereby entry of new values (not already included in the database) into tblEquipment automatically triggers off the opportunity to add the value into the relevant reference table on the fly. If so, then that is fine.

This is how things should be fitting together in your database as far as I can see. Reporting on all values is then perfectly possible and correct.
Feb 22 '09 #5
ZaphodBBB
54 New Member
Hi Neopa

Yes your dead right as far as the relationships go. The Table tblEquipment has many to one relationships with tblSupplier, tblManufacturer s etc.

The problem has been that :

The database is used out in the field, so when new items are being added it may well be that whoever happens to be entering new gear will not know the supplier. Usually they will know the Manufacturer (as its usuallly prominently displayed on the equipment), they'll know what Type of item the new Equipment is a class of etc.

Because of this the database was made with a lot of compulsory fields, but Supplier is not one of them.

So they save an item of new equipment...let s say the model is 200WS (philips monitor) but they do not know the supplier. The new item gets saved with its Serial Number, Equipment Number etc, but no supplier.

Then next time someone comes to enter another item of the same equipment, they select the 200WS from a comboBox and (I guess) because there was no Supplier saved with the original item of equipment, the recordset that is opened or attempted to open to populate the other fields on the Add Equipment form ends up with rs.NoMatch = True, so a Message box pops up saying that there are no details for that item. Even though there is in reality everything but the Supplier details. So the user cannot enter another Model of the same name so it effectively means that another item of the same Equipment cannot be entered until the sup[plier details are known.

I have started working on a work around but it is (for me) long and complicated specially as databases are not my main job..just something I occasionally enjoy doing, if it makes my life easier.

Thanks again
Mike
Feb 23 '09 #6
NeoPa
32,569 Recognized Expert Moderator MVP
@ZaphodBBB
I'm not sure you read my post carefully enough Mike.

I was suggesting the links to tblSupplier & tblManufacturer s be from tblModel (rather than tblEquipment).

It's about separating the definition, from the data.

tblModel should store all the definition data (via its links to other tables etc), and tblEquipment should simply store the actual data and a simple link to the definition data via tblModel.

Does that make sense?
Feb 23 '09 #7
ZaphodBBB
54 New Member
Hi NeoPa

O.k. thanks for that. It will be very interesting to try and get column data from the other tables and add it to the Models table, rearrange all queries etc!!

I have no idea how to take existing data from a column of one table and add it to another column of another. Generally all I ever do is add rows (records). Will be even more interesting I guess as specially in the tblSuppliers there will be a fair number of blank entries.

Any basic hints of what to look at as a method goes for copying colums from one table to another?

Thanks again
Mike
Feb 24 '09 #8
NeoPa
32,569 Recognized Expert Moderator MVP
Only to remember that you are copying rows (records) not columns (fields).

Actually I'm struggling to grasp actually where you're at at the moment. Possibly you can attach a Compact / Repaired and zipped version of your database for me to take a look at.
Feb 24 '09 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
2491
by: none | last post by:
Hi. I'm implementing a web deployment using 9i. I have a table for STOCK, which has one or more PURCHASES, which have one or more PURCHASE_ITEMS. When a purchase item is INSERTED, I'd like the web module component to update the relevant grandparent record of STOCK, and when UPDATED, to remember the previous level and adjust accordingly. I'm sure that this can probably be done in PL/SQL, but don't have a clue how - all books and...
3
1787
by: Astra | last post by:
Hi All Wondered if you could help me with the below query. I have 1 simple table called STOCKCATS that consists of 2 fields. These fields are called CATID and LEVEL. The contents of this table are as follows:
1
3201
by: ravi | last post by:
I have created the following interest to calculate the interest for the following currency pairs. I have tried to combine them in macros using conditions but the next query that is run in the macro ends up deleting the previous interest value that has been generated by the query. For example if query 1 is run on the table with currency pair USD/CHF then the interest will be updated without any problem but if there is another entry in the...
3
579
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to make a query that selects from a table as desribed below .. I have a table (Volunteer) that has a member field (memnumber) and a number of fields that are headed in various categories and are yes/no formated
4
2065
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating premiums are dependant on the country in which the client is in. Therefore, we have a Country table, with its list of rates, a client table and then the property table. Getting this is great, works fine, easy! Problem is, now I need to work out a...
8
3715
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: "Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21" 1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21 1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
6
4840
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
27
18389
by: Bob | last post by:
running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the crosstab query itself - only use information FROM it to update a perfectly updatable table! Why the ---- would I get this error, when there are clearly NO permission issues, or issues in updating the table I'm trying to
4
2355
by: Deus402 | last post by:
Here is my table design: tblEmployers EmployerID autonum (primary key) EmployerName text tblLocations LocationID autonum (primary key) EmployerID longint (foreign key) LocationAdress text
2
1964
by: sara | last post by:
I am helping a non-profit track their elder clients and care given to the clients. The organization would like a report that shows various info on ALL clients, such as: # in each town, # and Percent Male, Female, Smoker, # in age range # that use wheelchair or walker.
0
8466
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8384
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8896
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8810
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8590
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7410
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4387
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2798
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2035
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.