confirming all prices same

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » Confirming all prices are the same for multiple dates

Confirming all prices are the same for multiple dates

resolvedResolved · High Priority · Version 2016

Manny has attended:
Access Intermediate course
Access Advanced course

Confirming all prices are the same for multiple dates

Hello Guys,

Hopefully an easy one, but am struggling to figure this one out.

I have a table with lots of pricing information for multiple portfolio. Essentially what is crucial to my database is that the price for each holding (which could be on multiple funds) must be the same for each respective day.

eg. price of asset XXX must be YYYY for date ZZZ. The price should be same in any portfolio, but this is a query to check that the prices are the same for any given date. i would like the query to return the exceptions.

Please help :)

Manny

RE: Confirming all prices are the same for multiple dates

Hi Manny

Which programme are you using to do this?

Kind regards
Wendy Canelas
Microsoft Office Trainer

RE: Confirming all prices are the same for multiple dates

Hi Wendy,

Access :)

Manny

RE: Confirming all prices are the same for multiple dates

Hi Manny,

You need a query criteria asset =xxxx criteria price NOT yyyy and date yyyy. This will give records with a wrong price




kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Confirming all prices are the same for multiple dates

Hi Jens,

I think i have done a bad job at explain this. Let me give you an example of a table

DATE Price ID Portfolio
01/09/2018 10 abc A
01/09/2018 10 abc B
01/09/2018 10 abc C
01/09/2018 15 abc D
02/09/2018 20 xyz A
02/09/2018 20 mno A

As you can see for date 01/09/2018, we have 4 line item for ID abc which are in different portfolios. We can see that in portfolio D however the price is 15. This is an incorrect price and i would like the query to return ID abc has different prices for the same date (regardless of portfolio)

Thanks, Manny

RE: Confirming all prices are the same for multiple dates

Hi Manny,

Criteria for Date column #01/08/2018# criteria for price NOT 10.


If you have many days you want to test you will need a table with days and the right prices. Create a multiple fields primary keys. And use Price and Date as primary key in the table with the correct prices. Create an unmatched query between the table and the query with your factual records.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 10 Sep 2018: Automatically marked as resolved.


 

Access tip:

Related tables

When you have related tables such as Customers and their Orders, the Customer table is the Primary table.

Open the Customers table in datasheet view and go to the Home Tab and Records group. Click on the More option and choose Subdatasheet and then click on Subdatasheet again. Now choose the related table (Orders) and click ok.

Now you can click the + symbol by each company to show the related orders.

View all Access hints and tips


Server loaded in 0.07 secs.