I have one table of transactions, another table of price quotes. Transactions are nearly daily; quotes are periodic, roughly once per week. In a query, I want to pull the oldest (or least date) price quote whose date is greater or equal to the transaction date (trying to find the nearest price quote to compare to actual price paid). I have criteria for the price quote date as >=Trans Date, and tried setting the field to min or last, but these don't do anything. Can someone fill in the missing piece? Thanks.
Apr 2 '08
15  3594  FishVal 2,653
Recognized Expert Specialist
The Zenith table is just one of many tables that are data downloaded/ submitted by the vendor and cannot simply be changed. I understand the table structure is not perfect but that's what I have to work with (you should see some of the other tables :). Unfortunately, because each vendor has various formatting and verbage, I have to have seperate tables and queries. This surely isn't "optimal", but it does work for what we need.
Ok. I will not argue further. ;) Its your own choice to sacrifice database design for the sake of "easy import". Take a look at Sub Query Problem. The solution I've provided there is not the most beautiful one but seems to work.
Regards,
Fish
NeoPa 32,579
Recognized Expert Moderator MVP
OK.
Assuming the tables as laid out in your post #4 then, how are the two tables linked?
The fields would be linked by limiting the Vendor Id to a specific value. I used FishVal's example and have a decent workaround that will do for now as we rethink some of this. I'm learning as I go that there are so many exceptions, a general rule is getting hard to use. Thanks NeoPa and FishVal for all your help!
NeoPa 32,579
Recognized Expert Moderator MVP
I sort of get where you're coming from, but I still see no way of linking the two tables, even if [Fuel] is limited by [VendorID] (There's no similar field in your other table - [Zenith]).
This may not matter for now as you have a work around, but if/when you decide you'd like to proceed we will need to know :)
Thanks again NeoPa. FYI Zenith has a unique Vendor ID, so I'd limit it that way. Again, my lack of clarity on here.
FishVal 2,653
Recognized Expert Specialist
Hi, kpfunf.
Though I've promised not to argue on the tables structure I would stay it once more. [Price Quote] -> [Sales] is so classic example of one-to-many relationship ... , you just have a very improper tables design. ;)
I would strongly encourage you to look for a more intelligent import routine to store incoming quotes in normalized tables. Really, tables designed to satisfy incoming data format is like swallowing candies in wrappers.
Best regards,
Fish.
P.S. If you want to reconsider you db design, then start a new thread and post hyperlink to it here.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Hebar Tiltwobler |
last post by:
I need to figure out if the current date (passed in as a string) is equal
to or greater then a field in my database in the format of- M/D/YYYY AND if
the date is less then another field in my database.
"... WHERE '" & cdate(request("date")) & "' >= CONVERT(varchar,
(datePart(M,appointmentStart)/datePart(D,appointmentStart)/datePart(YYYY,app
ointmentStart)), 103)) AND "&_
"'" & cdate(request("date")) & "' <= (CONVERT(varchar,...
|
by: James Foreman |
last post by:
I'm building a revenue management tool and this requires some
understanding of how good my forecast accuracy is.
Therefore I'm trying to collect the mean absolute percentage error
(MAPE) on a set of predictions that I've been making; I have a table
that stores the actual result that occurred each day, and another that
stores the predictions that I've made on particular days for each day.
(All these predictions are integers).
My SQL is...
|
by: Sue |
last post by:
I'm working on a database that keeps track of employees hired by a general
contractor on a project by project basis. These employees are hired to work
on a project and are then laid off either at the end of the work they were
hired to do or at the end of the project. Any employee may be hired, laid
off and rehired several times during the course of time. The tables I have
and the critical fields to my question are:
TblEmployee
EmployeeID...
|
by: Massimiliano Alberti |
last post by:
Can someone check this? If it's OK, you can use however you want... :-)
It should search for an element in an array and, if it can't find it, return
the next element.
key is what you are searching, in a *base array of LONG with num elements.
After some tests it seems to work well (the original version was filled with
ASSERTs)
(I will use this function in a bigger look-for-or-insert function)
Some notes:
The function returns the index...
|
by: jty202 |
last post by:
I have string that contains a date in this format (14-Jan-05). I want to
store in date object if theres one and access each part of the date (month,
year, dates, day of week).
Specifically I want to convert it in this format 20050114 because this way,
I can compare it with another date in this format to see which is greater.
Can something show me how to do that. Thanks
| | |
by: PC Datasheet |
last post by:
Transaction data is given with date ranges:
Beginning End
4/1/06 4/4/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 4/30/06
I am looking for suggestions on how to find the date ranges where there were
no transactions.
|
by: Freddie |
last post by:
Hello everyone:
i am trying to compare a MAX(DATE) from one table that would be greater
than a date in another.
first_table
compname MYDATE
abc comp 2006-09-26 09:19:43.250
|
by: bml337 via AccessMonster.com |
last post by:
I need to create a supervisory log for my clients. The log table has the
following fields date, checkbox and notes. now for the tricky part... one
log is weekly and should be generated every monday. i have a similar log
that will be run on the first of each month.
so i need a record auto generated every Monday of the week and another
generated on the first of every month... these will be in two separate tables.
.. also possible to...
|
by: Harlequin |
last post by:
I must start this posing by making the point that I am NOT a VB programmer and I'm something of a Newbie to MS Access. I can program in a number of languages (Java, Javascript, PERL,PHP and TCL) but have never actually learnt VB so my request is that you bear this in mind if you plan on replying to this post.
My request for help is as follows:
I have an MS Access database in which one of the tables within it contains two date fields called...
|
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...
|
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: 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...
|
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: 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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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...
| |
