Stuff a value from a report into a query?

[Quintios]

I've been fighting with a query and I think I've found the solution. I have a main report that is created with the main sort on a number in a table called 'Sequence'.

When the report is being created, I need to grab the actual number of the 'Sequence' and stuff it into a query, and then the resulting query gets stuffed into the report (as a subreport).

(The reason I'm doing this is because I'm trying to get a crosstab query to work but the problem is there are different Column headers in each Sequence, so when I try to get a crosstab query to work I get ALL the instances of the column headers, instead of those that just pertain to that particular 'Sequence'.)

I hope this was clear... I didn't know what to search for so if someone can point me to a resource, or tell me what part of the help file to read, or WHATEVER. This is the most critical thing in this databse. If I can't get this crosstab query to work and print out I don't know what I'm going to do... :-(

thx!

Q-

 

[Blorf]

I have had this problem and have found a neat solution, but it takes some time to make it work. I will wait and see of someone smarter can help before I jump in. Will check back later.

Ascii dumb question, get a dumb Ansi

 

[Quintios]

No no no! Now now now! Phuleeeeeezzzzzz!!!


Were you having the same problem with the crosstab, or getting a value from the report into the query?

thx!

Q-

 

[Blorf]

The Problem I had was that each time I ran the cross tab, I got different header or field names, so having a report was useless. Sometimes would be differnet, or even not there, so the report would not run at all.

Is that your problem? If so, I will tell you what I did. This time, since dinner is done, I will stay by the computer for a while and see what you say.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[dhookom]

There are examples of dynamic crosstab reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Blorf]

The samples are neat. If they don't work for you, are are too advanced (They kind of confused me) let me know, and I will make my explanations. But Tomorrow, as I am rather sleepy, and I am having a great deal of difficulty thinking.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[dhookom]

I created the examples and have to review them to create new ones. The reports are very flexible and efficient. The code is minimal compared to that found in the Solutions.mdb or KB articles.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Quintios]

I'll try to explain the problem I'm having with the crosstab query and how I've sort of solved it.

The database I have is a development environment for programming elements called "Tags". One of the properties of a Tag is called a Sequence. A Sequence is a two digit number, like 10 or 18. All Tags have a sequence, no Tag can have two sequences, and all Tags are unique (thus they are my primary key.)

The wrench in the works is a special type of tag (there are seven types of tags) called a StepTag. The StepTag is a governing element in a Sequence. You can have many StepTags, but usually there are just 4 or 5. Every other Tag has an Action that the tag must take when the StepTag is evaluated as 'True' by the computer (the StepTags are mutually exclusive so only one can be true in a given sequence at a given time).

So I end up with a many to many relationship between Tags and StepTags.

When I run the crosstab query, I get all the StepTags as the column headers, instead of just the StepTags in one particular sequence. This makes sense as the query operates on all the Sequences. However, in order to create a query that will just result in the Tags to StepTags relationship I have to hard code in the Sequence number.

When I run the main report, it runs through several subreports all sorted by Sequence. When the report gets to the part where I want this crosstab query to appear, I want the value of the Sequence that the report is using to be put into the query, so the resulting crosstab will appear correctly in the report.

Make sense? *grin*

thx!

Q-

 

[dhookom]

The "issue" you describe is why I created the Crosstab report at the link I posted. Each "tag" can have its own unique set of "steptags". Once the report is setup, you can add, edit, or delete the step tags associated with the tag values and never have to change the design of any object.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Quintios]

Sweet, I'll check that out today. *grin*

thx!

Q-

 

[Quintios]

I'm studying what you gave me there dhookom, and the more I look at it (and maybe I'm missing something) the more I realize I'm going to have to create a temporary table somehow to make this report work.

The best way to describe my situation, referencing your database, is that you have a table of customers and there are *special* customers. The special customers go as column headers, and the regular customers go as the row headers. Contained therein is information as to how the regular customers are related to the *special* customers.

But then to make it more confusing, there are say, three groups of *special* customers, and there are different numbers of special customers. Your regular customers are related to only one of the special customers.

So the crosstab reports, when you create them, are going to have different numbers of columns because of the differing numbers of *special* customers. Since in this example I'm using the number of "three", there would be three cross tab reports, each with different numbers of columns, but the crosstab report is using information from ONE TABLE ONLY.

I'm going to study your examples more, but on the surface I don't believe they apply to my situation.

I think the best solution for me is to create another table called "special customers" just for the purposes of this report. Now I just need to figure out how to create a table on the fly during a report. *gah*

Blorf, are you catching this? Does it make any sense to you?

thx!

Q-

 

[Blorf]

I think so.

My situation was this. I had a sales order, and a BOM. The sales order has model numbers on it. I needed a report that would take the models on the sales order, and use them as my report headers, and list the components on the BOM's on the left, and put the quantities required under the correct model numbers.

As sales orders are new every time, the model number headers were constantly changing, and the number of models also changed from sales order to sales order.

This is what I am talking about. How to make a report not care and just do it.

I think that your problem is similar. Want I should walk you through how I did it? It is long, but fairly easy to understand. And I am absolutley certain that it is not the best method, but it works.






Ascii dumb question, get a dumb Ansi

 

[Quintios]

I'd love to hear how you did it. I'm playing around with dhookom's sample database and trying to correlate it to my application (always the hardest part). It's definitely a useful example for other situations, but perhaps not mine. I don't know quite yet.

If you want to email me you can at quintios at hotmail.com. Or post stuff here. Either way I'll see it.

thx!

Q-

 

[Blorf]

I sent a small sample database to your e-mail. Take a look. Hopefully what it does will be clear.

ChaZ

Ascii dumb question, get a dumb Ansi