Gents I really do not have an Idea how I could do this thing. Have an excel sheet which I receive from my customer and I get imported to Access and I would call that table tblCustomer and that would look something like this:
ProductID | Name | Expire date | SumofQty
3 | Flour | 13-Dec-2013 | 6
6 | Meat | 20-Jan-2014 | 10
So the table contain maybe 100 items. I want in the same table or another table to copy the same record 6 times as per SumofQty and than the next record copy it 10 times and so on. I need this cause I will create labels for each product right now I`m doing manually :((( Please if someone has an Idea how to do it :(
So, just to be clear,
from your example data table
you need:
6 lables that have PrdID3, Flour: Exp13-Dec-2013,
10 lables for the meat
and so forth.
You are receiving this information via an excel workbook.
Is this correct?
[edit]
If so, then you really don't need VBA to do this.
Create a table with a single field, set as primary, type cast as Number(long)
Enter a number of records... say 1 thru 20
call this "tbl_repeater" name the field [repeater_pk]
Now in a QUERY!!!
show the imported table, show the repeater table,
add the fields you want shown from the import
add the repeater field;however, uncheck show
what we will do is take the cartesian product between this repeater table and your imported table and limit the records returned against [tbl_repeater]![repeater_pk] by returning only those [tbl_repeater]![repeater_pk]<=[tbl_import]![SumOfQty]
Save this query,
use it to feed your report.
-z
3 1313 zmbd 5,501
Expert Mod 4TB
So, just to be clear,
from your example data table
you need:
6 lables that have PrdID3, Flour: Exp13-Dec-2013,
10 lables for the meat
and so forth.
You are receiving this information via an excel workbook.
Is this correct?
[edit]
If so, then you really don't need VBA to do this.
Create a table with a single field, set as primary, type cast as Number(long)
Enter a number of records... say 1 thru 20
call this "tbl_repeater" name the field [repeater_pk]
Now in a QUERY!!!
show the imported table, show the repeater table,
add the fields you want shown from the import
add the repeater field;however, uncheck show
what we will do is take the cartesian product between this repeater table and your imported table and limit the records returned against [tbl_repeater]![repeater_pk] by returning only those [tbl_repeater]![repeater_pk]<=[tbl_import]![SumOfQty]
Save this query,
use it to feed your report.
-z
Ok that is what I ask and I got it. worked like a charm. but can I have an autonumber for each repeater like for each record have another column and have:
ProductID | Name | Expire date | SumofQty |Autonumber
3 | Flour | 13-Dec-2013 | 6 | 1
3 | Flour | 13-Dec-2013 | 6 | 2
3 | Flour | 13-Dec-2013 | 6 | 3
6 | Meat | 20-Jan-2014 | 10 | 1
6 | Meat | 20-Jan-2014 | 10 | 2
6 | Meat | 20-Jan-2014 | 10 | 3 and so one
zmbd 5,501
Expert Mod 4TB
Yes, where I had you uncheck show, check mark it instead. Run the query, you should now see the [repeater_pk] so long as this is sequential in nature, you should get the numbering as desired.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: tilak.negi |
last post by:
We have one single hash (#) table, in which we insert data processing
priority wise (after calculating priority).
for. e.g.
Company Product Priority Prod. Qty Prod_Plan_Date
C1...
|
by: Hi5 |
last post by:
Hi,
I am designing a database for a client in which It has a client table
including the followings:
1-Table Client
2-Table lookupcategory
3-Table Ctegory
|
by: Hyphessobricon |
last post by:
Hallo,
Indeed, a count of a query with a group by function gives more records than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact....
|
by: Swinky |
last post by:
I hope someone can help...I feel like I'm walking in the dark without a
flashlight (I'm NOT a programmer but have been called to task to do
some work in Access that is above my head).
I have...
|
by: dmon000 |
last post by:
I have a form that saves a diet order for a patient in an "Order Detail" table. Presently it saves one record for one particular meal (breakfast, lunch or dinner).
Management now wants 3 records...
|
by: ahmadbaseet |
last post by:
Hello,
How can I get more records with DAO.
I write
Dim rs As DAO.Recordset
'selecting data through SQL then
with "rs("Fieldname")"
I can get the data if only 1 row is back from SQL query....
|
by: suren1503 |
last post by:
Hi All Techies,
I have created one DTS to copy table1 (which is on Oracle server) to table2 (on SQL Server 2000)
I want to copy only new records + modified records from table1 to table2.
I...
|
by: busterbaxter |
last post by:
Hi All,
I'm farely new with access so this question may seem really simple. I have an existing inventory database that I'm trying to make changes to. The database only has 1 table with the...
|
by: jecintah |
last post by:
I have a datagrid and this datagrid holds only two records.How can i span it to make it hold more records.
Any help will be highly appreciated.
Thankyou
|
by: shubham rastogi |
last post by:
hello guys I want to copy or insert records into the previously created table from another table..
For example I have two tables A and B ....
I want to copy or insert records from table B into...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |