Fill Table control (1 Viewer)

[Gismo]

Hi,

Attached I have a Exell spreadsheet extracted from out maintenance [program
On the right is a field called Interval ID, Value "O" is a header it, all the other ID's is used in the detail
i need to run a report but would sort and group by description headed and then by position, main group and so forth

i want to fill the header coulomb automatically with the value of description and interval ID = O
all the other descriptions below must have the same value in header until the interval ID becomes O again then the header must change to the next value in Description

hope it makes sense
is there an easy way to do this in Access?

 

[jdraw]

There's no content in the zip when I open it??????

 

[Gismo]

please try again

 

[jdraw]

I don't understand the requirement, and I'm not an Excel user.

 

[Gasman]

So why isn't HYDRAULIC PUMP - A5015280 in column G?

All your headers appear to have an item number of zero, so use that and the other fields to arrange the data as you want it.?

 

[Gismo]

So why isn't HYDRAULIC PUMP - A5015280 in column G?

All your headers appear to have an item number of zero, so use that and the other fields to arrange the data as you want it.?

you are correct, Hud pump is the odd one out and the planner needs to have a look at the entry in the program, it seems to be the only odd one out
most of the headers seems to have the Item number as zero yes, but that is not the rule of thumb, there are quite a few other entries where Item number does not represent the header

if this was the rule, it would have been easier

filter by zero item number. there are white and green highlighted as well

 

[Gasman]

Not really the odd one out.? If you go further down the sheet there are plenty or more 'headers' with no value in G?

You have to get the data stable before even thinking about this?

I made a request in my last place of work to the firm that controlled the database behind the web app, to have a report, which denormalised the data, so I could create my own reports. That meant duplication in the rows (like you have now, but just for a few rows)?

Only YOU know the rules, but it is not beyond reason to ask for the output to be changed so you are able to do what you want, rather than having to bodge the data to get the same result.

Though you could perhaps get your report headers from Description1 alone?

 

[Gismo]

Not really the odd one out.? If you go further down the sheet there are plenty or more 'headers' with no value in G?

You have to get the data stable before even thinking about this?

I made a request in my last place of work to the firm that controlled the database behind the web app, to have a report, which denormalised the data, so I could create my own reports. That meant duplication in the rows (like you have now, but just for a few rows)?

Only YOU know the rules, but it is not beyond reason to ask for the output to be changed so you are able to do what you want, rather than having to bodge the data to get the same result.

Though you could perhaps get your report headers from Description1 alone?

View attachment 87203

my request might have been unclear to start with

Description 1 - H, is where i need to get my header from
G is there I want to copy to

the first few lines in G was the sample as what needs to be done automatically by access

 

[Gasman]

I would probably have to do it via a recordset, where I store a header when IntervalID is O, then read and update until the next O and then store again and repeat until end of file.?

Perhaps even do it in Excel?

 

[Gismo]

I would probably have to do it via a recordset, where I store a header when IntervalID is O, then read and update until the next O and then store again and repeat until end of file.?

Perhaps even do it in Excel?

yes, i have no ide how to do a record set
doing it in excel would also not be the preferred way as i have quite a few spreadsheet which unfortunately i can not download in to one spreadsheet

 

[Gasman]

You *might* be able to do it via a query, where you set the field to a value where you use a sub query to select the value based on all your common fields?

As for multiple spreadsheets, firstly are they in the same format, if so I would use code in my Personal workbook or a common workbook.?

 

[Gismo]

You *might* be able to do it via a query, where you set the field to a value where you use a sub query to select the value based on all your common fields?

As for multiple spreadsheets, firstly are they in the same format, if so I would use code in my Personal workbook or a common workbook.?

i will try with a query

all the spreadsheets will be in exactly the same format

 

[Gasman]

Try an Update query where you bring the table in twice and match each of the unique fields.
I'd tidy up the header names as well as Access complain on an import.

 

[Gasman]

Expand on this, and do not forget the O

UPDATE LTB, LTB AS LTB_1 SET LTB.Field7 = [ltb_1].[Description 1]
WHERE (((LTB.[Assembly Group 1])=[ltb_1].[assembly group 1]) AND ((LTB.[Assembly Group 2])=[ltb_1].[assembly group 2]));

generated from

 

[Gismo]

Expand on this, and do not forget the O

UPDATE LTB, LTB AS LTB_1 SET LTB.Field7 = [ltb_1].[Description 1]
WHERE (((LTB.[Assembly Group 1])=[ltb_1].[assembly group 1]) AND ((LTB.[Assembly Group 2])=[ltb_1].[assembly group 2]));

generated from

View attachment 87205

it just does not want to do as i need it to do

 

[Gasman]

I'm not sure your data is correct even?
On what I *thought* it was I created a query which updated 750 rows, but then spotted this

My query was

UPDATE LTB, LTB AS LTB_1 SET LTB.Field7 = [ltb_1].[Description 1]
WHERE (((LTB.[Main Group])=[ltb_1].[Main Group]) AND ((LTB.[Assembly Group 1])=[ltb_1].[assembly group 1]) AND ((LTB.[Assembly Group 2])=[ltb_1].[assembly group 2]) AND ((LTB.[Assembly Group 3])=[ltb_1].[Assembly Group 3]) AND ((LTB_1.[Item Number])=0) AND ((LTB.[Position No#])=[ltb_1].[Position No#]) AND ((LTB_1.[Interval ID 1])="O"));

I was expecting the O header to have a 0 item number, at least it looked that way?
So as I said, make sure your data is correct before working on it?
Personally I'd tell them to do it, it would be eaiser each way, as at the time of creation, they would know the value?, same as if you were outputting the data.

This as far as I am going to go?

 

[MajP]

This may be of interest, but not sure what you are doing.

Access Fill Down to Emulate Excel Fill Down

Recently there was a user trying to emulate the Excel fill down functionality based on imported data. I am re-posting this solution here since I could see some utility and this may be a common issue. The OP mentioned doing several web searches and not finding an easy solution. The solution...

www.access-programmers.co.uk

 

[Gismo]

so what i did so far, if a description has a interval of "O", i copy that value in a new field called Header, next to the description
so basically, as i understand, in a record set, i need to copy the first record in header and then paste it in header control until it finds the next value in header, then copy the next value and replicate again until next value, true?
please could you advise on a code to do that?

 

[Gismo]

I'm not sure your data is correct even?
On what I *thought* it was I created a query which updated 750 rows, but then spotted this

View attachment 87208

My query was

UPDATE LTB, LTB AS LTB_1 SET LTB.Field7 = [ltb_1].[Description 1]
WHERE (((LTB.[Main Group])=[ltb_1].[Main Group]) AND ((LTB.[Assembly Group 1])=[ltb_1].[assembly group 1]) AND ((LTB.[Assembly Group 2])=[ltb_1].[assembly group 2]) AND ((LTB.[Assembly Group 3])=[ltb_1].[Assembly Group 3]) AND ((LTB_1.[Item Number])=0) AND ((LTB.[Position No#])=[ltb_1].[Position No#]) AND ((LTB_1.[Interval ID 1])="O"));

I was expecting the O header to have a 0 item number, at least it looked that way?
So as I said, make sure your data is correct before working on it?
Personally I'd tell them to do it, it would be eaiser each way, as at the time of creation, they would know the value?, same as if you were outputting the data.

This as far as I am going to go?
View attachment 87209

we will be decommissioning the program soon so i will net get any assistance to get a report with the data i need

thank you for your assistance, much appreciated

 

[Gasman]

@MajP
Gismo is trying to populate detail records with the data from the relevant header, but his data (well to me at least) does not appear consistent?