Hi..
I have a table of Products and StateID. The products with a field for which states they are availble using the codes from the StateID table
tbl-Products
ProductID Description Available
1 "Blue Coffee Table" "1,2,3,5"
2 "Red Coffee Table" "2,3,4"
tbl-StateID
StateID State Country
1 ACT AU
2 NSW AU
3 VIC AU
4 QLD AU
5 SA AU
Note - this table will get into 2 or 3 digit StateID numbering...
I already have the Customers StateID figured out from the customers Country & State. Eg Mr John Smith Vic AU = 3
How can I have a from that has a Product Selction that only shows Products where the "3" is included in the tblProducts.Ava ilable
I know the how to have a field show the list where the entire field matches the requried Like Country = AU or State = VIC but using this normalised style and multiple entries in the one field im a little lost.
8 1455
What does the "1,2,3,5" mean in the Available field of your Products table?
It actually looks to me like this table is most certainly NOT normalized! A normalized table would only store one piece of data in one place. You have 4 pieces of data in one place...
Let us know a little more about what those numbers might mean, and we'll try to point out a little better solution.
Regards,
Scott
Hi..
I have a table of Products and StateID. The products with a field for which states they are availble using the codes from the StateID table
tbl-Products
ProductID Description Available
1 "Blue Coffee Table" "1,2,3,5"
2 "Red Coffee Table" "2,3,4"
tbl-StateID
StateID State Country
1 ACT AU
2 NSW AU
3 VIC AU
4 QLD AU
5 SA AU
Note - this table will get into 2 or 3 digit StateID numbering...
I already have the Customers StateID figured out from the customers Country & State. Eg Mr John Smith Vic AU = 3
How can I have a from that has a Product Selction that only shows Products where the "3" is included in the tblProducts.Ava ilable
I know the how to have a field show the list where the entire field matches the requried Like Country = AU or State = VIC but using this normalised style and multiple entries in the one field im a little lost.
you can use the InSTR function for that
select * from yourtable where Instr([yourfield],"3");
or
select * from yourtable where [yourfield] Like "*3*";
stuff like that
What does the "1,2,3,5" mean in the Available field of your Products table?
It actually looks to me like this table is most certainly NOT normalized! A normalized table would only store one piece of data in one place. You have 4 pieces of data in one place...
Let us know a little more about what those numbers might mean, and we'll try to point out a little better solution.
Regards,
Scott
The 1,2,3,5 represents the StateID number from the tblStateID so in this case..
the product with a 1,2,3,5 would be availble In the ACT,NSW,VIC & SA only
you can use the InSTR function for that
select * from yourtable where Instr([yourfield],"3");
or
select * from yourtable where [yourfield] Like "*3*";
stuff like that
The problem here is that when I get to a StateID of lets say 13 I dont want it to be listed as part of a search result looking for 1 or 3 because these will more than probably be a different country even.
So far here is the full listing of the tbl-StateID
tbl-StateID
StateID State Country
1 AU ACT
2 AU NSW
3 AU VIC
4 AU QLD
5 AU SA
6 AU TAS
7 AU NT
8 AU WA
9 NZ AUCKLAND
10 NZ BAY OF PLENTY
11 NZ CANTERBURY/WEST
12 NZ GISBORNE
13 NZ HAWKES BAY
14 NZ NELSON/MARLBOROGH
15 NZ NORTHLAND
16 NZ OTAGO/SOUTHLAND
17 NZ TARANAKI/WANGANUI
18 NZ WAIKATA
19 NZ WELLINGTON/MANAWA
You have a many to many relationship between tblProducts and tblStates. That means that more than one product can be related to more than one state, and vice versa.
The best way to handle this is to create a third table with the ID field for each tblProduct and tblStates. Your queries are far easier to write, and your duplication of data is far less doing this.
Regards,
Scott
You have a many to many relationship between tblProducts and tblStates. That means that more than one product can be related to more than one state, and vice versa.
The best way to handle this is to create a third table with the ID field for each tblProduct and tblStates. Your queries are far easier to write, and your duplication of data is far less doing this.
Regards,
Scott
The problem is with 19 states and so many products i have a table with some 16500 records. is that still going to be the best way forward?
The problem is with 19 states and so many products i have a table with some 16500 records. is that still going to be the best way forward?
That depends on how you view data integrity rules!
To my mind, yes it still is the best way. The reason being that in your products table, how are you going to update the field that contains the stateid numbers if/when a product ceases to be available in one state? Or when you wish to add a state to expand the availability of a product? Or, when a state gets swallowed up by another? Any change that gets made to that field that contains multiple values will require you to write some kind of convoluted vba code to search the database, find the wrong values and change just them, leaving the other values unchanged! (Or worse, go through by hand and try to change them manually... perish forbid!)
As I've said before, database programs are built with data integrity rules in mind. They will allow you to bend and break the rules, but you do so at your own peril...
The linking table in a many to many relationship results in the least duplication of data, is easiest to update, and is easiest to query. If you like doing things the hard way, go ahead! But don't expect many here on this site to be too keen to help you out when you run into problems :-)
Regards,
Scott
MMcCarthy 14,534
Recognized Expert Moderator MVP
The problem is with 19 states and so many products i have a table with some 16500 records. is that still going to be the best way forward?
Hi RobH
It might be worth your while reading this tutorial on Database normalisation. Database Normalisation and Table Structures Sign in to post your reply or Sign up for a free account.
Similar topics |
by: andreas.maurer1971 |
last post by:
Hi all,
since a few years I use the following statement to find duplicate
entries in a table:
SELECT t1.id, t2.id,...
FROM table AS t1 INNER JOIN table AS t2
ON t1.field = t2.field
WHERE t1.id < t2.id
|
by: Rupert Street |
last post by:
I have set up a combo box that contains descriptions of work carried
out. I would like if possible to click on one entry then click on
another to combine both entries to make a single entry separated by a
comma eg (entry 1) data input (entry 2) plan preparation = data
input, plan preparation. Can someone let me know if this possible and
how to go about it - thanks.
|
by: deepak |
last post by:
i have set multiple selection property in bith listboxes(html control) to
true. i have taken 2 buttons(html control) say Button1,Button2.now i want to
add mutiple selected items to another listbox after clicking button1, and
similray remvoing the mutiple selected ite,s from, another after clicking
button2 ,, how can i do this ,, my script is given belo...
|
by: sonal |
last post by:
Hi all,
I hv started with python just recently... and have been assigned to
make an utility which would be used for data validations...
In short we take up various comma separated data files
for eg: area.txt, school.txt, students.txt.... and so on (ok?!?)
now,
1. area code used in the school.txt must be defined in the area.txt
(Primary key in area => area_code defined in area.txt
& Foreign key on school => area_code defined in...
|
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables...
| |
by: vljones |
last post by:
Hoping to find a way of entering new data into a combo box which contains a list of items contained in a table.
In addition I want to allocate an ID number to entries that are not currently in the list.
I have Form with a subform - frmQuotation!frmSubLimits
On the subform I have the following fields:
txtSubLimit and numSubLimitID
|
by: poucedeleon |
last post by:
I am building a database for logging in Officers into our building. It uses a continuous form. I have added a command button labeled "Multiple Bookings. OnClick it opens a form that has two comboboxes (Select Agency: fills in "AgencyName" on Main Form.) (Officer Name: fills "Officers Name" on MainForm.) and text field (Amount: states how many records to create) Each day we have groups of bookings arrive (Chain, Co-op, Comittments & DOC) These...
|
by: fformulaa |
last post by:
Can't figure out how to sum up a total of hours from same date entries.
Query:
SELECT work_hrs_for_day.empl_numbr, work_hrs_for_day.user_date, work_hrs_for_day.workday_totl, mech_hrs_for_day.day_totl
FROM work_hrs_for_day
LEFT JOIN mech_hrs_for_day
ON (work_hrs_for_day.user_date = mech_hrs_for_day.job_date) AND (work_hrs_for_day.empl_numbr = mech_hrs_for_day.empl_numbr)
|
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,...
|
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...
|
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,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |