queries with multiple tables (1 Viewer)

[Sandrra]

hi! I need help, please.

I have multiple tables, say 3 tables

table 1 table 2 table3
date from date from date from
date to date to date to
place place place
product product distance
price customer price
price

I want to combine all the data from the 3 tables and get the sum of all the prices with a given condition that the query date should be within the range of date from and to of all tables. Worst scenario, all date from and to are not equal.:banghead:

Hope someone could help me.

 

[Galaxiom]

Looks a lot like the records from your tables should all be in the same table with another field to indicate the attribute that caused you to use separate tables.

Then you will find it much easier.

Otherwise use a Union query to get all the data combined.

 

[Sandrra]

I am trying to build this in one table but the problem is the effectivity (date from and to) of each pricing components are different.

 

[Galaxiom]

Pricing components? I think you had better describe your data structure a bit more.

 

[Sandrra]

Thanks for your time, I really appreciate


let's say table 1 is the product cost, table 2 is the specific pricing per customer and table 3 is the transport cost. product cost change frequently - it can be daily, while the 2 tables changes seldom. Now, I want get the pricing components with their corresponding price depending on my query date.

For me to get current prices, I add another field to indicate which one is active, by yes/no. My problem now is what if I want to get old prices.

 

[Galaxiom]

let's say table 1 is the product cost, table 2 is the specific pricing per customer and table 3 is the transport cost. product cost change frequently - it can be daily, while the 2 tables changes seldom. Now, I want get the pricing components with their corresponding price depending on my query date.

For me to get current prices, I add another field to indicate which one is active, by yes/no. My problem now is what if I want to get old prices.

One would not normally record the active Yes/No. The currently active price should be able to be determined by the date fields and as such the Active field is a breach of normalisation.

You would get the maximum date that is less than the query date in a Group By product query.

I am still not able to fully understand what you are trying to show. Ideally post a cut down sample of the database and an example of the output you require would be good.

 

[Sandrra]

Hi! Here's my sample database and the output I wanted to produce. I hope you can figure it out how this will be done.

Thanks, thanks so much.

Cheers!

 

[dylan_dog]

Hi,
I'd include field SIDE ID to TABLE1 and TABLE2 and drop distance from TABLE2 or SITE MASTER. Than you can make joins on SITE ID between your tables and create query based on SITE ID and dates.

Also, you'll find it much easier in the future if you don't use spaces in your field names and have different names for your dates in different tables .

I put something together, is this what you need? I'd also like to hear from everybody else if I did it the right way.

Regards, Samo

 

[Sandrra]

Hi Dylan_dog, Thanks for this and so sorry was not able to immediately reply as I was out for 4 days without internet connection.

I also got the same result as yours. Is it possible to limit result to a list of products of each customer (no duplicate product) pulling last price of each table prior or equal to cost date?

 

[Sandrra]

Hi Samo,
I am suppose to upload sample file but failed. Can I edit your Access DB for me to show what I wanted to get? Thanks!

 

[dylan_dog]

Of course

 

[Sandrra]

Is it advisable to eliminate or delete field Date to from all tables?

 

[Sandrra]

can someone explain how the expression "LAST" works. Does this mean it will only get the last record, or last record of each customer and product? I tried to use it, some are correct but some are not. instead of last record, it gives me the first record of my product?

 

[Sandrra]

I want to limit my result to a recent date, either prior or equal to given date?

 

[Sandrra]

I am trying to attach sample data but the system doesn't allow me to upload it. Sorry

 

[Sandrra]

I'm not sure if this link will work. This link contains zip file of my sample dbase...

https://www.dropbox.com/home#!/home/Dbase

thanks and hope to hear comments and suggestions...

 

[dylan_dog]

Not eliminating the dates as you probably need them. Just rename them to something meaningful like products_date_from and pricing_date_from, since it is confusing (At least for me. I did the same thing once, and was lost with names when I wanted to modify my own queries and forms a week later) to have two fields with the same name in your query. The same goes for fields price, it could be product_price and transport_price.

I sent you my email on PM, just send the files over and I can try to upload them.

 

[dylan_dog]

Sandrra,

I have some questions:

-how often are the prices changing?
-do prices for all the items change on the same date or can you change the price for every product independently?
-how many products are there?

The problem I ran to is that your dates in all three tables are overlapping and it is very hard to pull the data out for a specific period because you can have many price changes in that period.

 

[Sandrra]

hi Samo,

Answer to your questions:
1. price changes can happen everyday or at once a week.
2. no, can change on a per product and it can only be one component of prices (product cost, or freight cost or discount)
3. maximum of 20 products

And yes, its true dates are overlapping. Can this be possible to be done in MS Access?

Thanks again, Samo!