Search results

This also hasn't addressed the issue of showing all of the categories and I don't know how to create a union with "dummy data". I tried searching online but I've only found requests for people to post examples of their database populated by dummy data. I thought about creating a dummy table...

Ok, why is this so hard? I created the select queries as you directed and each one properly pulled the data. Here's the code for the month: SELECT [Event Information].Program_Code, Format([Date_of_Event],"yyyy mmm") AS PivotColumn, Count(*) AS CountColumn FROM [Event Information] WHERE...

I'm sorry, namliam, I'm still confused. How should the year and month select queries be different? I know how to create a select query that pulls the events from the current year. But without using a select query for each specific month how is the YourMonth query different from the YourYear...

Namlaim, I'm not quite sure what you mean. Are you suggesting that I create one select query for each month, then one for the year, then a union query to join them? Then would I be doing something like this in the crosstab? TRANSFORM NZ(Count([Event Information].Event_ID),0) AS [Number of...

Okay, so I found a starting point for doing what I want with a crosstab and I'm starting to get a hang of it. This query is written to count the number of programs in each month, broken down into program categories. Here's the sql: TRANSFORM NZ(Count([Event Information].Event_ID),0) AS [Number...

Sorry, what I meant to say was one column per time frame (Annual, January, February, etc.) with 8 rows in each, like a traditional spreadsheet. If done properly, that's what I'd be able to get, right?

It may be a little bit before I have time to but I'll give cross tabs another shot.

It would be better to have a column with 8 rows. That's actually why I tried crosstabs first and then just gave up. I was planning on putting it into a report that users would see. That's why I figured I'd still be able to use 1 row with the unions. Would I still be able to do that with the...

Would you just need the table & relevant queries? I could probably post that without too much effort, so long as you don't need forms, etc. As for the number of fields, I know it is a lot, but it's a lot of info that I need to keep track of. The Event table keeps track of 8 different kinds of...

Okay, so I tabled this query and started working on another that would also be used as an annual report. But now I have a similar issue with this new one. It tells me that the system resources are exceeded. This is new query, called Annual Number of Programs Report, is just a select query...

Does anyone have any further input or ideas? Thanks.

Oh, and again, if I remove one of the joins from the Annual Income Report Union it works fine. So still it's only 12 joins that work, when it should be 16.

And, yes, in hind sight (even before this issue), I realized that using BOCES_PO instead of BOCES_ID was not good practice and redundant, since when establishing the link I set both fields to match between the two tables. I just didn't want to go back and change things because I was worried...

True, but unfortunately I won't be able to share that with a screen shot. The Event Information table has 143 fields (not all are used in each record, but each one still needs a lot). At most I'd be able to size things to include about 40 of those, and most of the ones that would be visible...

The only part where the BOCES Invoice table comes into play is the second SELECT statement in the Annual Education Income Union's SQL, which I posted above.

Here's a picture of the relationship page. The main table is Event Information and the second table that is being referenced in this case is the BOCES Invoice table. I'm not sure what you meant by asking if it was a new condition requirement.

No, all of the records are in one table. The reason why I've had to break it up is because, depending on the content of each record, there are 8 different ways of calculating the income (one of those conditions does require referencing information in a second table). I therefore created a...

[Solved] System Resources Exceeded Error I am in the process of writing a union query but have come upon a road block; when running it I get a warning stating that "System resources exceeded." I read that this can happen in Access 2007 (which is what I'm using) if there are more than 16 joins...

I created a trial copy of the two queries and started experimenting with that. The problem with that is it selects the last of the events, which is the event currently open in the main form. I want the previous event in the subforms. I solved the same day issue by replacing the Date_of_Event...

[Solved] Query to Pull Previous Record I have a form that is used to book a new event for a client who is already in the database. Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the...