Query calculations with unrelated tables

[SoCal]

Hi All,

My DB has an Order Details table that uses dynamic combo boxes to fill in criteria from my form. The Oder Entry form uses a combo box to select the type of product, this generates specific choices for values in the next product combo box (from a product table), and so on. The result is that my Order Details table is populated (for some values) by user selection on the form.

The Product table (where the choices of the combo box) is not related to the Order Detail Table, it just get's values from there through the use of the form.

Now, in the Product table, I have the type of Product and the Cost of the Product. I now want to make a query that will pull that product type with the cost for calculations.

I know this would be simple if the tables were related, but they are not.

Any help would be appreciated, and I hope all of this makes sense.

Thanks!

 

[jzwp22]

The Product table (where the choices of the combo box) is not related to the Order Detail Table

If there is no relationship between the order detail table and the product table then how can you create an order with any products? There would need to be some relationship; something like this perhaps:

tblOrderDetails
-pkOrderDetailID primary key, autonumber
-fkOrderID foreign key to tblOrders
-fkProductID foreign key to tblProducts
-QuantityOrdered
etc.

I have to assume that you have a field such as fkProductID in your order detail table but of course you would not have the product type or description fields for the product. You can get that information with a query that joins the two tables.

Perhaps providing your table structure will help us better understand your problem?

 

[SoCal]

Hi and Thanks for the response! My OrderDetails table has some of its product specifics populated in a form from info from unrelated tables. After a bit of research, can I use the DLOOKUP function to get info from these unrelated tables into a query so that I can do some calculations? If so, I would need some help setting this query up.

Thanks!

 

[jzwp22]

In order to help you use the DLookup function, we will need to see the fields in the orderdetails table as well as the fields in the unrelated tables. You will also have to tell us which fields in the orderdetails table have matching info in the unrelated table. (BTW, if a field in one table matches the info in a field in another table that is what forms a relationship, so I think you may actually have related tables, you just might not have them joined). The query will only rely on what is in the tables not via the form that was used to get the data in the table.