Streaming Field Value via Query

[mindlessclone2003]

All,

Right now I have a report that I'm linking into access. The report is set up strange and I'd like to do everything from within access rather than creating a small excel macro. I have an agent ID column and I have an agent detail column. Here's where it gets tricky. I only have one record w/ an id compared to like 10 for the details. Is there a way to fill the id field down until I get to another record w/ an id? Perhaps this example will make my question clearer...

WHAT I HAVE:

HunterJ 534 Fake St
Orlando, FL 22394
SmithM 251 WhereAbout Ave
Pittsburgh, PA 34459

WHAT I'D LIKE

HunterJ 534 Fake St
HunterJ Orlando, Fl 22394
SmithM 251 WhereAbout Ave
SmithM Pittsburgh, PA 34459

 

[scriverb]

Check out the property Hide Duplicates of the agent ID column control. It is probably set to Yes. Change that to No and the value of the agent ID will appear on each line.

Let me know how that works for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mindlessclone2003]

Thanks, though that doesn't work. The id's aren't actually in the table. I want them to fill in as if they were in the table. The more I think about this, I'm thinking I'm going to HAVE to run a vba script on the report prior to linking it. Thoughts???

 

[scriverb]

Are you using a query as the Record source for your report? If so you can use the ID from the one table each time the many side of the relationship details records are displayed. Why don't you post your SQL from the query here and let me take a look at it.

Also, in your report are you displaying all of the data in the Detail Section of the report or are you using a Group Header to display the ID? If that is the case we can just move that field down to the Detail level to make it show on each line of the report.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mindlessclone2003]

Ok, I'm not actually even creating a report off this query. What I'm actually trying to do is set up the tables via queries which will be linked to my sql server. I created such a function w/ excel, but because I had more than 65526 records in a couple of the reports, by reports I mean reports that are used to generate my tables, I had to delete things on a daily basis which I really needed. To start out I download these canned reports automatically from a report generator called document direct. They download in a format (TXT) such as I mentioned in the first post w/ the ID then 10 lines of information, then another ID and 10 lines and so on. So I have these linked tables that are in that format, and via queries I'd like to create tables, with the information housed as I explained, so that I can then have the finished tables linked to sql for my ASP pages...

 

[scriverb]

Okay, I think I understand. These Txt files have multiple rows for an ID but the ID is only on the first record of the 10. Do you want this data imported into ACCESS and an ACCESS table created with the intended format? Are you don't want all of the data on the same record. (i.e. ID, Address, City, State, phone, etc. ) All in the same record.

Are each of the txt files to be kept seperate or merged together?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mindlessclone2003]

Exactly. Yes I want to have the data imported and then have a table created w/ the intended format. I would be happy with either all the information on 1 record or multiple records w/ the ID on all, and I'd like to keep each seperate. Thanks...

 

[scriverb]

So that I can be sure this works for you. Send me a sample txt file to the email address in my profile. I will then fashion a solution for you and post it back here. If it is proprietary information just create a txt table exactly in the format that you want to import and create the table with.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???