imported report is not in table format - how to format?

[NateE]

Hi,

I'm new to Access and hoping that someone can help me.
My data/report is in this format:
part# Location Qty
Part1 LocA 1
LocB 2
LocC 2
LocD 2
Part2 LocA 1
LocB 2
LocC 2
LocD 2

What is the best way to make this to a table wherein the parts are
associated to the appropriate locations? It is too much to copy them down due to large number of parts.

How can I make it show this way:
part# Location Qty
Part1 LocA 1
Part1 LocB 2
Part1 LocC 2
Part1 LocD 2
Part2 LocA 1
Part2 LocB 2
Part2 LocC 2
Part2 LocD 2


thanks,
Nate

 

[Dugantrain]

Ok, where is this report coming from? Excel, text file? Looks like you're going to be wanting two tables: a Parts table and a Part Details (or whatever you want to call it) table. And how are you importing? Are you just planning to copy and paste, are you using the import wizard? Give some more details and I'm sure we can help.

 

[NateE]

The data comes in as text file from our DRP system. I'm using the import wizard and I'm able to parse it correctly. Parts numbers are under the part header, locations are under the location header and quantity as well. The problem is how to repeat the part numbers so that it is linked to the appropriate locations and quantity

I can use excel to parse and use some kind of formula to repeat the part numbers but my data is too large, it is more than 66k lines that Excel can do. If you know how to increase lines in excel then I can probably use excel instead, else, I'm hoping that Access is my alternative.

Here's how it looks again. The original post did not take the spaces, the dots are spaces. Hope you can help.

Part Number....Location.....quantity
Pat1................Loc-A.........2
......................Loc-B.........1
......................Loc-C.........2
......................Loc-D.........1
Pat2................Loc-A.........2
......................Loc-B.........1
......................Loc-C.........2
......................Loc-D.........1

 

[NateE]

Hi,

Can anybody help please.

Again, my data comes in this way from a text file and I'd like to know how
to populate the table to repeat the part numbers corresponding to the locations.

Here's how it looks again. The original post did not take the spaces, the dots are spaces. Hope you can help.

Part Number....Location.....quantity
Part1................Loc-A.........2
......................Loc-B.........1
......................Loc-C.........2
......................Loc-D.........1
Part2................Loc-A.........2
......................Loc-B.........1
......................Loc-C.........2
......................Loc-D.........1

I'd like to know how to make it repeat the part numbers so that
it will look like a table, like this below:

Part Number....Location.....quantity
Part1................Loc-A.........2
Part1................Loc-B.........1
Part1................Loc-C.........2
Part1................Loc-D.........1
Part2................Loc-A.........2
Part2................Loc-B.........1
Part2................Loc-C.........2
Part2................Loc-D.........1

thanks,
Nate

 

[Philem]

Hullo. Quick and dirty solution would be to open said data file in Excel, then copy the part numbers down. Save the new file, and import THAT into Access. I do something similar with one of my reporting db's. CAVEAT: Excel can only deal with so much information, namely, 65,535 records. If your source file is larger than that, you will not be able to use this solution.

So, such as it is, HTH

P

 

[NateE]

Unfortunately, my file is more than 65K...I 'd appreciate any other ideas.

thanks,
Nate