income expenses query not showing all records

[PNGBill]

View All Sales and View Sales by Customer should be able to use the same query and Report with the code behind the Customer button doing the filter work. Monthly Sales History only needs the date included in the earlier two, which it most likely is and you can have all three using the same query and report but different filter with the button code.

Profit and Loss per product, per month and total should also be the same basic data (query) and just filtered.

Discontinued Inventory is separate but I guess in time you will have other Inventory Reports and a similar issue may arise.

Another note on Forms and Reports.

When you have your Query settled down, view it in sql view. Copy and past it where you have the Data Source in your Form or Report Properties.
If your version works this way.

This means that instead of the Form or Report looking for your query to get it's data, it has the query as part of it - don't know the technical terms for this.
You then delete the original query - after testing the form or report.

Your data base will be tidier and your forms and reports more portable should you require to Import or Export them say when working on a copy to make improvements etc.
When making any changes, if you want to see the query as in design view, you just copy and past the sql back to a new query, in sql view and then view in design view.

 

[Nicolette]

Ok thanks for the tips I will make the adjustments but this doesn't solve the problems I'm having with my query.

 

[PNGBill]

Ok thanks for the tips I will make the adjustments but this doesn't solve the problems I'm having with my query.

I thought your query problem related to the date criteria.

I you output all records then your query should work.

Let the code handle the date issue, provided the date field is included in the query.

 

[Nicolette]

Maybe I misunderstood. It seems to be date related yes. So you are suggesting using the filter in the command button to display the records I want right? but I need to be able to enter the month and year that I want. and If there are no sales I want just listings if there are sales I want both the listing and the sale also some sales will not happen durning the same month as the listing so even if there is no listing I still need the sale to show I can't seem to get the query to even do that much not to mention the date problem.

I hope that made sense thanks!

 

[PNGBill]

Maybe I misunderstood. It seems to be date related yes. So you are suggesting using the filter in the command button to display the records I want right? but I need to be able to enter the month and year that I want. and If there are no sales I want just listings if there are sales I want both the listing and the sale also some sales will not happen durning the same month as the listing so even if there is no listing I still need the sale to show I can't seem to get the query to even do that much not to mention the date problem.

I hope that made sense thanks!

Just as your Button is One click Now, so it will be in the future. If you have a Field in your query where data is requested then that question should still Kick In.
Another way is to include an Input Box into your code which will ask some or all of these questions and use the response in the filter but as above, your query is still being used so if it now asks a question then it will do the same how ever it is called.

You may need to step back from your query and start again.

You want All Sales made since the beginning of time.
You want the Date, The Invoice ID, The Cust ID etc.
But essentially it is all sales.

Starting point is to write down how many sales records you have and as you build the query check that this num remains constant and where it differs, back track and find where you went wrong.

Two common mistakes are:
1. When you bring in another data source (table or Query) and join it, the record count increases or decreases - this is because the join is not correct. eg All records in Table A and Only those records in Table B that match is Different then the reverse.

2. Nulls You will find this to be a constant worry to you until you get into the habit of being one step ahead of the beast.
The most common tool in dealing with Nulls is Nz([fieldname],0) in your query. Do this Just in Case so you get a Zero instead of a Null.
This ensures you get All sales records even though some have not yet been Paid which will not be the case if you bring in the Payments Table and make the join as equal, any unpaid sales will disappear.

Remember, keep an eye on the record count at all times and the query should work.
If you do want to restrict some records, then make a query first to get the count of these so when you restrict in the main query, you ensure Only the records you want restricted are restricted ie the record count matches what you expected it to be.

 

[Nicolette]

ok so since for testing purposes i'm only using dataview of the query at this point when I run the query I want to see all Listings for a monthe and a year that I enter. If there is a sale off one of those listings I want that sale information but the sale is during that same month and year. as far at the sale information all I want is the OrderID, Picture, the SKU and the Title of the item the rest of the fields are expenses and income that are calculated from other queries

I haven't been using Nz([fieldname],0) honestly I forgot about it this shouldn't apply because the records that are missing information like you can see in my first image shouldn't be missing information because these figures are already calculated and are
not null. so I'm not sure why they are missing.

I have rewrote this query a number of times already and I keep ending up at the same point Missing information and/or date problems as in either records i don't want show or it shows no records at all. So far the record count has been correct there should be 14 for December 2006 and that is what I get. but it also give me sales I don't want and there is the missing information.

Also I don't want each listing (some SKU's have many listings) I want each SKU and a count of times that SKU is listed Which I have figured out but sometimes it shows each listings
Sales are secondary in this query.

 

[PNGBill]

Doesn't matter what you are looking for, you have a starting point.
If this is Inventory, Sales, Customers etc.

It appears the problems is more complicated then it should be.
ie. If you have a query that works and then it doesn't, there is just one issue that should be in question.
If you have two queries that both work,but when joined, they don't then it is either an issue with the Join or the join and Null.

I have just changed some of our database where I had a squiliane queries and now just use one.
I have also, as much as possible, tried to have the queries only use tables.

When you have queries 6 deep, just make one change to a seemingly obscure query and who knows what problems arise in what Forms and Reports.

It may seem like an impossible task now but it can be done.

Back to your issue now.

Try and output as many records as possible and then do the restricting. ie don't worry about what month, just return all records and get it to work and then edit the query to restrict dates and or use vba code.

Also, when you view your query in sql view it can easily be posted in the forum between the ## symbol next to the php symbol and advice can be sought on where you may have gone wrong.

 

[SOS]

I think this would get resolved much quicker if we just had a copy of the database to play with.

 

[Nicolette]

I know it needs a ton of work.... right now i'm still developing and testing and I haven't cleaned anything up so there a some unused queries and such... I have tried to keep it normalized but no promises. There are still some things I would like to put into the DB eventually but If I can get the basics running then I worry about all the extra's I want. I am all ears for suggestions but the look is not a top priority.

 

[SOS]

Remember to specify which forms, queries, etc. we should be looking at for this particular problem.

 

[Nicolette]

Query 2 is the one I have been working on. the form with the buttons is frmViewAvaliableReports

 

[Nicolette]

Oh I am so frustrated!!! I have officially tried everything that my limited knowledge allows.....

 

[PNGBill]

Oh I am so frustrated!!! I have officially tried everything that my limited knowledge allows.....

What limited knowledge are you referring to??
The knowledge you started with a couple of months ago or the knowledge you have now??
I find sometimes a new day brings a fresh idea.

Get over this issue and your so called limited knowledge will be enhanced limited knowledge

 

[Nicolette]

I know fresh eyes Oh my knowledge has absolutly expanded from when I started.

 

[Nicolette]

Well I still haven't figured this out so I guess i'm just gonna drop the idea for now. If you do have any ideas let me know otherwise.... Thanks for your help!

 

[PNGBill]

If you are trying to output data that comes from more then One Table And the data doesn't Merge then you possibly need a Union Query.

This is where say you have 130 records from Sales and 150 from Expenses and you want to combine these to be 280 records that will be used in your Report / Pivot Chart. A Union Query does this where as a standard Select Query will want to Merge the data ie you will get 150 records and some of these will have sales data.

 

[Nicolette]

This is where say you have 130 records from Sales and 150 from Expenses and you want to combine these to be 280 records that will be used in your Report / Pivot Chart. A Union Query does this where as a standard Select Query will want to Merge the data ie you will get 150 records and some of these will have sales data.

Yeah i played with a union query and i don't think it is what i'm looking for I'm pretty sure that the merger of a select query is what i need....