Changing linked record based on criteria

1 New Member

.. At least, I think that's what I'm asking for...

Please bear with me as I try to explain my setup and my question. :)

I have a database set up for products. each product (obviously) has a unique ID, but not a unique configuration (we'll call them specs)

The specs for each product is based on the product's bottle shape, the brand, and the volume of the bottle. Even though there are many unique products, a lot of those products share the same bottle, brand, etc.

To remedy this, I had a query that created a unique ID based on the Brand, shape, and volume, and then a separate table that contained each unique spec, which also had an id created in the same fashion. This is how I joined these tables in a query. The unique ID is called a BTV Code (as it's based on the Brand, Type (shape), and Volume)

A problem as arisen where the spec would now change based on the year, and a certain spec would be valid for a certain range of years. So the details we have for that product would be valid for say 2001-2005, and then we'd have a different spec for 2006- onward. I was trying to think of a way to incorporate the year into the BTV code, but I can't think of any way to do it since there's a range.

Can anyone point me in the direction of a solution? or maybe a better way of linking the two tables, so this can work?

In short, I need a way to link two tables based on 3 fields, but be able to have different records that lie in different year ranges. I can elaborate more if needed.

Thanks again for any help!

Attached is a screenshot of the relationships for the query that joins the tables together

Attached Images

download.jpg (44.9 KB, 98 views)

Sep 15 '10 #1

Subscribe Reply

987

Similar topics

8665

Dynamically create linked tables (based on Excel files) and prompt user for files to be linked...

by: Chris Powell | last post by:

I am using Excel/Access 2000 and have two large Excel files (25,000 rows each) that I wish to create linked tables in Access rather than importing into Access. The two source Excel files change frequently, so I want to: A) prompt the user, using a custom form, for the specific Excel files to be linked; B) create the links dynamically; C)...

Microsoft Access / VBA

2042

Pulling info from linked table based on text box entry

by: Shannan Casteel via AccessMonster.com | last post by:

I would like to be able to use 4 text boxes (i.e. Part #, Description, Quantity, Price) for up to 40 parts. However, only the Part # and Quantity will be entered. The Description and Price will be pulled from a linked table. The Price field should take the price for the particular Part # and multiply it by the quantity and return the value....

Microsoft Access / VBA

1894

Opening and populating a form with a record based on two criteria (fields)

by: emmaruwa | last post by:

I have a form with two text boxes (in its details section) that pull data from two fields in my database table. This same form also has a button beside the text boxes which is supposed to open another form and populate that form with a record from the table using the data in the previous two text boxes as record selection criteria. However, it...

Microsoft Access / VBA

2460

Changing the background based on value in xml using xsl

by: ml41782 | last post by:

I have a solarr grid tie system. the power data is dumped in xml. using xsl I want to change the color of the field based on the data going positive ( power from the grid ) or negative ( solar is pushing power to the grid ) I have been trying diffferent things and it just doesn't work. What am I missing ? <table border="3"> ...

XML

6068

Changing field color based on date data for each record

by: kevinj | last post by:

Greetings, I am creating a "stoplight" type report that is based on the number of days between dates, the field will be highlighted in red, yellow, or green. I am able to change the background color on the textbox, but each subsequent record is set to the color as determined by the first record. Is there a way to do this for each record? I...

Microsoft Access / VBA

5165

MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

by: NJonge01 | last post by:

Great thanks to all the helpful responses I've read! Recently using MS Access after a lengthy (7-10 years) away from the tool. I apologize for posting a question that for all intents & purposes appears to have been answered several times. Unfortunately, I've tried nearly a dozen approaches that seem to work for others on this board with similar...

Microsoft Access / VBA

4696

Auto create record based on date and criteria in other record

by: ringer | last post by:

I have a db where each record is a certain task to be done. Each record includes the date the task should be completed. Some of the tasks are one time only but some are recurring. I have been having to create a new record for every task even the recurring ones, but I'd like to try to automate creation of the recurring tasks. What I'm trying to...

Microsoft Access / VBA

1919

Delete a record based on a value entered by the user and display therest of the records

by: agarwasa2008 | last post by:

Hi, I would like to delete a record based on a user entered string. Here are the details. I have a txtFind textbox. A string is entered by the user. Based on that string value it displays that one record in the fields in the frmDeleteComponent form. There is also a lstDelete listBox which displays all the record. But for some reason it...

Microsoft Access / VBA

4098

Changing linked SQL Server tables in code

by: brucedodds | last post by:

I've inherited an A2003 application with linked SQL Server 2000 tables in the back end, using the Microsoft SQL Server ODBC driver. We've set up a test SQL Server database. I'd like to automate the process of relinking the application to test/prod. I've found some code examples here of relinking ODBC back ends to different databases, but...

Microsoft Access / VBA

5647

How to add a Record Based on Existing Record

by: prashantdixit | last post by:

Hi, I am developing a stock control software. Iam stuck somewhere. I have a form "Add new stock" consisting of combobox, text boxes etc. which is used to add records in a table. I have another form which has "Add Similar To" button. The table which i used to store records contains a field call "Add More Quantity". If a user enters any...

Microsoft Access / VBA

7459

Changing the language in Windows 10

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...

Windows Server

7726

Problem With Comparison Operator <=> in G++

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. ...

C / C++

6052

AI Job Threat for Devs

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...

Career Advice

5377

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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...

Microsoft Access / VBA

5097

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3505

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

3488

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1953

transfer the data from one system to another through ip address

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

C# / C Sharp

1064

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP