Best 5 bids

  • Thread starter Thread starter JulieFlynt
  • Start date Start date
J

JulieFlynt

I have a dB that has two main tables not associated with a relationship.

Table One is baseline info from previous year with information about each
line item (i.e., Company, Line Item, Awarded Jobs, Cost, etc).

Table Two has bidding cycle information for this year. Same fields in each
table with the exception of the bidder for each line item is an additional
field. The secondary table has the bidding information for the next year -
field names the same.

I need a query to show last year's job cost, then show the best (lowest) 5
bids for each line item so that I can calculate the difference from last
year's cost to this year's project cost.

Is there any way to do this without having to know SQL? The person who had
this before did a manual report with SQL statements for each field and I
can't tell what he did because I'm not a SQL programmer.
 
I am afraid it will need some SQL to convey all that information to you as
otherwise it will be too time consuming for me.

It will take very little for you to use the PRE-MADE SQL.

Provide a list of actual table and field names with the datatype.

An easy way to do that is to create a select query with all the fields.
Open in Design View, click on VIEW - SQL View, highlight all in the window
that pops up, copy, and paste in a post.

Then say what is the datatype of each field.

Once I, or someone else build the SQL for you just copy, create query in
design view, select no table, click on VIEW - SQL View, paste the post. Save
the query.

Test the query.
 
P.S. I suggest you learn the rudiments of SQL. An easy way without books is
to create a simple query in design view and then look at the SQL, modify it
in design view and look again at the changes.

There a rules for syntax. The Action word are noted in upper case like this --
SELECT
FROM
WHERE
ORDER BY
GROUP BY
HAVING

The simplest query must have SELECT and FROM. WHERE is how you begin a
criteria statement.
 
If you have two (or more) tables with essentially the "same fields in each
table", you don't have a database, you have a ... spreadsheet!

Before you try to figure out how to get Access to work around data it isn't
optimized for, consider brushing up on relational database design and
normalization.

What you are trying to do is possible, but requires much more work if forced
to happen with your current data structure.

With data organized the way Access expects, it's a fairly simple matter.

Pay now or pay later!

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top